March 13, 2020 at 11:56 pm
Hi,
I am looking for a solution to this problem:
Anyone has a good idea of how to solve it?
March 14, 2020 at 1:26 am
possible way - not the prettiest and probably not the best one - and not sure if it will work for all periods you may have
code adapted from https://dba.stackexchange.com/questions/174035/split-duration-hourly-depending-on-start-and-end-time
Requirements copied from stackoverflow
I am trying to timebox start and end times by the full hour and calculate how many seconds per each hour that were used. With full hour I mean for instance 15:00:00 to 16:00:00. I cannot figure out a way to do this in SQL because of boundary issues that happens at midnights and rows spanning over. I have the following input and outputs:
Table data:
Start End
2020-03-06 15:30:40.000 2020-03-06 17:09:01.000
2020-03-06 22:47:52.000 2020-03-06 23:48:52.000
2020-03-06 23:49:52.000 2020-03-07 00:47:52.000
2020-03-09 17:05:26.000 2020-03-09 18:05:26.000
2020-03-09 18:05:32.000 2020-03-09 19:05:26.000
Columns in the output are: Year Month Day Hour SecondsInFullHour
Output (query on 2020-03-06)
.
.
.
2020 03 06 12 0
2020 03 06 13 0
2020 03 06 14 0
2020 03 06 15 1760
2020 03 06 16 3600
2020 03 06 17 541
2020 03 06 18 0
2020 03 06 19 0
2020 03 06 20 0
2020 03 06 21 0
2020 03 06 21 0
2020 03 06 22 728
2020 03 06 23 3540 (truncated due to day limit of 00:00:00 the following day)
Output (query on 2020-03-07)
2020 03 07 00 2872 (calculated from row 3)
Output (query on 2020-03-09)
2020 03 09 17 3274
2020 03 09 18 3594
2020 03 09 19 326
declare @workdate datetime = '2020-03-06'
;
-- may need more rows here depending on how long the events can be
WITH E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
),
numbers (n) AS ( SELECT top 24 row_number() over (order by a.n) - 1 FROM E1 a, E1 b)
, inpdata as
(
select convert(datetime, starttime) as Start_timestamp
, convert(datetime, Endtime) as End_timestamp
from (values ('2020-03-06 15:30:40.000', '2020-03-06 17:09:01.000')
,('2020-03-06 22:47:52.000', '2020-03-06 23:48:52.000')
,('2020-03-06 23:49:52.000', '2020-03-07 00:47:52.000')
,('2020-03-09 17:05:26.000', '2020-03-09 18:05:26.000')
,('2020-03-09 18:05:32.000', '2020-03-09 19:05:26.000')
) t (Starttime, Endtime)
)
, workday as
(
SELECT
convert(varchar(13), start_final, 121) as period
, convert(date, start_final) as perioddt
, sum(DATEDIFF(second, start_final, end_final)) Duration
FROM
(
SELECT Start_timestamp
, End_Timestamp
, CASE WHEN t.Start_timestamp > n.start_from_numbers THEN t.Start_timestamp ELSE n.start_from_numbers END start_final
, CASE WHEN t.End_timestamp > n.end_from_numbers THEN n.end_from_numbers ELSE t.End_timestamp END end_final
FROM inpdata t
CROSS APPLY
(
SELECT dateadd(hour, n.n + datediff(hour, 0, t.Start_timestamp), 0) start_from_numbers
, dateadd(hour, 1 + n.n + datediff(hour, 0, t.Start_timestamp), 0) end_from_numbers
FROM numbers n WHERE DATEDIFF(HOUR, t.Start_timestamp, t.End_Timestamp) >= n.n
) n
) t2
group by convert(varchar(13), start_final, 121)
,convert(date, start_final)
)
select workdate
, coalesce(work.duration, 0) as duration
from numbers
cross apply (select convert(varchar(13), dateadd(hour, numbers.n, @workdate), 121) as workdate) dt
outer apply (select *
from workday wd
where wd.period = dt.workdate
) work
March 17, 2020 at 11:22 am
is there a way to use some sort of ranking function to do this?
something like partition by datepart(hour, somefield)
then use that as a subquery.... i'm just throwing out ideas here
MVDBA
March 17, 2020 at 2:22 pm
use a "minutes" table, or create one in your CTE like Frederico did, I like Jeremiah Peschka's solution here: http://facility9.com/2010/02/24/rounding-to-the-nearest-x-minutes
-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply