CODEWITHSUNDEEP
sql-servert-sql
user1716729
user1716729

Reputation: 397

Utilisation per hour

Hope someone can help me with a script to identify resource utilisation per hour. The screenshot shows the 3 scenarios enter image description here

I am struggling to come with a query when time overlap

Expected output enter image description here

Sample data

CREATE TABLE #temptable ( [resourceid] uniqueidentifier, [VirtualWorkerName] nvarchar(128), [startdatetime] datetime, [enddatetime] datetime, [sessionnumber] int )
INSERT INTO #temptable ([resourceid], [VirtualWorkerName], [startdatetime], [enddatetime], [sessionnumber])
VALUES
( '{f69f653e-d301-44ec-a081-7df6e3b02113}', N'DR0001:8183', N'2018-11-01T09:00:00.003', N'2018-11-01T09:25:00.003', 3 ), 
( '{0bf414c3-da13-4379-97a1-395f6638b946}', N'DR0001', N'2018-11-01T09:00:00.003', N'2018-11-01T09:25:00.003', 24 ), 
( '{0bf414c3-da13-4379-97a1-395f6638b946}', N'DR0001', N'2018-11-01T09:00:00.003', N'2018-11-01T09:25:00.003', 15 ), 
( '{0bf414c3-da13-4379-97a1-395f6638b946}', N'DR0001', N'2018-11-01T09:00:00.003', N'2018-11-01T09:25:00.003', 19 ), 
( '{0bf414c3-da13-4379-97a1-395f6638b946}', N'DR0001', N'2018-11-01T09:00:00.003', N'2018-11-01T09:25:00.003', 12 ), 
( '{a77efccf-26b2-49cb-b0d4-a721328e19c8}', N'DR0001:8193', N'2018-11-01T09:00:00.003', N'2018-11-01T09:25:00.003', 56 ), 
( '{f69f653e-d301-44ec-a081-7df6e3b02113}', N'DR0001:8183', N'2018-11-01T09:35:00.003', N'2018-11-01T09:40:00.003', 21 ), 
( '{a77efccf-26b2-49cb-b0d4-a721328e19c8}', N'DR0001:8193', N'2018-11-01T09:40:00.003', N'2018-11-01T10:10:00.003', 88 ), 
( '{f69f653e-d301-44ec-a081-7df6e3b02113}', N'DR0001:8183', N'2018-11-01T09:50:00.003', N'2018-11-01T10:10:00.003', 17 ), 
( '{f69f653e-d301-44ec-a081-7df6e3b02113}', N'DR0001:8183', N'2018-11-01T10:00:00.003', N'2018-11-01T10:05:00.003', 76 ), 
( '{0bf414c3-da13-4379-97a1-395f6638b946}', N'DR0001', N'2018-11-01T10:00:00.003', N'2018-11-01T10:10:00.003', 64 ), 
( '{0bf414c3-da13-4379-97a1-395f6638b946}', N'DR0001', N'2018-11-01T10:05:00.003', N'2018-11-01T10:10:00.003', 44 ), 
( '{f69f653e-d301-44ec-a081-7df6e3b02113}', N'DR0001:8183', N'2018-11-01T11:42:43.843', N'2018-11-01T12:00:49.643', 144 ), 
( '{f69f653e-d301-44ec-a081-7df6e3b02113}', N'DR0001:8183', N'2018-11-01T12:17:30.943', N'2018-11-01T14:05:03.88', 831 ), 
( '{f69f653e-d301-44ec-a081-7df6e3b02113}', N'DR0001:8183', N'2018-11-01T14:21:30.3', N'2018-11-01T14:35:59.457', 26 ), 
( '{f69f653e-d301-44ec-a081-7df6e3b02113}', N'DR0001:8183', N'2018-11-01T14:42:44.037', N'2018-11-01T14:58:20.653', 36 ), 
( '{f69f653e-d301-44ec-a081-7df6e3b02113}', N'DR0001:8183', N'2018-11-01T15:05:16.753', N'2018-11-01T15:21:24.337', 46 ), 
( '{f69f653e-d301-44ec-a081-7df6e3b02113}', N'DR0001:8183', N'2018-11-01T15:40:50.7', N'2018-11-01T15:58:59.11', 66 ), 
( '{f69f653e-d301-44ec-a081-7df6e3b02113}', N'DR0001:8183', N'2018-11-01T15:48:42.353', N'2018-11-01T17:28:42.387', 30228 )

http://sqlfiddle.com/#!18/a3dfb4

Upvotes: 1

Views: 52

Answers (1)

jjdesign
jjdesign

Reputation: 344

here is the hour cut to use with your above script. looks a bit complex with inner select queries, but in fact the simple trick is to slice distinct 5 minutes periods :

declare @T TABLE([resourceid] uniqueidentifier, [dt1] datetime, [dt2] datetime)
INSERT @T ([resourceid], dt1, dt2)
select distinct resourceid, dt1,dt2
from #temptable a 
outer apply(select [dt1] = cast(convert(char(16), startdatetime,126)+':00' as datetime) /*second truncate*/
                  ,[dt2] = cast(convert(char(16), enddatetime,126)  +':00' as datetime)
            )dt
order by resourceid, dt1,dt2;

select resourceid, [Time] = periodeH, [usage (minutes)] = SUM(diff)
from(
        select distinct resourceid,periodeH,periode1,periode2,diff
        from(
                select *,[diff]=DATEDIFF(MINUTE
                                        ,case when dt1 < periode1 then periode1 else dt1 end
                                        ,case when dt2 > periode2 then periode2 else dt2 end
                                     )
                from @T
                    -- hour slice --
                join (values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23)
                     )hours(hh) on hh between datepart(HOUR,dt1) and  datepart(HOUR,dt2)
                    -- 5 minutes slice --
                join (values(0),(5),(10),(15),(20),(25),(30),(35),(40),(45),(50),(55)
                     )minutes(mm) on cast(convert(char(11),dt1,126)+RIGHT('0'+cast(hh as varchar),2)+':'+RIGHT('0'+cast(mm as varchar),2)+':00' as datetime) between cast(convert(char(15),dt1,126)+'0:00' as datetime) and cast(convert(char(16),dt2,126)+':00' as datetime)
                outer apply(select 
                                 [periodeH] = convert(char(11), dt1 ,126)+RIGHT('0'+cast(hh as varchar),2)+':00:00'
                                ,[periode1] = cast(convert(char(11), dt1,126)+RIGHT('0'+cast(hh as varchar),2)+':'+RIGHT('0'+cast(mm as varchar),2)+':00' as datetime)
                                ,[periode2] = dateadd(minute,5,cast(convert(char(11), dt1 ,126)+RIGHT('0'+cast(hh as varchar),2)+':'+RIGHT('0'+cast(mm as varchar),2)+':00' as datetime))
                            )periode
            )a
        where diff>0
)a group by resourceid,periodeH
order by 1,2

Upvotes: 1

Related Questions