Advertisement
Guest User

Untitled

a guest
Mar 20th, 2019
66
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 1.47 KB | None | 0 0
  1. CREATE TABLE [dbo].[Table1]
  2. (
  3. [ID] [varchar](50) NULL,
  4. [DATETIME] [datetime] NULL,
  5. [Flag] [int] NULL
  6. )
  7.  
  8. INSERT INTO table1
  9. VALUES ('ID-1', '2019-03-13 09:48:00.000', '2'),
  10. ('ID-1', '2019-03-13 09:48:00.000', '2'),
  11. ('ID-1', '2019-03-13 18:11:00.000', '3'),
  12. ('ID-1', '2019-03-13 18:11:00.000', '3'),
  13. ('ID-1', '2019-03-14 02:00:00.000', '3'),
  14. ('ID-1', '2019-03-14 09:54:00.000', '2'),
  15. ('ID-1', '2019-03-14 09:54:00.000', '2'),
  16. ('ID-1', '2019-03-14 09:54:00.000', '2'),
  17. ('ID-1', '2019-03-14 22:00:00.000', '3'),
  18. ('ID-1', '2019-03-14 22:00:00.000', '3'),
  19. ('ID-1', '2019-03-14 22:00:00.000', '3'),
  20. ('ID-1', '2019-03-15 13:55:00.000', '2'),
  21. ('ID-1', '2019-03-15 13:55:00.000', '2'),
  22. ('ID-1', '2019-03-15 13:55:00.000', '2'),
  23. ('ID-1', '2019-03-15 13:55:00.000', '2'),
  24. ('ID-1', '2019-03-15 13:55:00.000', '2'),
  25. ('ID-1', '2019-03-15 22:00:00.000', '3'),
  26. ('ID-1', '2019-03-15 22:00:00.000', '3'),
  27. ('ID-1', '2019-03-15 22:00:00.000', '3'),
  28. ('ID-1', '2019-03-15 22:00:00.000', '3')
  29.  
  30. WITH ins
  31. AS ( SELECT *
  32. FROM table1
  33. WHERE Flag = 2 )
  34. ,outs
  35. AS ( SELECT *
  36. FROM table1
  37. WHERE Flag = 3 )
  38. SELECT i.ID
  39. ,i.DATETIME InTime
  40. ,MIN(o.DATETIME) OutTime
  41. FROM ins i
  42. LEFT JOIN outs o ON o.ID = i.ID AND o.DATETIME > i.DATETIME -- out must be later than in
  43. GROUP BY i.ID
  44. ,i.DATETIME
  45. ,i.Flag;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement