Rameshwar Pawale Rameshwar Pawale - 1 month ago 6
SQL Question

preceding and following rows

I need a SQL query which pulls out data from a table which has continuous state-wise data as shown below. If I want to get the data for a given time window say an hour/day, then it should filter out records and pivot the state information.

Filter : FROM '2016-08-11 17:00:00.000' TO '2016-08-11 18:00:00.000' - 1 Hour as denoted by red line below-

enter image description here

I tried following query.

;WITH CTE as (
Select * from #temp
PIVOT(SUM(durationinseconds)
FOR state IN ([Appear Away],[Available],[Away],[Offline],[online]) ) as Pivottable
where StartTimestamp >= '2016-08-11 17:00:00.000' and EndTimestamp <= '2016-08-11 18:00:00.000'
)
select CAST(StartTimestamp as Date) as LocalDate,SUM([Available]) as [Available] , SUM([Away]) as Away,SUM([Online]) as [Online],
SUM(Offline) as [Offline]
from CTE
group by CAST(StartTimestamp as Date)


However, this query does not handle the boundary condition - LIKE second record started from 17:02, It should pick first 2 minutes from first record and add it to 'Away' state. And similarly for the bottom record it should only consider till 18:00.

Can someone help me with this query?

Sample Data -

create table #temp ([State] varchar(20),StartTimeStamp Datetime2, EndTimeStamp Datetime2, DurationInSeconds int)
Insert into #temp values
('Away','2016-08-11 16:57:19.000000', '2016-08-11 17:02:16.000000',297 )
,('Appear Away','2016-08-11 17:02:16.000000', '2016-08-11 17:07:16.000000', 300 )
,('Available','2016-08-11 17:07:16.000000', '2016-08-11 17:12:16.000000', 300 )
,('Away','2016-08-11 17:12:16.000000', '2016-08-11 17:17:16.000000', 300 )
,('Available','2016-08-11 17:17:16.000000', '2016-08-11 17:22:16.000000', 300 )
,('Away','2016-08-11 17:22:16.000000', '2016-08-11 17:27:16.000000', 300 )
,('Appear Away','2016-08-11 17:27:16.000000', '2016-08-11 17:32:16.000000', 300 )
,('Offline','2016-08-11 17:32:16.000000', '2016-08-11 17:37:16.000000', 300 )
,('Away','2016-08-11 17:37:16.000000', '2016-08-11 17:42:19.000000', 303 )
,('Appear Away','2016-08-11 17:42:19.000000', '2016-08-11 17:47:16.000000', 297 )
,('Offline','2016-08-11 17:47:16.000000', '2016-08-11 17:52:16.000000', 300 )
,('Appear Away','2016-08-11 17:52:16.000000', '2016-08-11 17:57:17.000000', 301 )
,('Online','2016-08-11 17:57:17.000000', '2016-08-11 18:02:16.000000', 299 )
,('Away','2016-08-11 18:02:16.000000', '2016-08-11 18:07:16.000000', 300 )


UPDATE2: Expected Output:

enter image description here

Answer

You can as the below:

DECLARE @StartDate DATETIME = '2016-08-8 17:00:00.000'
DECLARE @EndDate DATETIME = '2016-08-15 18:00:00.000' 

;WITH TmpCte
AS
(
    SELECT
        T.*,
        IIF(T.StartTimeStamp < DATEADD(ms, DATEDIFF(ms, '00:00:00', CAST(@StartDate AS TIME)), CAST(CAST(T.StartTimeStamp AS DATE) AS DATETIME)), 
                DATEADD(ms, DATEDIFF(ms, '00:00:00', CAST(@StartDate AS TIME)), CAST(CAST(T.StartTimeStamp AS DATE) AS DATETIME)), 
                T.StartTimeStamp) AS TmpStart,
        IIF(T.EndTimeStamp > DATEADD(ms, DATEDIFF(ms, '00:00:00', CAST(@EndDate AS TIME)), CAST(CAST(T.EndTimeStamp AS DATE) AS DATETIME)),
                DATEADD(ms, DATEDIFF(ms, '00:00:00', CAST(@EndDate AS TIME)), CAST(CAST(T.EndTimeStamp AS DATE) AS DATETIME)), 
                T.EndTimeStamp) AS TmpEnd        
    FROM
        #temp T
), CTE as (
    SELECT * FROM 
    (
          SELECT
            *,
            ABS(DATEDIFF(SECOND, TmpStart, TmpEnd)) TmpDuration
          FROM TmpCte
          where 
            TmpStart >= @StartDate and 
            TmpEnd <=  @EndDate AND
            TmpEnd > TmpStart       
    ) A
    PIVOT(SUM(TmpDuration)
    FOR state IN ([Appear Away],[Available],[Away],[Offline],[online])  ) as     Pivottable

  )
  select CAST(StartTimestamp as Date) as LocalDate,SUM([Available]) as [Available] , SUM([Away]) as Away,SUM([Online]) as [Online],
  SUM(Offline) as [Offline] 
  from CTE 
  group by CAST(StartTimestamp as Date) 

Result:

+------------+-----------+------+--------+---------+
| LocalDate  | Available | Away | Online | Offline |
+------------+-----------+------+--------+---------+
| 2016-08-11 |       600 | 1039 |    163 |     600 |
+------------+-----------+------+--------+---------+