preceding and following rows

Question

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 23:50:00.000' TO '2016-08-13 01:15:00.000' - 85 Minutes as highlighted in yellow color below-

The filters are as below -

When I give following filters, then

DECLARE @StartDate DATETIME = '2016-08-11 23:50:00.000'
DECLARE @EndDate   DATETIME = '2016-08-15 01:15:00.000' 

Ideally, it should consider the time window as -

2016-08-11 23:50:00 to 2016-08-12 01:15:00 - 85 minutes
2016-08-12 23:50:00 to 2016-08-13 01:15:00 - 85 minutes
2016-08-13 23:50:00 to 2016-08-14 01:15:00 - 85 minutes
2016-08-14 23:50:00 to 2016-08-15 01:15:00 - 85 minutes
2016-08-15 23:50:00 to 2016-08-16 01:15:00 - 85 minutes

and so on ....

UPDATE 3

Can someone help me with this query?

Sample Data -

create table #temp1 ([State] varchar(20),StartTimeStamp Datetime2,    EndTimeStamp Datetime2, DurationInSeconds int)
Insert into #temp1 values('Away',       '2016-08-11 23:40:00.000000',           '2016-08-11 23:45:00.000000',   300     )
,('Appear Away','2016-08-11 23:45:00.000000',           '2016-08-11 23:50:00.000000',   300 )
,('Available', '2016-08-11 23:50:00.000000',            '2016-08-11 23:55:00.000000',   300 )
,('Available','2016-08-11 23:55:00.000000',             '2016-08-11 23:59:59.000000',   299 )
,('Away',       '2016-08-12 00:00:00.000000',           '2016-08-12 00:05:00.000000',   300 )
,('Offline',    '2016-08-12 00:05:00.000000',           '2016-08-12 00:15:00.000000',   600 )
,('Away',       '2016-08-12 00:15:00.000000',           '2016-08-12 00:30:00.000000',   900 )
,('Appear Away','2016-08-12 00:30:00.000000',           '2016-08-12 01:15:00.000000',   2700 )
,('Away',       '2016-08-12 01:15:00.000000',           '2016-08-12 01:30:00.000000',   900 )
,('Offline',    '2016-08-12 01:30:00.000000',           '2016-08-12  18:30:00.000000',   64800 )
,('Appear Away','2016-08-12 18:30:00.000000',           '2016-08-12   23:30:00.000000',   18000 )
,('Available',  '2016-08-12 23:30:00.000000',           '2016-08-12 23:45:00.000000',   900 )
,('Away',       '2016-08-12 23:45:00.000000',           '2016-08-12 23:50:00.000000',   300 )
,('Offline',    '2016-08-12 23:50:00.000000',           '2016-08-12 23:55:00.000000',   300 )
,('Available',  '2016-08-12 23:55:00.000000',           '2016-08-12 23:59:59.000000',   299 )
,('Away',       '2016-08-13 00:00:00.000000',           '2016-08-13 00:05:00.000000',   300 )
,('Offline',    '2016-08-13 00:05:00.000000',           '2016-08-13 00:15:00.000000',   600 )
,('Away',       '2016-08-13 00:15:00.000000',           '2016-08-13 00:30:00.000000',   900 )
,('Appear Away','2016-08-13 00:30:00.000000',           '2016-08-13 01:15:00.000000',   2700 )
,('Away',       '2016-08-13 01:15:00.000000',           '2016-08-13 01:30:00.000000',   900 )

UPDATE2: Expected Output:

Answer 1

Try as the below.

DECLARE @StartDate DATETIME = '2016-08-11 23:45:00.000'
DECLARE @EndDate DATETIME = '2016-08-12 01:00:00.000' 

;WITH TmpCte
AS
(
    SELECT
        T.*,
        IIF(T.StartTimeStamp < @StartDate, 
                @StartDate,
                T.StartTimeStamp) AS TmpStart,
        IIF(T.EndTimeStamp > @EndDate,              
              @EndDate,
                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([Appear Away]) as [Appear Away], 
    SUM([Available]) as [Available] , 
    SUM([Away]) as Away,SUM([Online]) as [Online],
    SUM(Offline) as [Offline] 
  from CTE 
  group by CAST(StartTimestamp as Date) 

Answer 2

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 |
+------------+-----------+------+--------+---------+