question

leodec-4354 avatar image
0 Votes"
leodec-4354 asked MelissaMa-msft edited

T-SQL with datetime

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


sql-server-transact-sql
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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

0 Votes 0 ·

Hi @leodec-4354,

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

0 Votes 0 ·
leodec-4354 avatar image
0 Votes"
leodec-4354 answered GuoxiongYuan-7218 edited

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


· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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

0 Votes 0 ·
GuoxiongYuan-7218 avatar image
0 Votes"
GuoxiongYuan-7218 answered GuoxiongYuan-7218 edited

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;
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Viorel-1 avatar image
0 Votes"
Viorel-1 answered Viorel-1 edited

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)


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered

Hi @leodec-4354,

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.