CODEWITHSUNDEEP
sqldatetime
mhopkins321
mhopkins321

Reputation: 3073

Counting total number or things that happen each hour in sql

I have a list of roughly 3000 rows. Each row has a different end time. see below

Fund name | Fund ID   | RedeemTime  | PurchaseTime | LatestTime  |
__________  _________  ____________   ____________ | ___________ |
Title1    | IDnumber  |  4:00:00    | 14:30:00     |  14:30:00   |
Title2    | IDnumber2 | 13:30:00    | 12:00:00     |  13:30:00   |
Title3    | IDnumber3 | 10:00:00    | 14:00:00     |  14:00:00   |
Title4    | IDnumber4 | 10:00:00    | 10:30:00     |  10:30:00   |

I am currently using a case statement for the 5th column to find the later of the two times.

I need to take the 5th column and provide a count of what happens each hour. For example on the table above the results would be

Hour     |  Count
_________ _______
10:00:00 |    1
13:00:00 |    1
14:00:00 |    2

Upvotes: 0

Views: 332

Answers (2)

Andomar
Andomar

Reputation: 238048

You could "normalize" the two times in a subquery. You can generate a list of hours using a CTE. For example, for all hours in August 2012:

; with  HourList as
        (
        select  cast('2012-08-01' as datetime) as DateCol
        union all
        select  dateadd(hour, 1, DateCol)
        from    HourList
        where   dateadd(hour, 1, DateCol) < '2012-09-01'
        )
select  hr.DateCol
,       count(yt.StartTime)
from    HourList hr
left join
        (
        select  case when time1 > time2 then time1 else time2 end as EndTime
        from    YourTable
        ) yt
on      yt.EndTime <= hr.DateCol and hr.DateCol < dateadd(hour, 1, yt.EndTime)
group by
        hr.DateCol
option  (maxrecursion 0)

If you don't have a table filled with hours, there are ways to generate it. They all depend on your database. So if you'd like help with that, post which database you are using.

Upvotes: 3

Kermit
Kermit

Reputation: 34054

Assuming you are using only a time field...

First, select which time is greater:

(CASE WHEN time2 > time1 THEN time2
ELSE time1) as time

Then you would SELECT a COUNT with GROUP BY HOUR(time)

Upvotes: 1

Related Questions