SQL grouping part-CodePudding

The group by method is if the value of the next column is the same, I will calculate how long the unit has been in a certain place, later added per group

BEFORE

| ID         | UNIT  | PositionDateTime        | Location |   
| ---        | ----- | ----------------------- | -------- |
| 2054431321 | PM1041| 2022-09-25 00:05:55.000 | CP 33    |
| 2054776699 | PM1041| 2022-09-25 00:06:53.000 | KM 33    |
| 2054431444 | PM1041| 2022-09-25 00:06:53.000 | KM 33    |
| 2056063402 | PM1041| 2022-09-25 00:06:55.000 | KM 33    |
| 2054431444 | PM1041| 2022-09-25 00:11:55.000 | KM 31    |
| 2056064084 | PM1041| 2022-09-25 00:20:55.000 | KM 33    |
| 2056065697 | PM1041| 2022-09-25 00:30:55.000 | KM 33    |
| 2059291255 | PM1041| 2022-09-25 02:05:55.000 | CP 33    |
| 2059291566 | PM1041| 2022-09-25 02:15:55.000 | CP 33    |
| 2054776808 | PM1041| 2022-09-25 02:21:55.000 | KM 31    |
| 2060581645 | PM1041| 2022-09-25 02:22:55.000 | KM 31    |

GROUP TO

UNIT  | PositionDateTimeStart    | PositionDateTimeEnd     | Location |   
----- | -----------------------   --------------------     | --------
PM1041| 2022-09-25 00:05:55.000  | 2022-09-25 00:05:55.000 | CP 33    |
PM1041| 2022-09-25 00:06:53.000  | 2022-09-25 00:06:55.000 | KM 33    |
PM1041| 2022-09-25 00:11:55.000  | 2022-09-25 00:11:55.000 | KM 31    |
PM1041| 2022-09-25 00:20:55.000  | 2022-09-25 00:30:55.000 | KM 33    |
PM1041| 2022-09-25 02:05:55.000  | 2022-09-25 02:15:55.000 | CP 33    |
PM1041| 2022-09-25 02:21:55.000  | 2022-09-25 02:22:55.000 | KM 31    |

CodePudding user response：

You can the preferred output using cursors, please find below sample script to get preferred output:

Create Table #table 
(
Id int,
Unit varchar(10),
PositionDateTime DateTime,
Location varchar(6)
)

Create Table #OuputTable 
(
Unit varchar(10),
PositionDateStartTime DateTime,
PositionDateEndTime DateTime,
Location varchar(6)
)

INSERT INTO #table
    VALUES (2054431321, 'PM1041', '2022-09-25 00:05:55.000', 'CP 33')
INSERT INTO #table
    VALUES (2054776699, 'PM1041', '2022-09-25 00:06:53.000', 'KM 33')
INSERT INTO #table
    VALUES (2054431444, 'PM1041', '2022-09-25 00:06:53.000', 'KM 33')
INSERT INTO #table
    VALUES (2056063402, 'PM1041', '2022-09-25 00:06:55.000', 'KM 33')
INSERT INTO #table
    VALUES (2054431444, 'PM1041', '2022-09-25 00:11:55.000', 'KM 31')
INSERT INTO #table
    VALUES (2056064084, 'PM1041', '2022-09-25 00:20:55.000', 'KM 33')
INSERT INTO #table
    VALUES (2056065697, 'PM1041', '2022-09-25 00:30:55.000', 'KM 33')
INSERT INTO #table
    VALUES (2059291255, 'PM1041', '2022-09-25 02:05:55.000', 'CP 33')
INSERT INTO #table
    VALUES (2059291566, 'PM1041', '2022-09-25 02:15:55.000', 'CP 33')
INSERT INTO #table
    VALUES (2054776808, 'PM1041', '2022-09-25 02:21:55.000', 'KM 31')
INSERT INTO #table
    VALUES (2060581645, 'PM1041', '2022-09-25 02:22:55.000', 'KM 31')

--select * from #table;

