Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Aggregate data based on 1 hour intervals Expand / Collapse
Author
Message
Posted Thursday, December 12, 2013 2:35 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, February 24, 2014 11:06 AM
Points: 109, 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
Post #1522486
Posted Thursday, December 12, 2013 3:08 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:14 PM
Points: 13,139, Visits: 11,980
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)
Post #1522503
Posted Thursday, December 12, 2013 5:02 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 8:54 AM
Points: 106, Visits: 363
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 (ie:) 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)

Post #1522524
Posted Thursday, December 12, 2013 5:14 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 8:54 AM
Points: 106, Visits: 363
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

Post #1522531
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse