Reputation: 682
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:
Upvotes: 0
Views: 261
Reputation: 4092
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)
Upvotes: 0
Reputation: 4092
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 |
+------------+-----------+------+--------+---------+
Upvotes: 2