August 20, 2021 at 3:22 pm
Hello
Need to write sql to find datetime between start_Dt and end_Dt so that ID can be counted at each hour between start_Dt and end_Dt.
sample data is
create table #t( id int, start_Dt datetime, end_dt datetime)
insert into #t values (101, '2021-07-31 22:40:00.000','2021-08-01 01:23:00.000')
insert into #t values (102, '2021-08-01 11:40:00.000','2021-08-01 13:23:00.000')
insert into #t values (103, '2021-08-01 10:42:00.000','2021-08-01 17:15:00.000')
select * From #t
create table #t_result (cnt int, dt datetime) -- result required
insert #t_result values (1, '2021-07-31 22:00:00.000')
insert #t_result values (1, '2021-07-31 23:00:00.000')
insert #t_result values (1, '2021-08-01 00:00:00.000')
insert #t_result values (1, '2021-08-01 01:00:00.000')
insert #t_result values (0, '2021-08-01 02:00:00.000')
insert #t_result values (0, '2021-08-01 03:00:00.000')
insert #t_result values (0, '2021-08-01 04:00:00.000')
insert #t_result values (0, '2021-08-01 05:00:00.000')
insert #t_result values (0, '2021-08-01 06:00:00.000')
insert #t_result values (0, '2021-08-01 07:00:00.000')
insert #t_result values (0, '2021-08-01 08:00:00.000')
insert #t_result values (0, '2021-08-01 09:00:00.000')
insert #t_result values (1, '2021-08-01 10:00:00.000')
insert #t_result values (2, '2021-08-01 11:00:00.000')
insert #t_result values (2, '2021-08-01 12:00:00.000')
insert #t_result values (2, '2021-08-01 13:00:00.000')
insert #t_result values (1, '2021-08-01 14:00:00.000')
insert #t_result values (1, '2021-08-01 15:00:00.000')
insert #t_result values (1, '2021-08-01 16:00:00.000')
insert #t_result values (1, '2021-08-01 17:00:00.000')
select * from #t_result
Need to count that 101 was waiting at
2021-07-31 22:00:00.000,
2021-07-31 23:00:00.000,
2021-08-01 00:00:00.000,
2021-08-01 01:00:00.000
where as id 103 was waiting at
2021-08-01 10:00:00.000,
2021-08-01 11:00:00.000,
2021-08-01 12:00:00.000,
2021-08-01 13:00:00.000,
2021-08-01 14:00:00.000,
2021-08-01 15:00:00.000,
2021-08-01 16:00:00.000,
2021-08-01 17:00:00.000
could you please help me to write the sql script to get the result.
regards
August 20, 2021 at 5:30 pm
This uses a tally function (or it could be called a "numbers function") twice. Once, to generate the total hourly datetime range across all rows in table #t. Second, to individually expand the date ranges in #t table. This article describes the dbo.fnTally function.
with
max_min_cte(t_min_dt, t_max_dt) as (
select min(dateadd(hour, datediff(hour, 0, start_dt), 0)),
max(dateadd(hour, datediff(hour, 0, end_dt), 0))
from #t),
dt_cte(dt) as (
select dateadd(hour, fn.n, mm.t_min_dt)
from max_min_cte mm
cross apply dbo.fnTally(0, datediff(hour, mm.t_min_dt, mm.t_max_dt)) fn),
exp_cte(cnt, dt) as (
select count(*), hr_calc.dt
from #t t
cross apply (values (dateadd(hour, datediff(hour, 0, t.start_dt), 0),
dateadd(hour, datediff(hour, 0, t.end_dt), 0))) conv_hr(hr_start_dt, hr_end_dt)
cross apply dbo.fnTally(0, datediff(hour, conv_hr.hr_start_dt, conv_hr.hr_end_dt)) hr_fn
cross apply (values (dateadd(hour, hr_fn.n, conv_hr.hr_start_dt))) hr_calc(dt)
group by hr_calc.dt)
select d.dt, isnull(e.cnt, 0) cnt
from dt_cte d
left join exp_cte e on d.dt=e.dt
order by d.dt;
dbo.fnTally
CREATE FUNCTION [dbo].[fnTally]
/**********************************************************************************************************************
Jeff Moden Script on SSC: https://www.sqlservercentral.com/scripts/create-a-tally-function-fntally
**********************************************************************************************************************/ (@ZeroOrOne BIT, @MaxN BIGINT)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN WITH
H2(N) AS ( SELECT 1
FROM (VALUES
(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
)V(N)) --16^2 or 256 rows
, H4(N) AS (SELECT 1 FROM H2 a, H2 b) --16^4 or 65,536 rows
, H8(N) AS (SELECT 1 FROM H4 a, H4 b) --16^8 or 4,294,967,296 rows
SELECT N = 0 WHERE @ZeroOrOne = 0 UNION ALL
SELECT TOP(@MaxN)
N = ROW_NUMBER() OVER (ORDER BY N)
FROM H8
;
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
August 20, 2021 at 6:02 pm
Simplified using OUTER APPLY instead of the LEFT JOIN
with
max_min_cte(t_min_dt, t_max_dt) as (
select min(dateadd(hour, datediff(hour, 0, start_dt), 0)),
max(dateadd(hour, datediff(hour, 0, end_dt), 0))
from #t)
select calc.dt, isnull(oa.cnt, 0) cnt
from max_min_cte mm
cross apply dbo.fnTally(0, datediff(hour, mm.t_min_dt, mm.t_max_dt)) fn
cross apply (values (dateadd(hour, fn.n, mm.t_min_dt))) calc(dt)
outer apply (select count(*)
from #t t
cross apply (values (dateadd(hour, datediff(hour, 0, t.start_dt), 0),
dateadd(hour, datediff(hour, 0, t.end_dt), 0))) conv_hr(hr_start_dt, hr_end_dt)
cross apply dbo.fnTally(0, datediff(hour, conv_hr.hr_start_dt, conv_hr.hr_end_dt)) hr_fn
cross apply (values (dateadd(hour, hr_fn.n, conv_hr.hr_start_dt))) hr_calc(dt)
where calc.dt=hr_calc.dt) oa(cnt)
order by calc.dt;
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply