Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE TABLE [dbo].[Table1]
- (
- [ID] [varchar](50) NULL,
- [DATETIME] [datetime] NULL,
- [Flag] [int] NULL
- )
- INSERT INTO table1
- VALUES ('ID-1', '2019-03-13 09:48:00.000', '2'),
- ('ID-1', '2019-03-13 09:48:00.000', '2'),
- ('ID-1', '2019-03-13 18:11:00.000', '3'),
- ('ID-1', '2019-03-13 18:11:00.000', '3'),
- ('ID-1', '2019-03-14 02:00:00.000', '3'),
- ('ID-1', '2019-03-14 09:54:00.000', '2'),
- ('ID-1', '2019-03-14 09:54:00.000', '2'),
- ('ID-1', '2019-03-14 09:54:00.000', '2'),
- ('ID-1', '2019-03-14 22:00:00.000', '3'),
- ('ID-1', '2019-03-14 22:00:00.000', '3'),
- ('ID-1', '2019-03-14 22:00:00.000', '3'),
- ('ID-1', '2019-03-15 13:55:00.000', '2'),
- ('ID-1', '2019-03-15 13:55:00.000', '2'),
- ('ID-1', '2019-03-15 13:55:00.000', '2'),
- ('ID-1', '2019-03-15 13:55:00.000', '2'),
- ('ID-1', '2019-03-15 13:55:00.000', '2'),
- ('ID-1', '2019-03-15 22:00:00.000', '3'),
- ('ID-1', '2019-03-15 22:00:00.000', '3'),
- ('ID-1', '2019-03-15 22:00:00.000', '3'),
- ('ID-1', '2019-03-15 22:00:00.000', '3')
- WITH ins
- AS ( SELECT *
- FROM table1
- WHERE Flag = 2 )
- ,outs
- AS ( SELECT *
- FROM table1
- WHERE Flag = 3 )
- SELECT i.ID
- ,i.DATETIME InTime
- ,MIN(o.DATETIME) OutTime
- FROM ins i
- LEFT JOIN outs o ON o.ID = i.ID AND o.DATETIME > i.DATETIME -- out must be later than in
- GROUP BY i.ID
- ,i.DATETIME
- ,i.Flag;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement