December 12, 2013 at 2:35 pm
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
December 12, 2013 at 3:08 pm
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 12, 2013 at 5:02 pm
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
TABLESomeTable
(
SomeDateDATETIMENOT NULL,
SomeValueDECIMAL(12,2)NOT NULL
)
-- Add some records with a date and a random value.
INSERT
INTOSomeTable
(SomeDate, SomeValue)
SELECTDATEADD(MINUTE, 5 * N, GETDATE() - .5),
CAST(LEFT(REVERSE(CAST(RAND(N) AS VARCHAR(20))), 3) AS DECIMAL(12,2)) / 10
FROM(SELECTTOP 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 (ie:) 6:30-7:30 into the 6:00 block.
SELECTCONVERT(VARCHAR(13), DATEADD(MINUTE, -30, SomeDate), 121) AS BlockTime,
SUM(SomeValue) AS BlockTotal
FROMSomeTable
GROUP BY CONVERT(VARCHAR(13), DATEADD(MINUTE, -30, SomeDate), 121)
ORDER BY CONVERT(VARCHAR(13), DATEADD(MINUTE, -30, SomeDate), 121)
December 12, 2013 at 5:14 pm
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply