Prev: rows to columns
Next: SMO
From: Mufasa on 30 Jun 2008 09:35
So I have an order entry system and I need to show a report of the number of
orders per day. People want it to show even when there is no orders for that
date. So the only way I can think of doing it is create a temp table that
contains all of the dates in my range, and then join that with my order
counts so that I have each and every date.

Is there an easier way?

TIA - Jeff.


From: Paddy on 30 Jun 2008 09:53
Jeff

You can use a calendar table or a CTE.

The following CTE is a copy from a previous post by Plamen Ratchev

Post =

http://groups.google.co.uk/group/microsoft.public.sqlserver.programming/browse_thread/thread/9236f26d00f7f414/d908c7c7bc18a7a1?hl=en&lnk=st&q=cte#d908c7c7bc18a7a1



WITH Last7Days (calc_date)
AS
(SELECT DATEADD(DAY, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP) - 6, 0)
UNION ALL
SELECT DATEADD(DAY, 1, calc_date)
FROM Last7Days
WHERE DATEADD(DAY, 1, calc_date) < CURRENT_TIMESTAMP)
SELECT calc_date
FROM Last7Days;


Paddy
From: Tom Cooper on 30 Jun 2008 10:02
You have the right idea, except that you don't want to build a temporary
table each time. You should build a permanant "calendar" table. Then you
just use that table with a WHERE clause that selects all the dates in the
range you are interested in. For more info on calendar tables, how to build
one, and some of thier many uses, see
http://www.aspfaq.com/show.asp?id=2519.

Tom

"Mufasa" <jb(a)nowhere.com> wrote in message
news:%23SkecYr2IHA.5472(a)TK2MSFTNGP06.phx.gbl...
> So I have an order entry system and I need to show a report of the number
> of orders per day. People want it to show even when there is no orders for
> that date. So the only way I can think of doing it is create a temp table
> that contains all of the dates in my range, and then join that with my
> order counts so that I have each and every date.
>
> Is there an easier way?
>
> TIA - Jeff.
>
>


From: TheRealRobbie on 30 Jun 2008 10:10
Mufasa,

Actually, using a "dates/numbers" table like you described is the best
(fastest, most efficient, easiest to understand) way to tackle that kind of
problem.
Figuring out the maximum needed number of dates should not be too much of an
obstacle.

Also, take a look at this article that describes some useful stuff you can
do with a "numbers" table:
http://www.sqlservercentral.com/articles/Advanced+Querying/2547/

Regards, Rob

"Mufasa" wrote:

> So I have an order entry system and I need to show a report of the number of
> orders per day. People want it to show even when there is no orders for that
> date. So the only way I can think of doing it is create a temp table that
> contains all of the dates in my range, and then join that with my order
> counts so that I have each and every date.
>
> Is there an easier way?
>
> TIA - Jeff.
>
>
>
From: SQL Menace on 30 Jun 2008 10:43
I always use a number table if I don't have to deal with Holidays
closed markets etc etc

See here: http://wiki.lessthandot.com/index.php/Date_Ranges_Without_Loops


Denis The SQL Menace
http://www.lessthandot.com/
http://sqlservercode.blogspot.com
http://sqlblog.com/blogs/denis_gobo/default.aspx


On Jun 30, 10:10 am, TheRealRobbie
<TheRealRob...(a)discussions.microsoft.com> wrote:
> Mufasa,
>
> Actually, using a "dates/numbers" table like you described is the best
> (fastest, most efficient, easiest to understand) way to tackle that kind of
> problem.
> Figuring out the maximum needed number of dates should not be too much of an
> obstacle.
>
> Also, take a look at this article that describes some useful stuff you can
> do with a "numbers" table:http://www.sqlservercentral.com/articles/Advanced+Querying/2547/
>
> Regards, Rob
>
>
>
> "Mufasa" wrote:
> > So I have an order entry system and I need to show a report of the number of
> > orders per day. People want it to show even when there is no orders for that
> > date. So the only way I can think of doing it is create a temp table that
> > contains all of the dates in my range, and then join that with my order
> > counts so that I have each and every date.
>
> > Is there an easier way?
>
> > TIA - Jeff.- Hide quoted text -
>
> - Show quoted text -

 |  Next  |  Last
Pages: 1 2
Prev: rows to columns
Next: SMO