T-SQL with datetime

Question

T-SQL with datetime

leo dec 41

Hello

Need to write sql to find datetime between start_Dt and end_Dt so that i can count id to show that id can be counted during that hour.

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 10:42:00.000','2021-08-01 17:15:00.000')

select * From #t

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

Yitzhak Khabinsky 27,196 Reputation points

2021-08-20T15:50:25.72+00:00

While asking a question you need to provide a minimal reproducible example:
(1) DDL and sample data population, i.e. CREATE table(s) plus INSERT, T-SQL statements.
(2) What you need to do, i.e. logic, and your attempt implementation of it in T-SQL.
(3) Desired output based on the sample data in the #1 above.
(4) Your SQL Server version (SELECT @@version;)
MelissaMa-msft 24,246 Reputation points Moderator

2021-08-24T02:37:35.117+00:00

Hi @leo dec ,

Could you please validate all the answers and provide any update?

Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

Thank you for understanding!

Best regards,
Melissa

4 answers

Your answer

Yitzhak Khabinsky 27,196 Reputation points

2021-08-20T15:50:25.72+00:00

While asking a question you need to provide a minimal reproducible example:
(1) DDL and sample data population, i.e. CREATE table(s) plus INSERT, T-SQL statements.
(2) What you need to do, i.e. logic, and your attempt implementation of it in T-SQL.
(3) Desired output based on the sample data in the #1 above.
(4) Your SQL Server version (SELECT @@version;)
MelissaMa-msft 24,246 Reputation points Moderator

2021-08-24T02:37:35.117+00:00

Hi @leo dec ,

Could you please validate all the answers and provide any update?

Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

Thank you for understanding!

Best regards,
Melissa

Answer 1

Hi @leo dec ,

Welcome to Microsoft Q&A!

Please also refer below and check whether it is working.

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')  

;WITH CTE AS (  
SELECT MIN(CONVERT(datetime, CONVERT(varchar(13), start_Dt, 121) + ':00:00')) AS MIND,   
MAX(CONVERT(datetime, CONVERT(varchar(13), end_dt, 121) + ':00:00')) AS MAXD  
FROM #t)  
,hrs AS (  
select MIND dt from CTE  
UNION ALL  
SELECT DATEADD(HOUR, 1, dt) AS [Hour] FROM hrs ,CTE  
WHERE dt < MAXD)  
select isnull(d.cnt,0) cnt,a. dt from hrs a   
left join (  
select b.dt,   
count(*) cnt from hrs b,#t c  
where b.dt between CONVERT(datetime, CONVERT(varchar(13), start_Dt, 121) + ':00:00') and end_dt  
group by b.dt) d  
on a.dt=d.dt

Output:

cnt	dt  
1	2021-07-31 22:00:00.000  
1	2021-07-31 23:00:00.000  
1	2021-08-01 00:00:00.000  
1	2021-08-01 01:00:00.000  
0	2021-08-01 02:00:00.000  
0	2021-08-01 03:00:00.000  
0	2021-08-01 04:00:00.000  
0	2021-08-01 05:00:00.000  
0	2021-08-01 06:00:00.000  
0	2021-08-01 07:00:00.000  
0	2021-08-01 08:00:00.000  
0	2021-08-01 09:00:00.000  
1	2021-08-01 10:00:00.000  
2	2021-08-01 11:00:00.000  
2	2021-08-01 12:00:00.000  
2	2021-08-01 13:00:00.000  
1	2021-08-01 14:00:00.000  
1	2021-08-01 15:00:00.000  
1	2021-08-01 16:00:00.000  
1	2021-08-01 17:00:00.000

Best regards,
Melissa

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Probably the first row of new sample details is incorrect. (Must be '2021-07-31').

Check this query:

;
with Q1 as
(
    select min(start_Dt) [start], max(end_Dt) [end]
    from #t
),
Q2 as
(
    select 
        datetimefromparts( year([start]), month([start]), day([start]), datepart(hour, [start]), 0, 0, 0) dt
    from Q1
    union all
    select dateadd(hour, 1, dt)
    from Q2
    cross apply Q1
    where dateadd(hour, 1, dt) < [end]
)
select 
    (select count(*) from #t where q.dt between datetimefromparts( year([start_Dt]), month([start_Dt]), day([start_Dt]), datepart(hour, [start_Dt]), 0, 0, 0) and end_dt) cnt,
    dt
from Q2 q
order by dt
option (maxrecursion 0)

Answer 3

Guoxiong 8,221

Try this:

;WITH CTE_1 AS (
    SELECT id, 
           CONVERT(datetime, CONVERT(varchar(13), start_Dt, 121) + ':00:00') AS Start_At, 
           CONVERT(datetime, CONVERT(varchar(13), end_dt, 121) + ':00:00') AS End_At
    FROM #t
),
CTE_2 AS (
    SELECT id, Start_At AS Waiting_At, End_At
    FROM CTE_1 AS c1
    UNION ALL
    SELECT id,  DATEADD(HOUR, 1, Waiting_At) AS Waiting_At, End_At
    FROM CTE_2 AS c2
    WHERE Waiting_At < End_At
)

SELECT id, Waiting_At FROM CTE_2 ORDER BY id;

0 comments

Answer 4

Hello

sorry if anything is missing in the question i asked..

SQL version is 2016

i try to explain the desired result again..

logic is to count id on hourly basis between the start_dt and end_dt columns, which means that id 101 should be counted as 1 @ below mentioned date and time
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 id 103 should be counted as 1 @ below mentioned date and time
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

I updated the sample data as well as created the result table as well .`create table #t( id int, start_Dt datetime, end_dt datetime)

create table #t( id int, start_Dt datetime, end_dt datetime)

insert into #t values (101, '2021-08-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)
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

Guoxiong 8,221 Reputation points

2021-08-20T18:16:00.017+00:00

Did you try my script? Can you explain why both 101 and 103 are counted as 1? And also why 102 is 0?

Share via

T-SQL with datetime

4 answers

Your answer