CODEWITHSUNDEEP
sqlsql-serverdatabase
MTANG
MTANG

Reputation: 516

SQL Server query "next-day changes"

I have a data set like the following:

Name  Date      Type
Alex  18/1/5     A
Bob   17/12/31   D
Alex  18/1/1     B
Alex  18/1/8     A
Bob   17/8/31    D
Bob   18/3/31    C
Bob   18/6/30    E

I want to do the following: For each name, sort by date and find the type change between the sorted result. The result should look like following:

  Name  DateBefore DateAfter TypeBefore TypeAfter
  Alex  18/1/1     18/1/5    B          A
  Bob   17/12/31   18/3/31   D          C
  Bob   18/3/31    18/6/30   C          E

How can I achieve this via SQL Query?

Upvotes: 1

Views: 57

Answers (2)

D-Shih
D-Shih

Reputation: 46219

If I understand correctly, you can try to use LEAD window function in a subquery, then get this type isn't equal nextType row.

CREATE TABLE T(
  Name VARCHAR(50),
  [Date] DATE,
  [Type] VARCHAR(5)

);




INSERT INTO T VALUES ('Alex','2018/01/5','A');
INSERT INTO T VALUES ('Bob','2017/12/31','D');
INSERT INTO T VALUES ('Alex','2018/01/1','B');
INSERT INTO T VALUES ('Alex','2018/01/8','A');
INSERT INTO T VALUES ('Bob','2017/08/31','D');
INSERT INTO T VALUES ('Bob','2018/03/31','C');
INSERT INTO T VALUES ('Bob','2018/06/30','E');

Query 1:

SELECT Name,
      [Date] 'DateBefore',
      nextDt 'DateAfter',
      [Type] 'TypeBefore' ,
      nextType 'TypeAfter'
FROM 
(
select  *,LEAD([Date]) over(partition by Name order by [Date])  nextDt
          ,LEAD([Type]) over(partition by Name order by [Date]) nextType
from t
) t1
where [Type] <> nextType

Results:

| Name | DateBefore |  DateAfter | TypeBefore | TypeAfter |
|------|------------|------------|------------|-----------|
| Alex | 2018-01-01 | 2018-01-05 |          B |         A |
|  Bob | 2017-12-31 | 2018-03-31 |          D |         C |
|  Bob | 2018-03-31 | 2018-06-30 |          C |         E |

Upvotes: 2

ali
ali

Reputation: 1351

As far as I understand, the query below would work;

SELECT        
    t1.Name AS NameOfPerson, 
    t1.Date AS DateBefore,
    t1.Type AS TypeBefore,
    (
        SELECT TOP 1 t2.Date 
        FROM PeopleTable AS t2 
        WHERE t2.Name = T1.Name AND t2.Date>t1.Date
        ORDER BY t2.Date 
    ) AS DateAfter,
    (
        SELECT TOP 1 t2.Type 
        FROM PeopleTable AS t2 
        WHERE t2.Name = T1.Name AND t2.Date>t1.Date
        ORDER BY t2.Date 
    ) AS TypeAfter
FROM PeopleTable AS t1 
ORDER BY Date ASC

The query may be optimized by it matches with your sample output.

Upvotes: 0

Related Questions