Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Aggregate data based on 1 hour intervals


Aggregate data based on 1 hour intervals

Author
Message
DaveDB
DaveDB
SSC-Enthusiastic
SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)

Group: General Forum Members
Points: 113 Visits: 215
Hello,

I need to write a SQL query that aggregates data based on 1 hour intervals. So, basically I would need something that lists all available times: (6:30 - 7:30), (7:30 - 8:30), (8:30 - 9:30), etc. So the INTERVALSTART and END have to be on the :30, but display the full hour. I assume I have to do something in the WHERE clause as well. The column that I am using for hour intervals is date/time.


Any help would be appreciated. Here is what I have so far...

SELECT INTERVAL_START, INTERVAL_END, AGGREGATED_VALUE
FROM TABLE A
LEFT JOIN TABLE B
ON A.logid = G.logid
WHERE convert(varchar(15), INTERVALSTART, 101) = convert(varchar(15),getdate(),101)
AND...... Start and End time
AND Item_Name IN ('Conservation Only','NJ SWAT','NJ Contact Center')
GROUP BY INTERVAL_START, INTERVAL_END, AGGREGATED_VALUE
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16632 Visits: 17024
DaveDB (12/12/2013)
Hello,

I need to write a SQL query that aggregates data based on 1 hour intervals. So, basically I would need something that lists all available times: (6:30 - 7:30), (7:30 - 8:30), (8:30 - 9:30), etc. So the INTERVALSTART and END have to be on the :30, but display the full hour. I assume I have to do something in the WHERE clause as well. The column that I am using for hour intervals is date/time.


Any help would be appreciated. Here is what I have so far...

SELECT INTERVAL_START, INTERVAL_END, AGGREGATED_VALUE
FROM TABLE A
LEFT JOIN TABLE B
ON A.logid = G.logid
WHERE convert(varchar(15), INTERVALSTART, 101) = convert(varchar(15),getdate(),101)
AND...... Start and End time
AND Item_Name IN ('Conservation Only','NJ SWAT','NJ Contact Center')
GROUP BY INTERVAL_START, INTERVAL_END, AGGREGATED_VALUE


It sounds like you need a calendar table. Search around this site, there are several articles and LOTS of threads. You might also check out the article found by following the first link in my signature.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
fahey.jonathan
fahey.jonathan
SSC-Enthusiastic
SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)

Group: General Forum Members
Points: 114 Visits: 395
You could adjust the actual time by 30 minutes so that values that used to fall in to the 6:30-7:30 range now fall into the 6:00-7:00 range, then group by the resulting values for the whole hour. Here is a start.
-- Create a working table.
CREATE -- DROP
TABLE SomeTable
(
SomeDate DATETIME NOT NULL,
SomeValue DECIMAL(12,2) NOT NULL
)

-- Add some records with a date and a random value.
INSERT
INTO SomeTable
(SomeDate, SomeValue)
SELECT DATEADD(MINUTE, 5 * N, GETDATE() - .5),
CAST(LEFT(REVERSE(CAST(RAND(N) AS VARCHAR(20))), 3) AS DECIMAL(12,2)) / 10
FROM (SELECT TOP 200 ROW_NUMBER() OVER (ORDER BY object_id) AS N FROM sys.columns) x

-- Sum the values by the hour, adjusting the hour by 30 minutes to
-- get (ieSmile 6:30-7:30 into the 6:00 block.
SELECT CONVERT(VARCHAR(13), DATEADD(MINUTE, -30, SomeDate), 121) AS BlockTime,
SUM(SomeValue) AS BlockTotal
FROM SomeTable
GROUP BY CONVERT(VARCHAR(13), DATEADD(MINUTE, -30, SomeDate), 121)
ORDER BY CONVERT(VARCHAR(13), DATEADD(MINUTE, -30, SomeDate), 121)


fahey.jonathan
fahey.jonathan
SSC-Enthusiastic
SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)

Group: General Forum Members
Points: 114 Visits: 395
I just realized that the ROW_NUMBER() function is included in the loading script, which will not work in 2K5. This would work instead:
SELECT   (TensValue * 10) + UnitsValue
FROM (SELECT DISTINCT colid AS TensValue FROM sys.syscolumns
WHERE colid BETWEEN 0 AND 9) b
CROSS JOIN (SELECT DISTINCT colid AS UnitsValue FROM sys.syscolumns
WHERE colid BETWEEN 1 AND 10) e
ORDER BY (TensValue * 10) + UnitsValue


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search