Group By Day() Problems - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Group By Day() Problems

Author

Topic

Blastrix
Posting Yak Master

208 Posts

Posted - 2003-05-08 : 13:27:54

I'm trying to return some data that I need to use for reporting purposes, but am having some problems.

I'm trying to put together a count of the performed actions by user over the course of the month, broken down by day. The problem I'm having is that if the user did not perform any actions on a given day, then they do not get represented for that day, and it plays all kinds of hell with the graphing application I'm using.

What I need is for the action count for a user to be represented as 0 for a day in which they do not perform any actions. Getting this has proved impossible for me so far.

I'm sure someone out there has encountered this before, and maybe knows how to do what I'm looking for. Any suggestions?

Thanks,
Steve

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-05-08 : 13:41:29

Create a (temp?) table with all the days that you want to report on and OUTER JOIN to that table your other base tables that you are working with.


select
    d.myday,
    count(t.primarykey)
from
    #ABunchOfDays d
    left join mytable t
        on d.myday = dateadd(dd,datediff(dd,0,t.somedatetime),0)
group by
    d.myday

Jay White
{0}

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-05-08 : 13:46:03

Or use a tally table to create every day in your date range, and then a LEFT OUTER JOIN to your data (grouped by day)...

Come on, Jay, how could you not mention that !!

(search the site for info on a tally table if you need it)

To generate all of your days:

select dateadd(d, number, @startdate) as Date
From Tally
where number <= datediff(d,@startdate, @endDate)

Then:

Select a.Date, b.Value
FROM
(above SQL) a
LEFT OUTER JOIN
YourData b
on
a.date = b.date

- Jeff

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-05-08 : 13:48:18

note: if your report returns a range of records for the month for each user, you use a cross join:

SELECT users.user, dates.date, data.data
FROM
users
CROSS JOIN
dates ( the SQL from above)
LEFT OUTER JOIN
data
ON
dates.date = data.date AND
users.user = data.user

or something along those lines. Hope this makes sense.

- Jeff

Blastrix
Posting Yak Master

208 Posts

Posted - 2003-05-08 : 14:16:35

Awesome! I had the temp table going for the days, and I had the users who were active in the period, I just couldn't tie it together. The cross join did the trick though. I don't know why, but I always forget about that type of join.

Thanks again,
Steve

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-05-08 : 15:59:02

Glad I could help.

(that's why they call me Mr Cross Join!)

- Jeff

jung1975
Aged Yak Warrior

503 Posts

Posted - 2003-05-08 : 16:46:01

Jeff,

May be I am missing something.
Can you explain to me how to create a Tally table in this situation?. I have read a Rob's article. but still confusing...

Jung

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-05-08 : 18:33:05

It is just a table of numbers, from 1 (or 0) to whatever .... you don't create it each time, you just store it in your database for use wherever you might need it.

just create it once; do a simple loop to fill up a table with 1 field and then leave it there.

A good count is usually 1000 numbers. then you can just do a cross join as many times as you need if you ever need a great deal more.

do some searching in the forums for tally tables and you'll see lots of uses. But the key concept is you don't populate this table each time a query or stored proc is run, you create it once. Many DBA's store it in a centralized "toolkit" database outside of the rest of their databases on their server. in this database you can put custom general-purpose functions and stored procs and all that.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources