CODEWITHSUNDEEP
sql-servert-sqlsql-server-2012
Ell
Ell

Reputation: 163

GROUP BY and JOIN on a 3 hour increment

I have 2 tables - one that has one record for every 3 hours:

    a.aDT        a.aVal
------------    -----------
8/10/15 00:00   1550
8/10/15 03:00   1600
8/10/15 06:00   1650

I have second table that has a value every minute:

b.bDT           b.bVal
--------      ------------
8/10/15 00:00     1
8/10/15 00:01     1
8/10/15 00:02     0

I need to wind up with a query that has the value/datetime from the 3 hour sample (a.aDT, a.aVal) in two columns, and the sum of the values from b (b.bVal) in the third column. I've tried GROUP BY (DATEPART(HOUR,bDT) % 3) but that doesn't seem to return what I'm looking for.

Thoughts?

Upvotes: 0

Views: 152

Answers (3)

APH
APH

Reputation: 4154

The following will round your seconds values up to the nearest three-hour interval. For example, values between 9:00 and 11:59 will be rounded to the 12:00 sample, and values between 12:00 and 14:59 will be rounded to the 15:00 sample. Depending on how you want to handle values occurring exactly on your cutoffs, you may want to adjust them slightly.

For example, if you want values occurring at 3:00 exactly to be in the 3:00 group, rather than the 6:00 group, you should subtract a second from your bDt before using the logic below. 

Select a.aDT, a.aVal, sum(b.bVal)
from TableA a
left join TableB b
on datepart(HH, a.aDT) 
    = (datepart(HH, b.bDt) - datepart(HH, bDt) % 3 + 3) % 24 --Convert the time in table B to be the next highest multiple of three
and cast(a.aDt as date) 
    = cast(b.bDt as date) --Make sure the samples are from the same day
group by a.aDT, a.aVal

Upvotes: 3

Jahirul Islam Bhuiyan
Jahirul Islam Bhuiyan

Reputation: 799

try this

select a.aDT ,
       a.aVal,
       (select sum(b.bVal) from table2 b where b.bDT between a.aDT and dateadd(hh,3,a.aDT) )
from table1 a

sum between three hour

Upvotes: 0

Dave C
Dave C

Reputation: 7402

Try something like this:

DECLARE @DATA TABLE (bDT DATETIME, bVal INT)

INSERT INTO @DATA (bDT, bVal)
SELECT '2015-08-10 00:00', 1 UNION
SELECT '2015-08-10 00:01', 1 UNION
SELECT '2015-08-10 00:02', 0 UNION
SELECT '2015-08-10 03:00', 1 UNION
SELECT '2015-08-10 04:00', 1 UNION
SELECT '2015-08-10 06:15', 0 UNION
SELECT '2015-08-10 06:15', 1 

SELECT *, DATEADD(HH,(DATEPART(HH,CONVERT(TIME,bDT))/3)*3, CONVERT(DATETIME,CONVERT(DATE,bDT))) Matching_A_Hour
FROM @DATA

SELECT DATEADD(HH,(DATEPART(HH,CONVERT(TIME,bDT))/3)*3, CONVERT(DATETIME,CONVERT(DATE,bDT))) Matching_A_Hour, SUM(bVal)
FROM @DATA
GROUP BY DATEADD(HH,(DATEPART(HH,CONVERT(TIME,bDT))/3)*3, CONVERT(DATETIME,CONVERT(DATE,bDT)))

Upvotes: 0

Related Questions