API tools faq
paste
Advertisement
Guest User

for posting on dba.stackexchange.com

a guest
Feb 6th, 2011
344
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
T-SQL 32.57 KB | None | 0 0
raw download clone embed print report
  1. CREATE TABLE #HistoricalEvents ( Identifier varchar(20), TimeOff datetime, TimeOn datetime )
  2. CREATE TABLE #CurrentEvents ( Identifier varchar(20), TimeOff datetime )
  3. CREATE TABLE #EventDataTemp( Identifier varchar(20), Type varchar(255), EventTime datetime, DiscoveredDate datetime, FileId int )
  4.  
  5. SET NOCOUNT ON
  6. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'101', N'Went Off', CAST(0x00009D360136A370 AS DateTime), CAST(0x00009D37008447AC AS DateTime), 16)
  7. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'101', N'Came On', CAST(0x00009D36013470F0 AS DateTime), CAST(0x00009D37008447AC AS DateTime), 16)
  8. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'101', N'Went Off', CAST(0x00009D36013284C0 AS DateTime), CAST(0x00009D37008447AC AS DateTime), 16)
  9. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'101', N'Came On', CAST(0x00009D360131F820 AS DateTime), CAST(0x00009D37008447AC AS DateTime), 16)
  10. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'101', N'Went Off', CAST(0x00009D36012E6610 AS DateTime), CAST(0x00009D37008447AC AS DateTime), 16)
  11. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'101', N'Came On', CAST(0x00009D36012A8DB0 AS DateTime), CAST(0x00009D37008447AC AS DateTime), 16)
  12. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'101', N'Came On', CAST(0x00009D36012A8DB0 AS DateTime), CAST(0x00009D37008447AC AS DateTime), 16)
  13. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'101', N'Came On', CAST(0x00009D3601297470 AS DateTime), CAST(0x00009D37008447AC AS DateTime), 16)
  14. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'101', N'Went Off', CAST(0x00009D360124C920 AS DateTime), CAST(0x00009D37008447AC AS DateTime), 16)
  15. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'101', N'Came On', CAST(0x00009D360124C920 AS DateTime), CAST(0x00009D37008447AC AS DateTime), 16)
  16. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'101', N'Went Off', CAST(0x00009D360123AFE0 AS DateTime), CAST(0x00009D37008447AC AS DateTime), 16)
  17. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'101', N'Came On', CAST(0x00009D360123AFE0 AS DateTime), CAST(0x00009D37008447AC AS DateTime), 16)
  18. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'101', N'Went Off', CAST(0x00009D360123AFE0 AS DateTime), CAST(0x00009D37008447AC AS DateTime), 16)
  19. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'101', N'Came On', CAST(0x00009D360123AFE0 AS DateTime), CAST(0x00009D37008447AC AS DateTime), 16)
  20. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'101', N'Went Off', CAST(0x00009D3601232340 AS DateTime), CAST(0x00009D37008447AC AS DateTime), 16)
  21. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'101', N'Came On', CAST(0x00009D3601232340 AS DateTime), CAST(0x00009D37008447AC AS DateTime), 16)
  22. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'101', N'Went Off', CAST(0x00009D3601232340 AS DateTime), CAST(0x00009D37008447AC AS DateTime), 16)
  23. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'101', N'Came On', CAST(0x00009D3601232340 AS DateTime), CAST(0x00009D37008447AC AS DateTime), 16)
  24. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'101', N'Went Off', CAST(0x00009D3601225050 AS DateTime), CAST(0x00009D37008447AC AS DateTime), 16)
  25. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'101', N'Came On', CAST(0x00009D3601225050 AS DateTime), CAST(0x00009D37008447AC AS DateTime), 16)
  26. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'101', N'Went Off', CAST(0x00009D3601220A00 AS DateTime), CAST(0x00009D37008447AC AS DateTime), 16)
  27. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'101', N'Came On', CAST(0x00009D3601220A00 AS DateTime), CAST(0x00009D37008447AC AS DateTime), 16)
  28. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'101', N'Came On', CAST(0x00009D3601220A00 AS DateTime), CAST(0x00009D37008447AC AS DateTime), 16)
  29. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'101', N'Came On', CAST(0x00009D3601220A00 AS DateTime), CAST(0x00009D37008447AC AS DateTime), 16)
  30. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'101', N'Came On', CAST(0x00009D3601220A00 AS DateTime), CAST(0x00009D37008447AC AS DateTime), 16)
  31. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'101', N'Came On', CAST(0x00009D3601220A00 AS DateTime), CAST(0x00009D37008447AC AS DateTime), 16)
  32. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'101', N'Came On', CAST(0x00009D3601220A00 AS DateTime), CAST(0x00009D37008447AC AS DateTime), 16)
  33. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'101', N'Went Off', CAST(0x00009D360121C3B0 AS DateTime), CAST(0x00009D37008447AC AS DateTime), 16)
  34. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'101', N'Came On', CAST(0x00009D360121C3B0 AS DateTime), CAST(0x00009D37008447AC AS DateTime), 16)
  35. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'101', N'Went Off', CAST(0x00009D360121C3B0 AS DateTime), CAST(0x00009D37008447AC AS DateTime), 16)
  36. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'101', N'Went Off', CAST(0x00009D37008447AC AS DateTime), CAST(0x00009D37008447AC AS DateTime), 16)
  37. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'102', N'Went Off', CAST(0x00009D360154D9D0 AS DateTime), CAST(0x00009D3700843168 AS DateTime), 16)
  38. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'102', N'Came On', CAST(0x00009D36015333F0 AS DateTime), CAST(0x00009D3700843168 AS DateTime), 16)
  39. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'102', N'Went Off', CAST(0x00009D36015333F0 AS DateTime), CAST(0x00009D3700843168 AS DateTime), 16)
  40. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'102', N'Came On', CAST(0x00009D36015333F0 AS DateTime), CAST(0x00009D3700843168 AS DateTime), 16)
  41. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'102', N'Came On', CAST(0x00009D36015333F0 AS DateTime), CAST(0x00009D3700843168 AS DateTime), 16)
  42. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'102', N'Went Off', CAST(0x00009D360152EDA0 AS DateTime), CAST(0x00009D3700843168 AS DateTime), 16)
  43. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'102', N'Came On', CAST(0x00009D360152A750 AS DateTime), CAST(0x00009D3700843168 AS DateTime), 16)
  44. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'102', N'Came On', CAST(0x00009D360152A750 AS DateTime), CAST(0x00009D3700843168 AS DateTime), 16)
  45. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'102', N'Went Off', CAST(0x00009D360152A750 AS DateTime), CAST(0x00009D3700843168 AS DateTime), 16)
  46. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'102', N'Came On', CAST(0x00009D3601526100 AS DateTime), CAST(0x00009D3700843168 AS DateTime), 16)
  47. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'102', N'Came On', CAST(0x00009D3601526100 AS DateTime), CAST(0x00009D3700843168 AS DateTime), 16)
  48. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'102', N'Came On', CAST(0x00009D360151D460 AS DateTime), CAST(0x00009D3700843168 AS DateTime), 16)
  49. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'102', N'Came On', CAST(0x00009D360151D460 AS DateTime), CAST(0x00009D3700843168 AS DateTime), 16)
  50. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'102', N'Went Off', CAST(0x00009D360151D460 AS DateTime), CAST(0x00009D3700843168 AS DateTime), 16)
  51. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'102', N'Came On', CAST(0x00009D360151D460 AS DateTime), CAST(0x00009D3700843168 AS DateTime), 16)
  52. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'102', N'Came On', CAST(0x00009D3601518E10 AS DateTime), CAST(0x00009D3700843168 AS DateTime), 16)
  53. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'102', N'Came On', CAST(0x00009D3601518E10 AS DateTime), CAST(0x00009D3700843168 AS DateTime), 16)
  54. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'102', N'Came On', CAST(0x00009D3601518E10 AS DateTime), CAST(0x00009D3700843168 AS DateTime), 16)
  55. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'102', N'Came On', CAST(0x00009D3601518E10 AS DateTime), CAST(0x00009D3700843168 AS DateTime), 16)
  56. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'102', N'Came On', CAST(0x00009D3601518E10 AS DateTime), CAST(0x00009D3700843168 AS DateTime), 16)
  57. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'102', N'Came On', CAST(0x00009D3601518E10 AS DateTime), CAST(0x00009D3700843168 AS DateTime), 16)
  58. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'102', N'Came On', CAST(0x00009D36015147C0 AS DateTime), CAST(0x00009D3700843168 AS DateTime), 16)
  59. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'102', N'Came On', CAST(0x00009D36015147C0 AS DateTime), CAST(0x00009D3700843168 AS DateTime), 16)
  60. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'102', N'Came On', CAST(0x00009D36015147C0 AS DateTime), CAST(0x00009D3700843168 AS DateTime), 16)
  61. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'102', N'Came On', CAST(0x00009D36015147C0 AS DateTime), CAST(0x00009D3700843168 AS DateTime), 16)
  62. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'102', N'Came On', CAST(0x00009D36015147C0 AS DateTime), CAST(0x00009D3700843168 AS DateTime), 16)
  63. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'102', N'Came On', CAST(0x00009D36015147C0 AS DateTime), CAST(0x00009D3700843168 AS DateTime), 16)
  64. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'102', N'Came On', CAST(0x00009D36015147C0 AS DateTime), CAST(0x00009D3700843168 AS DateTime), 16)
  65. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'102', N'Came On', CAST(0x00009D36015147C0 AS DateTime), CAST(0x00009D3700843168 AS DateTime), 16)
  66. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'102', N'Came On', CAST(0x00009D36015147C0 AS DateTime), CAST(0x00009D3700843168 AS DateTime), 16)
  67. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'102', N'Went Off', CAST(0x00009D3700843168 AS DateTime), CAST(0x00009D3700843168 AS DateTime), 16)
  68. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'103', N'Went Off', CAST(0x00009D4300F16CB0 AS DateTime), CAST(0x00009D440073F398 AS DateTime), 26)
  69. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'103', N'Came On', CAST(0x00009D4300EF8080 AS DateTime), CAST(0x00009D440073F398 AS DateTime), 26)
  70. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'103', N'Went Off', CAST(0x00009D440073F4C4 AS DateTime), CAST(0x00009D440073F4C4 AS DateTime), 26)
  71. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'104', N'Came On', CAST(0x00009D4300F47220 AS DateTime), CAST(0x00009D440073F398 AS DateTime), 26)
  72. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'104', N'Came On', CAST(0x00009D4300F31290 AS DateTime), CAST(0x00009D440073F398 AS DateTime), 26)
  73. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'104', N'Came On', CAST(0x00009D4300F1F950 AS DateTime), CAST(0x00009D440073F398 AS DateTime), 26)
  74. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'104', N'Came On', CAST(0x00009D4300F16CB0 AS DateTime), CAST(0x00009D440073F398 AS DateTime), 26)
  75. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'104', N'Came On', CAST(0x00009D4300F099C0 AS DateTime), CAST(0x00009D440073F398 AS DateTime), 26)
  76. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'104', N'Came On', CAST(0x00009D4300F099C0 AS DateTime), CAST(0x00009D440073F398 AS DateTime), 26)
  77. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'104', N'Went Off', CAST(0x00009D4300EFC6D0 AS DateTime), CAST(0x00009D440073F398 AS DateTime), 26)
  78. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'105', N'Came On', CAST(0x00009D4300F47220 AS DateTime), CAST(0x00009D440073F398 AS DateTime), 26)
  79. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'105', N'Came On', CAST(0x00009D4300F099C0 AS DateTime), CAST(0x00009D440073F398 AS DateTime), 26)
  80. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'105', N'Came On', CAST(0x00009D4300F099C0 AS DateTime), CAST(0x00009D440073F398 AS DateTime), 26)
  81. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'105', N'Came On', CAST(0x00009D4300EEAD90 AS DateTime), CAST(0x00009D440073F398 AS DateTime), 26)
  82. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'106', N'Went Off', CAST(0x00009D4300F1F950 AS DateTime), CAST(0x00009D440073F398 AS DateTime), 26)
  83. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'106', N'Came On', CAST(0x00009D4300EF8080 AS DateTime), CAST(0x00009D440073F398 AS DateTime), 26)
  84. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'106', N'Went Off', CAST(0x00009D440073F4C4 AS DateTime), CAST(0x00009D440073F4C4 AS DateTime), 26)
  85. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'107', N'Came On', CAST(0x00009D4300F1F950 AS DateTime), CAST(0x00009D440073F398 AS DateTime), 26)
  86. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'107', N'Came On', CAST(0x00009D4300F1B300 AS DateTime), CAST(0x00009D440073F398 AS DateTime), 26)
  87. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'107', N'Came On', CAST(0x00009D4300F16CB0 AS DateTime), CAST(0x00009D440073F398 AS DateTime), 26)
  88. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'107', N'Came On', CAST(0x00009D4300F16CB0 AS DateTime), CAST(0x00009D440073F398 AS DateTime), 26)
  89. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'107', N'Came On', CAST(0x00009D4300F099C0 AS DateTime), CAST(0x00009D440073F398 AS DateTime), 26)
  90. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'107', N'Came On', CAST(0x00009D4300EEAD90 AS DateTime), CAST(0x00009D440073F398 AS DateTime), 26)
  91. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'108', N'Went Off', CAST(0x00009D4300F00D20 AS DateTime), CAST(0x00009D440073F398 AS DateTime), 26)
  92. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'108', N'Came On', CAST(0x00009D4300EFC6D0 AS DateTime), CAST(0x00009D440073F398 AS DateTime), 26)
  93. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'108', N'Came On', CAST(0x00009D4300EEF3E0 AS DateTime), CAST(0x00009D440073F398 AS DateTime), 26)
  94. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'108', N'Went Off', CAST(0x00009D440073F4C4 AS DateTime), CAST(0x00009D440073F4C4 AS DateTime), 26)
  95. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'109', N'Came On', CAST(0x00009D4300F7BDE0 AS DateTime), CAST(0x00009D440073F398 AS DateTime), 26)
  96. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'109', N'Went Off', CAST(0x00009D4300F099C0 AS DateTime), CAST(0x00009D440073F398 AS DateTime), 26)
  97. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'109', N'Came On', CAST(0x00009D4300EFC6D0 AS DateTime), CAST(0x00009D440073F398 AS DateTime), 26)
  98. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'109', N'Came On', CAST(0x00009D4300EF8080 AS DateTime), CAST(0x00009D440073F398 AS DateTime), 26)
  99. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'109', N'Came On', CAST(0x00009D4300EF3A30 AS DateTime), CAST(0x00009D440073F398 AS DateTime), 26)
  100. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'109', N'Came On', CAST(0x00009D4300EEAD90 AS DateTime), CAST(0x00009D440073F398 AS DateTime), 26)
  101. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'110', N'Came On', CAST(0x00009D44017801D0 AS DateTime), CAST(0x00009D450074133C AS DateTime), 28)
  102. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'110', N'Came On', CAST(0x00009D440177BB80 AS DateTime), CAST(0x00009D450074133C AS DateTime), 28)
  103. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'111', N'Came On', CAST(0x00009D44016EF180 AS DateTime), CAST(0x00009D4500741468 AS DateTime), 28)
  104. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'111', N'Came On', CAST(0x00009D44016EF180 AS DateTime), CAST(0x00009D4500741468 AS DateTime), 28)
  105. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'111', N'Came On', CAST(0x00009D44016BA5C0 AS DateTime), CAST(0x00009D4500741594 AS DateTime), 28)
  106. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'112', N'Came On', CAST(0x00009D44016BA5C0 AS DateTime), CAST(0x00009D4500741594 AS DateTime), 28)
  107. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'113', N'Came On', CAST(0x00009D44016EF180 AS DateTime), CAST(0x00009D4500741468 AS DateTime), 28)
  108. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'114', N'Came On', CAST(0x00009D44016E1E90 AS DateTime), CAST(0x00009D4500741594 AS DateTime), 28)
  109. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'115', N'Came On', CAST(0x00009D44016EAB30 AS DateTime), CAST(0x00009D4500741594 AS DateTime), 28)
  110. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'115', N'Came On', CAST(0x00009D44016BEC10 AS DateTime), CAST(0x00009D4500741594 AS DateTime), 28)
  111. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'116', N'Came On', CAST(0x00009D440176A240 AS DateTime), CAST(0x00009D450074133C AS DateTime), 28)
  112. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'117', N'Came On', CAST(0x00009D44016EAB30 AS DateTime), CAST(0x00009D4500741468 AS DateTime), 28)
  113. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'117', N'Came On', CAST(0x00009D44016BEC10 AS DateTime), CAST(0x00009D4500741594 AS DateTime), 28)
  114. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'118', N'Came On', CAST(0x00009D440171B0A0 AS DateTime), CAST(0x00009D450074133C AS DateTime), 28)
  115. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'118', N'Came On', CAST(0x00009D4401716A50 AS DateTime), CAST(0x00009D4500741468 AS DateTime), 28)
  116. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'119', N'Came On', CAST(0x00009D450073A190 AS DateTime), CAST(0x00009D45007409DC AS DateTime), 28)
  117. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'119', N'Came On', CAST(0x00009D450073A190 AS DateTime), CAST(0x00009D45007409DC AS DateTime), 28)
  118. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'119', N'Came On', CAST(0x00009D450072CEA0 AS DateTime), CAST(0x00009D45007409DC AS DateTime), 28)
  119. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'119', N'Came On', CAST(0x00009D450071FBB0 AS DateTime), CAST(0x00009D45007409DC AS DateTime), 28)
  120. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'119', N'Came On', CAST(0x00009D450071B560 AS DateTime), CAST(0x00009D45007409DC AS DateTime), 28)
  121. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'120', N'Came On', CAST(0x00009D4401758900 AS DateTime), CAST(0x00009D450073EDBC AS DateTime), 28)
  122. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'121', N'Came On', CAST(0x00009D4401869060 AS DateTime), CAST(0x00009D450073EDBC AS DateTime), 28)
  123. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'121', N'Came On', CAST(0x00009D44018603C0 AS DateTime), CAST(0x00009D450073EDBC AS DateTime), 28)
  124. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'121', N'Came On', CAST(0x00009D44018603C0 AS DateTime), CAST(0x00009D450073EDBC AS DateTime), 28)
  125. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'121', N'Came On', CAST(0x00009D4401857720 AS DateTime), CAST(0x00009D450073EDBC AS DateTime), 28)
  126. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'121', N'Came On', CAST(0x00009D44018530D0 AS DateTime), CAST(0x00009D450073EDBC AS DateTime), 28)
  127. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'121', N'Came On', CAST(0x00009D4401841790 AS DateTime), CAST(0x00009D450073EDBC AS DateTime), 28)
  128. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'121', N'Came On', CAST(0x00009D440183D140 AS DateTime), CAST(0x00009D450073EDBC AS DateTime), 28)
  129. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'121', N'Came On', CAST(0x00009D4401838AF0 AS DateTime), CAST(0x00009D450073EDBC AS DateTime), 28)
  130. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'121', N'Came On', CAST(0x00009D4401838AF0 AS DateTime), CAST(0x00009D450073EDBC AS DateTime), 28)
  131. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'121', N'Came On', CAST(0x00009D440182FE50 AS DateTime), CAST(0x00009D450073EDBC AS DateTime), 28)
  132. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'122', N'Came On', CAST(0x00009D4401887C90 AS DateTime), CAST(0x00009D450073EDBC AS DateTime), 28)
  133. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'122', N'Came On', CAST(0x00009D4401883640 AS DateTime), CAST(0x00009D450073EDBC AS DateTime), 28)
  134. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'122', N'Came On', CAST(0x00009D440187A9A0 AS DateTime), CAST(0x00009D450073EDBC AS DateTime), 28)
  135. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'123', N'Came On', CAST(0x00009D4500149970 AS DateTime), CAST(0x00009D45007417EC AS DateTime), 28)
  136. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'124', N'Went Off', CAST(0x00009D45006A9140 AS DateTime), CAST(0x00009D450073EB64 AS DateTime), 28)
  137. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'124', N'Went Off', CAST(0x00009D450073EB64 AS DateTime), CAST(0x00009D450073EB64 AS DateTime), 28)
  138. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'125', N'Came On', CAST(0x00009D450067D220 AS DateTime), CAST(0x00009D450073EB64 AS DateTime), 28)
  139. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'125', N'Came On', CAST(0x00009D4500678BD0 AS DateTime), CAST(0x00009D450073EB64 AS DateTime), 28)
  140. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'126', N'Came On', CAST(0x00009D4500015F90 AS DateTime), CAST(0x00009D450073F014 AS DateTime), 28)
  141. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'126', N'Came On', CAST(0x00009D4500011940 AS DateTime), CAST(0x00009D450073F014 AS DateTime), 28)
  142. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'126', N'Went Off', CAST(0x00009D4500011940 AS DateTime), CAST(0x00009D450073F014 AS DateTime), 28)
  143. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'126', N'Came On', CAST(0x00009D450000D2F0 AS DateTime), CAST(0x00009D450073F014 AS DateTime), 28)
  144. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'126', N'Came On', CAST(0x00009D4500008CA0 AS DateTime), CAST(0x00009D450073F014 AS DateTime), 28)
  145. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'126', N'Came On', CAST(0x00009D4500000000 AS DateTime), CAST(0x00009D450073F014 AS DateTime), 28)
  146. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'126', N'Came On', CAST(0x00009D4500000000 AS DateTime), CAST(0x00009D450073F014 AS DateTime), 28)
  147. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'126', N'Went Off', CAST(0x00009D44018AF560 AS DateTime), CAST(0x00009D450073F014 AS DateTime), 28)
  148. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'126', N'Came On', CAST(0x00009D44018AAF10 AS DateTime), CAST(0x00009D450073F014 AS DateTime), 28)
  149. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'126', N'Went Off', CAST(0x00009D44018AAF10 AS DateTime), CAST(0x00009D450073F014 AS DateTime), 28)
  150. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'126', N'Came On', CAST(0x00009D44018AAF10 AS DateTime), CAST(0x00009D450073F014 AS DateTime), 28)
  151. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'126', N'Came On', CAST(0x00009D44018A68C0 AS DateTime), CAST(0x00009D450073F014 AS DateTime), 28)
  152. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'126', N'Went Off', CAST(0x00009D44018A68C0 AS DateTime), CAST(0x00009D450073F014 AS DateTime), 28)
  153. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'126', N'Went Off', CAST(0x00009D440189DC20 AS DateTime), CAST(0x00009D450073F014 AS DateTime), 28)
  154. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'126', N'Came On', CAST(0x00009D44018995D0 AS DateTime), CAST(0x00009D450073F014 AS DateTime), 28)
  155. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'126', N'Went Off', CAST(0x00009D44018995D0 AS DateTime), CAST(0x00009D450073F014 AS DateTime), 28)
  156. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'126', N'Came On', CAST(0x00009D4401894F80 AS DateTime), CAST(0x00009D450073F014 AS DateTime), 28)
  157. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'126', N'Came On', CAST(0x00009D4401894F80 AS DateTime), CAST(0x00009D450073F014 AS DateTime), 28)
  158. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'126', N'Went Off', CAST(0x00009D4401894F80 AS DateTime), CAST(0x00009D450073F014 AS DateTime), 28)
  159. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'126', N'Came On', CAST(0x00009D4401894F80 AS DateTime), CAST(0x00009D450073F014 AS DateTime), 28)
  160. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'126', N'Came On', CAST(0x00009D4401894F80 AS DateTime), CAST(0x00009D450073F014 AS DateTime), 28)
  161. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'126', N'Came On', CAST(0x00009D4401894F80 AS DateTime), CAST(0x00009D450073F014 AS DateTime), 28)
  162. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'126', N'Went Off', CAST(0x00009D4401894F80 AS DateTime), CAST(0x00009D450073F014 AS DateTime), 28)
  163. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'126', N'Came On', CAST(0x00009D4401894F80 AS DateTime), CAST(0x00009D450073F014 AS DateTime), 28)
  164. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'126', N'Came On', CAST(0x00009D4401894F80 AS DateTime), CAST(0x00009D450073F014 AS DateTime), 28)
  165. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'126', N'Went Off', CAST(0x00009D4401894F80 AS DateTime), CAST(0x00009D450073F014 AS DateTime), 28)
  166. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'126', N'Came On', CAST(0x00009D4401890930 AS DateTime), CAST(0x00009D450073F014 AS DateTime), 28)
  167. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'126', N'Came On', CAST(0x00009D4401890930 AS DateTime), CAST(0x00009D450073F014 AS DateTime), 28)
  168. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'126', N'Came On', CAST(0x00009D4401890930 AS DateTime), CAST(0x00009D450073F014 AS DateTime), 28)
  169. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'126', N'Went Off', CAST(0x00009D4401890930 AS DateTime), CAST(0x00009D450073F014 AS DateTime), 28)
  170. INSERT INTO #EventDataTemp (Identifier, Type, EventTime, DiscoveredDate, FileId) VALUES (N'127', N'Went Off', CAST(0x00009D4400BD83A0 AS DateTime), CAST(0x00009D450073DAFC AS DateTime), 28)
  171.  
  172. ;WITH InputDateSort (Identifier, DiscoveredDate, FileId) AS (
  173. SELECT DISTINCT Identifier, MIN(DiscoveredDate), FileId FROM #EventDataTemp GROUP BY FileID, Identifier )
  174. INSERT INTO #CurrentEvents (
  175.       Identifier
  176.     , TimeOff )
  177.   SELECT
  178.        t.Identifier
  179.       ,MIN(EventTime)
  180.   FROM #EventDataTemp d
  181.   INNER JOIN InputDateSort t ON t.Identifier = d.Identifier AND d.DiscoveredDate = t.DiscoveredDate
  182.   WHERE Type LIKE '%Off%' AND NOT EXISTS (
  183.     SELECT * FROM #CurrentEvents c WHERE c.Identifier = d.Identifier
  184.   )
  185.   GROUP BY t.FileId,t.Identifier,t.DiscoveredDate
  186.   ORDER BY 1, 2 DESC
  187.  
  188. ;WITH InputDateSort (Identifier, DiscoveredDate, FileId) AS (
  189. SELECT DISTINCT Identifier, MAX(DiscoveredDate), FileId FROM #EventDataTemp GROUP BY FileID, Identifier )
  190.   SELECT
  191.        t.Identifier
  192.       ,MAX(EventTime)
  193.   FROM #EventDataTemp d
  194.   INNER JOIN InputDateSort t ON t.Identifier = d.Identifier AND d.DiscoveredDate = t.DiscoveredDate
  195.   WHERE Type LIKE '%On%' AND EXISTS (
  196.     SELECT * FROM #CurrentEvents c WHERE c.Identifier = d.Identifier
  197.   )
  198.   GROUP BY t.FileId,t.Identifier,t.DiscoveredDate
  199.   ORDER BY 1, 2 DESC
  200.  
  201. SELECT DISTINCT
  202.         Identifier
  203.       , CASE WHEN Type LIKE '%Off%' THEN 'Off'
  204.             WHEN Type LIKE '%On%' THEN 'On'
  205.             ELSE 'Unknown' END AS TypeOfCondition
  206.       , CASE WHEN Type LIKE '%Off%' THEN EventTime ELSE NULL END AS TimeOff
  207.       , CASE WHEN Type LIKE '%On%' THEN EventTime ELSE NULL END AS TimeOn
  208.       , EventTime
  209.       , DiscoveredDate
  210.       , FileId
  211.   FROM #EventDataTemp
  212.   ORDER BY Identifier, DiscoveredDate, EventTime DESC
  213.  
  214. DROP TABLE #HistoricalEvents
  215. DROP TABLE #CurrentEvents
  216. DROP TABLE #EventDataTemp
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!