SQL Server time-phased query

Statistical continuous time period data

If OBJECT_ID (n ' Test ', n ' U ') is not nulldrop table Testgo CREATE table Test (pscode decimal, Outputcode int,monitortime da tetime) insert INTO Testselect 4100000406,1,convert (datetime, ' 2015-04-01 00:00 ') union Allselect 4100000406,1,convert ( DateTime, ' 2015-04-01 01:00 ') Union allselect 4100000406,1,convert (datetime, ' 2015-04-01 02:00 ') union allselect 4100000406,1,convert (datetime, ' 2015-04-01 03:00 ') Union allselect 4100000406,1,convert (datetime, ' 2015-04-01 04:00 ') Union Allselect 4100000406,1,convert (datetime, ' 2015-04-01 05:00 ') Union allselect 4100000406,1,convert (DateTime, ' 2015-04-01 ") Union Allselect 4100000406,1,convert (datetime, ' 2015-04-01") union Allselect 4100000406, 1, Convert (datetime, ' 2015-04-01) ' Union allselect 4100000406,1,convert (datetime, ' 2015-04-01 ') union Allselect 4100000406,1,convert (datetime, ' 2015-04-01 10:00am ') Union allselect 4100000406,1,convert (DateTime, ' 2015-04-01 ") Union Allselect 4100000406,1,convert (datetime, ' 2015-04-01 13:00 ') unionAllselect 4100000406,1,convert (datetime, ' 2015-04-01 ") union Allselect 4100000406,1,convert (DateTime, ' 2015-04-01 15:00 ') Union allselect 4100000406,1,convert (datetime, ' 2015-04-01 16:00 ') union Allselect 4100000406, 1, Convert (DateTime, ' 2015-04-01 17:00 ') Union allselect 4100000406,1,convert (datetime, ' 2015-04-01 18:00 ') union Allselect 4100000406,1,convert (datetime, ' 2015-04-01 19:00 ') Union allselect 4100000406,1,convert (DateTime, ' 2015-04-01 20:00 ') Union allselect 4100000406,1,convert (datetime, ' 2015-04-01 22:00 ') union Allselect 4100000406, 1, Convert (DateTime, ' 2015-04-01 23:00 ') Union allselect 4100000405,2,convert (datetime, ' 2015-04-01 01:00 ') union Allselect 4100000405,2,convert (datetime, ' 2015-04-01 02:00 ') Union allselect 4100000405,2,convert (DateTime, ' 2015-04-01 03:00 ') Union allselect 4100000405,2,convert (datetime, ' 2015-04-01 04:00 ') union Allselect 4100000405, 2, Convert (DateTime, ' 2015-04-01 05:00 ') Union allselect 4100000405,2,convert (datetime, ' 2015-04-01-6:00pm ') union Allselect4100000405,2,convert (datetime, ' 2015-04-01 ') union allselect 4100000405,2,convert (datetime, ' 2015-04-01 08:00 ') Union Allselect 4100000405,2,convert (datetime, ' 2015-04-01 ') union allselect 4100000405,2,convert (DateTime, ' 2015-04-01 ") Union Allselect 4100000405,2,convert (datetime, ' 2015-04-01") union Allselect 4100000405, 2, Convert (DateTime, ' 2015-04-01 13:00 ') Union allselect 4100000405,2,convert (datetime, ' 2015-04-01 ') union Allselect 4100000405,2,convert (datetime, ' 2015-04-01 15:00 ') Union allselect 4100000405,2,convert (DateTime, ' 2015-04-01 16:00 ') Union allselect 4100000405,2,convert (datetime, ' 2015-04-01 17:00 ') union Allselect 4100000405, 2, Convert (DateTime, ' 2015-04-01 18:00 ') Union allselect 4100000402,1,convert (datetime, ' 2015-04-01 00:00 ') union Allselect 4100000402,1,convert (datetime, ' 2015-04-01 01:00 ') Union allselect 4100000402,1,convert (DateTime, ' 2015-04-01 02:00 ') Union allselect 4100000402,1,convert (datetime, ' 2015-04-01 03:00 ') union Allselect 4100000402, 1,convert (datetime, ' 2015-04-01 04:00 ') Union allselect 4100000402,1,convert (datetime, ' 2015-04-01 05:00 ') union Allselect 4100000402,1,convert (datetime, ' 2015-04-01 ') union allselect 4100000402,1,convert (DateTime, ' 2015-04-01 ") Union Allselect 4100000402,1,convert (datetime, ' 2015-04-01") union Allselect 4100000402, 1, Convert (datetime, ' 2015-04-01 ') union allselect 4100000402,1,convert (datetime, ' 2015-04-01-11:00am ') union Allselect 4100000402,1,convert (datetime, ' 2015-04-01 ') union allselect 4100000402,1,convert (DateTime, ' 2015-04-01 13:00 ') Union allselect 4100000402,1,convert (datetime, ' 2015-04-01 ') union Allselect 4100000402, 1, Convert (DateTime, ' 2015-04-01 15:00 ') Union allselect 4100000402,1,convert (datetime, ' 2015-04-01 16:00 ') union Allselect 4100000402,1,convert (datetime, ' 2015-04-01 17:00 ') Union allselect 4100000402,1,convert (DateTime, ' 2015-04-01 18:00 ') Union allselect 4100000402,1,convert (datetime, ' 2015-04-01 19:00 ') union Allselect 4100000402, 1, Convert(DateTime, ' 2015-04-01 20:00 ') Union allselect 4100000402,1,convert (datetime, ' 2015-04-01 22:00 ') union allselect 4100000402,1,convert (datetime, ' 2015-04-01 23:00 ') Select Pscode,outputcode, CONVERT (varchar (+), MIN (Monitortime), + ' ~ ' +convert (varchar (+), MAX (monitortime), +) FW, COUNT (1) Num from (select X.pscode,x.outputcode,x.monitortim E,dateadd (Hour,-x.orderby,x.monitortime) diff from (Select Pscode,outputcode,monitortime, Row_number () over ( Partition by Pscode,outputcode ORDER by Pscode,outputcode,monitortime) (from Test) x) y GROUP by Y.PSCODE,Y.OUTPU Tcode,y.diff

SQL Server time-phased query