?
Solved

Procedure to aggregate data by time Interval when dates stored as numbers

Posted on 2009-02-23
12
Medium Priority
?
558 Views
Last Modified: 2012-05-06
I think the answer could be based on http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_21265854.html?sfQueryTermInfo=1+find+interv+time

I have a table which has a field maintdate where the date is stored in format yyyymmdd.
It has a mainttime field where the time is stored as hhmmss.

Over a given day (or range of days) I want to find how many rows have a mainttime in hourly interevals.  e.g a mainttime between 100000 and 110000
0
Comment
Question by:AlHal2
  • 6
  • 3
  • 3
12 Comments
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 23713461
Hope this will do and you would like this more simplistic approach:

SELECT COUNT(HH)
FROM
(
SELECT HOUR(mainttime) HH, DAY(mainttime) DA
FROM urtable
-- where mainttime = ''
) temp
group by HH, DA
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 23713662
SELECT
    SUM(CASE WHEN mainttime >= 000000 AND mainttime < 010000 THEN 1 ELSE 0 END) AS [00AM],
    SUM(CASE WHEN mainttime >= 010000 AND mainttime < 020000 THEN 1 ELSE 0 END) AS [01AM],
    SUM(CASE WHEN mainttime >= 020000 AND mainttime < 030000 THEN 1 ELSE 0 END) AS [02AM],
    SUM(CASE WHEN mainttime >= 030000 AND mainttime < 040000 THEN 1 ELSE 0 END) AS [03AM],
    --...,
    SUM(CASE WHEN mainttime >= 130000 AND mainttime < 140000 THEN 1 ELSE 0 END) AS [01PM],
    --...,
    SUM(CASE WHEN mainttime >= 230000 AND mainttime < 240000 THEN 1 ELSE 0 END) AS [11PM]
FROM tablename
WHERE maintdate BETWEEN 20090201 AND 20090215
0
 

Author Comment

by:AlHal2
ID: 23713750
I get a message 'HOUR' is not a recognized built-in function name.
Also if the hour is earlier than 10 o clock there is no leading zero.

I think it's because maintdate is stored as a date integer and mainttime as a time integer.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 57

Assisted Solution

by:Raja Jegan R
Raja Jegan R earned 200 total points
ID: 23713807
Its a pseudocode:
this will work

SELECT COUNT(HH)
FROM
(
SELECT DATEPART(HOUR, mainttime) HH, DATEPART(DAY,mainttime) DA
FROM urtable
-- where mainttime = ''
) temp
group by HH, DA
0
 

Author Comment

by:AlHal2
ID: 23713907
Thanks Scott.

rrjegan17,

Shouldn't the day function relate to maintdate rather than mainttime?
When I changed it I got this message "Arithmetic overflow error converting expression to data type datetime."
Also I need the column headings as Scott has produced.
0
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 300 total points
ID: 23713930
I thought you wanted totals across all days.  If you want each day individually, do this:

SELECT
    maintdate,
    SUM(CASE WHEN mainttime >= 000000 AND mainttime < 010000 THEN 1 ELSE 0 END) AS [00AM],
    SUM(CASE WHEN mainttime >= 010000 AND mainttime < 020000 THEN 1 ELSE 0 END) AS [01AM],
    SUM(CASE WHEN mainttime >= 020000 AND mainttime < 030000 THEN 1 ELSE 0 END) AS [02AM],
    SUM(CASE WHEN mainttime >= 030000 AND mainttime < 040000 THEN 1 ELSE 0 END) AS [03AM],
    --...,
    SUM(CASE WHEN mainttime >= 130000 AND mainttime < 140000 THEN 1 ELSE 0 END) AS [01PM],
    --...,
    SUM(CASE WHEN mainttime >= 230000 AND mainttime < 240000 THEN 1 ELSE 0 END) AS [11PM]
FROM tablename
WHERE maintdate BETWEEN 20090201 AND 20090215
GROUP BY maintdate
ORDER BY maintdate
0
 

Author Comment

by:AlHal2
ID: 23719509
Thanks again Scott.
If I want to look over a weekly period then I would need 168 sum statements  (24 hours over 7 days).
I was hoping that rrjegan17: would give me a way around that.  
However your solution is more flexible.
Is there any way of combining your flexibility with rrjegan17's use of a subquery.  If it's any use the time intervals are the same and start at midnight.
0
 

Author Comment

by:AlHal2
ID: 23719543
Alternatively, what about having it like this

Date, Time Interval, Number of Rows
20090201,00-01,56
.
.
.
20090201,23-24,72
.
.
.
20090215,15-16,23
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 23719559
AlHal2,
    My query will return results as rows instead of columns as suggested by Scott.
If you ask me to return results like that, then I have to go for Scotts Approach only.

In SQL Server 2005, you can use Pivot concept instead of SUM(CASE ) approach to achieve the same result with better performance. But I hope you use 2000 only.

Scott,
   I doubt in this line alone on your approach:

  SUM(CASE WHEN mainttime >= 230000 AND mainttime < 240000 THEN 1 ELSE 0 END) AS [11PM]

There is no 240000 value at all AFAIK. If you change that to mainttime <= 235959, then I dont have any issues with your approach.
0
 

Author Closing Comment

by:AlHal2
ID: 31550186
Scrap my last post.  Scott's solution is fine.
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 23721463
Yep, good point.  Just remove the "AND mainttime < 240000"; it's not needed.


>> If I want to look over a weekly period then I would need 168 sum statements  (24 hours over 7 days). <<

No, you only ever need 24 SUM() statements.  If you want to sum each day separately, include the day in the SELECT and in a GROUP BY.  I posted an example of this earlier :-) .
0
 

Author Comment

by:AlHal2
ID: 23721748
Thanks Scott.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

615 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question