Declare @Unit varchar(10), @PositionDateTime DateTime, @Location varchar(6);
Declare @NextRowUnit varchar(10), @PositionEndDateTime DateTime, @NextRowLocation varchar(6);
Declare @LastPositionEndDateTime DateTime


Declare Table_Cursor Cursor For
SELECT
    Unit
   ,PositionDateTime
   ,Location
FROM #table;

Open Table_Cursor;

Fetch Next from Table_Cursor Into @Unit, @PositionDateTime, @Location
WHILE @@Fetch_Status = 0
BEGIN
 

Fetch Next from Table_Cursor Into @NextRowUnit, @PositionEndDateTime, @NextRowLocation
If (@Location = @NextRowLocation AND @@Fetch_Status = 0)
Begin
Print @Location;
Print @NextRowLocation;
SET @LastPositionEndDateTime = @PositionEndDateTime;
END
ELSE
Begin
INSERT INTO #OuputTable
    VALUES (@Unit, @PositionDateTime, CASE WHEN @LastPositionEndDateTime IS NULL THEN @PositionDateTime ELSE @LastPositionEndDateTime END, @Location)
SET @Unit = @NextRowUnit;
SET @PositionDateTime = @PositionEndDateTime;
SET @Location = @NextRowLocation;
END

END

CLOSE Table_Cursor;
DEALLOCATE Table_Cursor;

SELECT
    *
FROM #OuputTable;

DROP TABLE #table
DROP TABLE #OuputTable

I hope this is what you were looking for.

CodePudding user response：

You can compare the previous row with next row using LAG() sql function.

Create table #temp(id int, unit varchar(15), PositiondateTime datetime, location varchar(15))
insert into #temp values ('2054431321' ,'PM1041','2022-09-25 00:05:55.000', 'CP 33')
insert into #temp values ('2054776699' ,'PM1041','2022-09-25 00:06:53.000', 'KM 33')
insert into #temp values ('2054431444' ,'PM1041','2022-09-25 00:06:53.000', 'KM 33')
insert into #temp values ('2056063402' ,'PM1041','2022-09-25 00:06:55.000', 'KM 33')
insert into #temp values ('2054431444' ,'PM1041','2022-09-25 00:11:55.000', 'KM 31')
insert into #temp values ('2056064084' ,'PM1041','2022-09-25 00:20:55.000', 'KM 33')
insert into #temp values ('2056065697' ,'PM1041','2022-09-25 00:30:55.000', 'KM 33')
insert into #temp values ('2059291255' ,'PM1041','2022-09-25 02:05:55.000', 'CP 33')
insert into #temp values ('2059291566' ,'PM1041','2022-09-25 02:15:55.000', 'CP 33')
insert into #temp values ('2054776808' ,'PM1041','2022-09-25 02:21:55.000', 'KM 31')
insert into #temp values ('2060581645' ,'PM1041','2022-09-25 02:22:55.000', 'KM 31')

SELECT MAX(unit)UNIT, MIN(PositiondateTime)PositionDateTimeStart, MAX(PositiondateTime)PositionDateTimeEnd, MAX(location)Location
FROM(
    select * 
           ,SUM(CASE WHEN location <> NextVal THEN 1 ELSE 0 END) OVER(ORDER BY PositiondateTime) GroupedValue
    from(
        select *
            ,ISNULL(lag(location) over(partition by unit order by PositiondateTime),'')NextVal
        from #temp
        )D
    )E
group by GroupedValue

drop table #temp

Output:

UNIT     PositionDateTimeStart   PositionDateTimeEnd     Location
-------------------------------- ----------------------- ---------
PM1041   2022-09-25 00:05:55.000 2022-09-25 00:05:55.000 CP 33
PM1041   2022-09-25 00:06:53.000 2022-09-25 00:06:55.000 KM 33
PM1041   2022-09-25 00:11:55.000 2022-09-25 00:11:55.000 KM 31
PM1041   2022-09-25 00:20:55.000 2022-09-25 00:30:55.000 KM 33
PM1041   2022-09-25 02:05:55.000 2022-09-25 02:15:55.000 CP 33
PM1041   2022-09-25 02:21:55.000 2022-09-25 02:22:55.000 KM 31