SQL Query Help

Question

Hi

I need help to write sql query. business logic is that i need to add each id to the time from the time they are in till they are out. for eg.. if id time_in started at 11:35 and time_out is 12:20 then id should get counted from 11:30 to 12:30

sample data is

create table #t1(id int, dt date, time_in time(7), time_out time(7)) insert into #t1 values(101, '2021-03-22','12:23','13:46') insert into #t1 values(102, '2021-03-22','11:26','16:46') insert into #t1 values(103, '2021-03-22','14:50','15:25') insert into #t1 values(104, '2021-03-22','12:23','13:46') insert into #t1 values(105, '2021-03-23','14:49','16:55') insert into #t1 values(106, '2021-03-23','15:25','18:00') insert into #t1 values(107, '2021-03-24','16:05','18:27') insert into #t1 values(108, '2021-03-24','10:14','18:30') insert into #t1 values(109, '2021-03-24','11:05','15:02') select * from #t1

result needed in attached screenshot.

hope i explained in properly. regards ![82002-book-1.gif][1] [1]: /api/attachments/82002-book-1.gif?platform=QnA

Answer 1

HI @leo dec ,

Welcome to Microsoft Q&A!

Based on Erland's query, please refer below and check whether it is working:

drop table if exists #t1, #halfhours  
  
create table #t1  
(id int, dt date,   
time_in time(7),   
time_out time(7))   
insert into #t1 values(101, '2021-03-22','12:23','13:46')   
insert into #t1 values(102, '2021-03-22','11:26','16:46')   
insert into #t1 values(103, '2021-03-22','14:50','15:25')   
insert into #t1 values(104, '2021-03-22','12:23','13:46')   
insert into #t1 values(105, '2021-03-23','14:49','16:55')   
insert into #t1 values(106, '2021-03-23','15:25','18:00')   
insert into #t1 values(107, '2021-03-24','16:05','18:27')   
insert into #t1 values(108, '2021-03-24','10:14','18:30')   
insert into #t1 values(109, '2021-03-24','11:05','15:02')  
  
CREATE TABLE #halfhours (hh smalldatetime NOT NULL PRIMARY KEY);  
INSERT #halfhours(hh)  
SELECT dateadd(MINUTE, 30*(number-1), '20210322')  
FROM    master..spt_values WHERE TYPE = 'p'  
and  number >= 1   
AND  dateadd(MINUTE, 30*(number-1), '20210322') < '20210325';   
  
;with cte as (  
SELECT convert(date, h.hh) date,convert(time(0), h.hh) period, COUNT(*) count  
FROM   #halfhours h  
JOIN   #t1 t ON convert(date, h.hh) = t.dt  
            AND convert(time(0), h.hh) BETWEEN t.time_in AND dateadd(minute, 29, t.time_out)  
GROUP  BY h.hh)  
,cte1 as (  
select distinct convert(time(0), a.hh) hour,b.date,b.count from #halfhours a  
inner join cte b on convert(time(0), a.hh)=b.period)  
select * from  
(select * from cte1) s  
pivot   
(max(count) for date in ([2021-03-22],[2021-03-23],[2021-03-24])) p  

Output:

hour	2021-03-22	2021-03-23	2021-03-24  
10:30:00	NULL	NULL	1  
11:00:00	NULL	NULL	1  
11:30:00	1	NULL	2  
12:00:00	1	NULL	2  
12:30:00	3	NULL	2  
13:00:00	3	NULL	2  
13:30:00	3	NULL	2  
14:00:00	3	NULL	2  
14:30:00	1	NULL	2  
15:00:00	2	1	2  
15:30:00	2	2	2  
16:00:00	1	2	1  
16:30:00	1	2	2  
17:00:00	1	2	2  
17:30:00	NULL	1	2  
18:00:00	NULL	1	2  
18:30:00	NULL	NULL	2  

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

Here is a solution:

CREATE TABLE #halfhours (hh smalldatetime NOT NULL PRIMARY KEY);

INSERT #halfhours(hh)
   SELECT dateadd(MINUTE, 30*(n-1), '20100101')
   FROM   dbo.Numbers
   WHERE  n >= 1 
     AND  dateadd(MINUTE, 30*(n-1), '20100101') < '20300101'; 

SELECT hh.hh, COUNT(*)
FROM   #halfhours hh
JOIN   #t1 t ON convert(date, hh.hh) = t.dt
            AND convert(time(0), hh.hh) BETWEEN t.time_in AND t.time_out
GROUP  BY hh.hh
ORDER  BY hh.hh

It does not match the expected output exactly, but I can't see how you could expect to get three for the slot of 2021-03-22 14:00:00.

To learn about the table dbo.Numbers, check out my article Using a Table of Numbers.

In this query, I created #halfhours as a temp table, but you may prefer to make it a permanent table.

The

Answer 3

Hi

Thanks for your quick reply

count 3 showing in 2021-03-22 14:00 is 1 count for each 101,104,102 as all 3 ids were in @14:00

hope this helps.

regards

Answer 4

OK, so from what you say above, an id should be counted as in @ 14.00, if the preson left 13:31 or later.

In such case, this modification gives you the result:

CREATE TABLE #halfhours (hh smalldatetime NOT NULL PRIMARY KEY);

INSERT #halfhours(hh)
   SELECT dateadd(MINUTE, 30*(n-1), '20100101')
   FROM   NorthNumbers.dbo.Numbers
   WHERE  n >= 1 
     AND  dateadd(MINUTE, 30*(n-1), '20100101') < '20300101'; 

SELECT hh.hh, COUNT(*)
FROM   #halfhours hh
JOIN   #t1 t ON convert(date, hh.hh) = t.dt
            AND convert(time(0), hh.hh) BETWEEN t.time_in AND dateadd(minute, 29, t.time_out)
GROUP  BY hh.hh
ORDER  BY hh.hh

But the result does still not match your image where only have 1 for a few hours on the 24th, despite that two ids were in for the better part of the day.