Getting the number of records within a 4 hour basis?

Question

I am currently trying to get the number of records from a repeating time period pattern of 4 hours. Below is an example of the data that I have now.

DT                  LD_VSL_M LD_VOY_N
1/5/2017 10:14:00 AM rmhp epjui 0023S
1/5/2017 10:16:00 AM rmhp epjui 0023S
1/5/2017 10:18:00 AM rmhp epjui 0023S
1/5/2017 10:32:00 AM rmhp epjui 0023S
1/5/2017 11:18:00 AM rmhp epjui 0023S
1/5/2017 11:25:00 AM rmhp epjui 0023S
1/5/2017 11:31:00 AM rmhp epjui 0023S
1/5/2017 12:18:00 PM rmhp epjui 0023S
1/5/2017 10:14:00 AM rfff rfff 0023N
1/5/2017 10:16:00 AM rfff rfff 0023N
1/5/2017 10:18:00 AM rfff rfff 0023N
1/5/2017 10:32:00 AM rfff rfff 0023N
1/5/2017 11:18:00 AM rfff rfff 0023N
1/5/2017 11:25:00 AM rfff rfff 0023N
1/5/2017 11:31:00 AM rfff rfff 0023N
1/5/2017 12:18:00 PM rfff rfff 0023N

ST_C     CT
F     P
F     P
F     P
F           P
F     P
F     P
F     P
F     P
F     P
F     P
F     P
F           P
F     P
F     P
F     P
F     P

For example, if I want to get the number of records on a 4 hour basis, such as 1/5/2017 0:00:00 to 1/5/2017 4:00:00, the number of records based on the example above should be 0. This goes the same for 1/5/2017 4:00:00 to 1/5/2017 8:00:00. However, from 1/5/2017 8:00:00 to 1/5/2017 12:00:00, there should be 7 records. Below is my desired output. When the LD_VSL_N, LD_VOY_N. ST_C and CT changes, it will start counting from 1/5/2017 0:00:00 again.

   DT             no_of_records 
1/5/2017 0:00         0
1/5/2017 4:00         0
1/5/2017 8:00         0
1/5/2017 12:00        7
1/5/2017 16:00        1
1/5/2017 20:00        0
1/5/2017 0:00         0
1/5/2017 4:00         0
1/5/2017 8:00         0
1/5/2017 12:00        7
1/5/2017 16:00        1
1/5/2017 20:00        0
LD_VSL_M LD_VOY_N
rmhp epjui 0023S
rmhp epjui 0023S
rmhp epjui 0023S
rmhp epjui 0023S
rmhp epjui 0023S
rmhp epjui 0023S
rmhp epjui 0023S
rmhp epjui 0023S
rfff rfff 0023N
rfff rfff 0023N
rfff rfff 0023N
rfff rfff 0023N
rfff rfff 0023N
rfff rfff 0023N
rfff rfff 0023N
rfff rfff 0023N

Right now, I am trying to get the no_of_records column. Please do help me as I have been stuck here for quite a while.

@Eli Hi, I am quite new to SQL server, and I have trouble finding out what to try as well! — Jamie, Jul 26 '17 at 13:17
Just change `DATEDIFF(MINUTE, '2017-01-01', TheDateTime) / 30` to `DATEDIFF(HOUR, '2017-01-01', DT) / 4` — Zohar Peled, Jul 26 '17 at 13:18
@Jamie check out my answer in the duplicate link. If you want me to explain anything there, comment on this question with `@zoharpeled` and I'll try my best to answer. — Zohar Peled, Jul 26 '17 at 13:20

score 0 · Accepted Answer · answered Jul 26 '17 at 13:40

0

You can query as below:

;With Cte_hours as ( --hours generation
    Select top(6) hr = (Row_number() over (order by (Select NULL))-1)*4 from master..spt_values 
), cte2 as ( --getting range
    Select DateAdd(HH, c.hr, Convert(datetime,d.dts) ) as Dts_Start, DateAdd(MS, -2, DateAdd(HH, c.hr+ 4, Convert(datetime,d.dts) ) ) Dts_end
    from (select distinct convert(date, dt) as dts from #testData ) d
    cross apply Cte_hours c
) --actual query
Select c2.Dts_Start, Sum(case when t.Dt is not null then 1 else 0 end) 
from cte2 c2
Left Join #testData t
on t.Dt between c2.Dts_Start and c2.Dts_end
group by c2.Dts_Start

Output as below:

+-------------------------+---------------+
|           DT            | No_of_records |
+-------------------------+---------------+
| 2017-01-05 00:00:00.000 |             0 |
| 2017-01-05 04:00:00.000 |             0 |
| 2017-01-05 08:00:00.000 |             7 |
| 2017-01-05 12:00:00.000 |             1 |
| 2017-01-05 16:00:00.000 |             0 |
| 2017-01-05 20:00:00.000 |             0 |
+-------------------------+---------------+

answered Jul 26 '17 at 13:40

Kannan Kandasamy

13,405
3
25
38

Hi! How do I add the rest of the columns into the output as well? – Jamie Jul 26 '17 at 15:11
On what column other attributes will join? – Kannan Kandasamy Jul 26 '17 at 15:13
The other columns that I have stated in my question, LD_VSL_N, LD_VOY_N. ST_C and CT – Jamie Jul 26 '17 at 15:28
It should be taken into account when calculating the no_of_records column – Jamie Jul 26 '17 at 15:30
You might require to add max() those as below : Select c2.Dts_Start as DT, Sum(case when t.Dt is not null then 1 else 0 end) No_of_records ,max(t.LD_VOY_N), max(t.LD_VSL_M) – Kannan Kandasamy Jul 26 '17 at 15:33
max does not give me the right number, when I remove it and group by it, the 4hr pattern is not sorting according to each LD_VOY_N and LD_VSL_M. Do you have any suggestions? – Jamie Jul 26 '17 at 17:01
Then you need to provide sample data accordingly... You might require to include that also in group by – Kannan Kandasamy Jul 26 '17 at 17:14
Hi, I have updated my post to show more clearly of the desired output. Do you think you know what's wrong? – Jamie Jul 26 '17 at 17:53
You just need to add other columns in group by – Kannan Kandasamy Jul 26 '17 at 17:54
Already have done that, but it does not make any difference.. – Jamie Jul 26 '17 at 18:01
Thanks for your help so far – Jamie Jul 26 '17 at 18:18

Getting the number of records within a 4 hour basis?

1 Answers1