Re: Help with SQL query; Hourly data by day...

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

On Nov 14, 10:14 pm, Ed Murphy <emurph...@xxxxxxxxxxxx> wrote:
chris.mcp...@xxxxxxxxxxxxxx wrote:
I have a table with email records and I am trying to figure out how
many emails I get every hour each day over a period of several months.
I get the data and time info for this from one column (called
CreateDate) with a DATETIME data type. Here is what I need:

1) There should be 24 columns to represent each hour of the day.

2) Even if no records are found for a specific hour or day I still
need an entry recorded as "0" emails for every hour.

3) The data in the "CreateDate" field looks like this: "7/26/2003
2:01:20 AM"

This will take care of days with 0 emails during some hours:

select year(CreateDate) CreateYear,
month(CreateDate) CreateMonth,
day(CreateDate) CreateDay,
coalesce(sum(case when datepart(hour,CreateDate) = 0 then 1 end))
as EmailCountHour00,
coalesce(sum(case when datepart(hour,CreateDate) = 1 then 1 end))
as EmailCountHour01,
-- similarly for 02 through 23
from the_table
group by year(CreateDate), month(CreateDate), day(CreateDate)

To cover days with 0 emails all day long, I think you need to
create a table containing a row for every date, then query that
table LEFT JOIN the above.

Hi, Not sure if you saw my earlier response (thank you profusely if I
submitted it wrong and you didn't see it) but here is how the code
looks now:

select year(CreateDate) CreateYear,
month(CreateDate) CreateMonth,
day(CreateDate) CreateDay,
coalesce(sum(case when datepart(hour,CreateDate) = 0 then 1
end))
as EmailCountHour00,
coalesce(sum(case when datepart(hour,CreateDate) = 1 then 1
end))
as EmailCountHour01,
coalesce(sum(case when datepart(hour,CreateDate) = 2 then 1 end))
as EmailCountHour02,
coalesce(sum(case when datepart(hour,CreateDate) = 3 then 1 end))
as EmailCountHour03,
coalesce(sum(case when datepart(hour,CreateDate) = 4 then 1 end))
as EmailCountHour04,
coalesce(sum(case when datepart(hour,CreateDate) = 5 then 1 end))
as EmailCountHour05,
coalesce(sum(case when datepart(hour,CreateDate) = 6 then 1 end))
as EmailCountHour06,
coalesce(sum(case when datepart(hour,CreateDate) = 7 then 1 end))
as EmailCountHour07,
coalesce(sum(case when datepart(hour,CreateDate) = 8 then 1 end))
as EmailCountHour08,
coalesce(sum(case when datepart(hour,CreateDate) = 9 then 1 end))
as EmailCountHour09,
coalesce(sum(case when datepart(hour,CreateDate) = 10 then 1 end))
as EmailCountHour10,
coalesce(sum(case when datepart(hour,CreateDate) = 11 then 1 end))
as EmailCountHour11,
coalesce(sum(case when datepart(hour,CreateDate) = 12 then 1 end))
as EmailCountHour12,
coalesce(sum(case when datepart(hour,CreateDate) = 13 then 1 end))
as EmailCountHour13,
coalesce(sum(case when datepart(hour,CreateDate) = 14 then 1 end))
as EmailCountHour14,
coalesce(sum(case when datepart(hour,CreateDate) = 15 then 1 end))
as EmailCountHour15,
coalesce(sum(case when datepart(hour,CreateDate) = 16 then 1 end))
as EmailCountHour16,
coalesce(sum(case when datepart(hour,CreateDate) = 17 then 1 end))
as EmailCountHour17,
coalesce(sum(case when datepart(hour,CreateDate) = 18 then 1 end))
as EmailCountHour18,
coalesce(sum(case when datepart(hour,CreateDate) = 19 then 1 end))
as EmailCountHour19,
coalesce(sum(case when datepart(hour,CreateDate) = 20 then 1 end))
as EmailCountHour20,
coalesce(sum(case when datepart(hour,CreateDate) = 21 then 1 end))
as EmailCountHour21,
coalesce(sum(case when datepart(hour,CreateDate) = 22 then 1 end))
as EmailCountHour22,
coalesce(sum(case when datepart(hour,CreateDate) = 23 then 1 end))
as EmailCountHour23
from qem_message
WHERE DATEPART(mm, createdate) > 4 AND DATEPART(mm, createdate) < 11
AND DATEPART(yyyy, createdate)= 2007
group by year(CreateDate), month(CreateDate), day(CreateDate)


Unfortunately it spits out an error when I try to run it:

Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near ')'.

Any thoughts on how I got the syntax wrong? Thank again. Chris.
.

Relevant Pages

  • Re: Help with SQL query; Hourly data by day...
    ... many emails I get every hour each day over a period of several months. ... I get the data and time info for this from one column (called ... CreateDate) with a DATETIME data type. ... as EmailCountHour00, ...
    (microsoft.public.sqlserver)
  • Re: Help with SQL query; Hourly data by day...
    ... many emails I get every hour each day over a period of several months. ... I get the data and time info for this from one column (called ... CreateDate) with a DATETIME data type. ... as EmailCountHour00, ...
    (microsoft.public.sqlserver)