March 11, 2008 at 12:55 pm
hi all,
We have a daily sales report that misses days if there are no sales. Is there a simple way to include zero sales days in the report? I can think of complicated ways (i.e. c# manipulation of results) but a nice addition to our sql query would be preferable.
Sample results:
1 2006/08/11
2 2006/08/09
3 2006/08/08
Ideal results:
1 2006/08/11
0 2006/08/10
2 2006/08/09
3 2006/08/08
Sample query:
SELECT count(id), CONVERT(VARCHAR(11),OrderedOn,111)
FROM Orders
GROUP BY CONVERT(VARCHAR(11),OrderedOn,111)
ORDER BY CONVERT(VARCHAR(11),OrderedOn,111) DESC
Your insight is most appreciated.
March 11, 2008 at 1:02 pm
do you have a date table in your db where you could join to that table to get the date and then do a count from order
the sql would look similar to this:
select d.cal_date, count(o.id)
from
tbl_date d
left join orders o
on d.cal_date = o.orderedon
group by d.cal_date
order by d.cal_date
March 11, 2008 at 1:13 pm
In order to get the days you want, even days with no data, you'd have to join your sales data to a dates table. Meaning a table with an unbroken sequence of dates.
here's one method:
declare @startdate datetime
set @startdate ='20000101'
select top 12000
Identity(int,1,1) as N,
dateadd(day,ROW_NUMBER() over (order by sc1.object_id),@startdate) dateval
INTO Dates
from sys.all_columns sc1 cross join sys.all_columns
order by sc1.object_id
Create unique clustered index ucdates on dates(dateval)
Create unique clustered index c_tallyN on dates(N)
You can then do:
select d.dateval, count (o.id)
from
dates d
left outer join orders o
on o.OrderedOn >=d.dateval and
o.OrderedOn <d.dateval+1
group by d.dateval
where d.dateval>'12/31/2007' --presuming you only want stuff this year - change to fit your range
order by d.dateval desc
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 20, 2008 at 5:52 pm
Thanks for the great responses!
Based on your suggestions I ended up constructing a query that uses a Common Table Expression to create a temporary table of dates to join against (I grabbed the CTE code from here: http://blogs.conchango.com/jamiethomson/archive/2007/01/11/T_2D00_SQL_3A00_-Generate-a-list-of-dates.aspx).
A sample is provided below to help others who have a similar challenge.
This query will create a conitiguous list of dates with sales numbers for each day. Days with no sales will be included with zero sales listed.
Thanks again!
//this query will list all days of March with total sales numbers for each day. Days with no sales are included with zero sales.
with mycte as
(
select cast('2008-03-01' as datetime) DateVal
union all
select DateVal + 1
from mycte
where DateVal + 1 < '2008-03-31'
)
select d.dateval, count (o.orderid)
from
mycte d
left outer join orders o
on o.OrderedOn >=d.dateval and
o.OrderedOn <d.dateval+1
group by d.dateval
order by d.dateval desc
March 20, 2008 at 8:30 pm
Thanks for sharing... but your CTE has an error in it... it won't return all of the days of March... it misses the last day because you of the < relationship which shoud be a <= relation ship...
Corrected code is as follows...
;with mycte as
(
select cast('2008-03-01' as datetime) DateVal
union all
select DateVal + 1
from mycte
where DateVal + 1 <= '2008-03-31'
)
select d.dateval
from mycte d
--Jeff Moden
March 20, 2008 at 8:32 pm
Just a programming note... the method that Matt used is a lot faster than recursive CTE's like the one you have over the long haul...
--Jeff Moden
March 22, 2008 at 7:06 pm
Thanks, for the catch there Jeff. You are right in that I was missing the last day's results.
Also, since I only need a month's worth of data at a time I'm willing to risk lesser performance in order to not have to administer another table.
I appreciate the feedback!
March 22, 2008 at 11:34 pm
As another programming note, based on indications you will be using this code again...
DECLARE @DateVal DATETIME
SET @DateVal = '2008-03-01'
;with mycte as
(
select @DateVal AS DateVal
union all
select DateVal + 1
from mycte
where DateVal + 1 < DATEADD(mm, 1, @DateVal)
)
select d.dateval
from mycte d
For the sake of re-usability, use a variable, go back to using the '<' from the first response but add a month to the variable.
An obvious solution, but sometimes we miss the obvious too.
March 23, 2008 at 2:01 pm
ace (3/22/2008)
Also, since I only need a month's worth of data at a time I'm willing to risk lesser performance in order to not have to administer another table.I appreciate the feedback!
Then, at least pick a method that has some performance built into it just in case someone decides to use it in an area of code that will require some performance. Recursion is one of the worst ways to do this type of thing. Although it doesn't take much in the area of CPU time, it's pretty heavy handed in the area of logical reads and duration.
The following code compares the recursive method to two other methods...
SET NOCOUNT ON
--=======================================================================================
-- Recursive method shown by John Beggs
--=======================================================================================
PRINT '========== Recursive method =========='
--===== Turn on some performance counters ===============================================
SET STATISTICS IO ON
SET STATISTICS TIME ON
DECLARE @Bitbucket DATETIME --Holds display output so display times aren't measured.
--===== Execute the code being tested ===================================================
DECLARE @DateVal DATETIME
SET @DateVal = '2008-03-01'
;with mycte as
(
select @DateVal AS DateVal
union all
select DateVal + 1
from mycte
where DateVal + 1 < DATEADD(mm, 1, @DateVal)
)
select @Bitbucket = d.dateval
from mycte d
--===== Turn off the performance counters and print a separator =========================
SET STATISTICS IO OFF
SET STATISTICS TIME OFF
PRINT REPLICATE('=',90)
GO
--=======================================================================================
-- ROW_NUMBER() method by Jeff Moden
--=======================================================================================
PRINT '========== ROW_NUMBER() method =========='
--===== Turn on some performance counters ===============================================
SET STATISTICS IO ON
SET STATISTICS TIME ON
DECLARE @Bitbucket DATETIME --Holds display output so display times aren't measured.
--===== Execute the code being tested ===================================================
DECLARE @StartDate AS DATETIME
SET @StartDate = '2008-03-01'
;WITH cteMonth AS
(
SELECT TOP (DATEDIFF(dd,@StartDate,DATEADD(mm,1,@StartDate)))
@StartDate-1+ROW_NUMBER() OVER (ORDER BY ac1.Object_ID) AS DateValue
FROM Master.sys.All_Columns ac1
)
SELECT @Bitbucket = DateValue
FROM cteMonth
--===== Turn off the performance counters and print a separator =========================
SET STATISTICS IO OFF
SET STATISTICS TIME OFF
PRINT REPLICATE('=',90)
GO
--=======================================================================================
-- Tally table method by Jeff Moden
--=======================================================================================
PRINT '========== Tally table method =========='
--===== Turn on some performance counters ===============================================
SET STATISTICS IO ON
SET STATISTICS TIME ON
DECLARE @Bitbucket DATETIME --Holds display output so display times aren't measured.
--===== Execute the code being tested ===================================================
DECLARE @StartDate AS DATETIME
SET @StartDate = '2008-03-01'
;WITH cteMonth AS
(
SELECT TOP (DATEDIFF(dd,@StartDate,DATEADD(mm,1,@StartDate)))
@StartDate-1+t.N AS DateValue
FROM Tally t
)
SELECT @Bitbucket = DateValue
FROM cteMonth
--===== Turn off the performance counters and print a separator =========================
SET STATISTICS IO OFF
SET STATISTICS TIME OFF
PRINT REPLICATE('=',90)
GO
Here's the output on my machine (notice all dates are created and "thrown away" to keep any differences in displays from affecting the tests)...
========== Recursive method ==========
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
Table 'Worktable'. Scan count 2, logical reads 188, physical reads 0, read-ahead
reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 17 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
==============================================================
========== ROW_NUMBER() method ==========
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
Table 'syscolrdb'. Scan count 1, logical reads 7, physical reads 0, read-ahead
reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'syscolpars'. Scan count 1, logical reads 3, physical reads 0, read-ahead
reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
==============================================================
========== Tally table method ==========
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
Table 'Tally'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0,
lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
==============================================================
Notice that the ROW_NUMBER() Method is almost as good as the Tally Table method for this particular task... but, on larger things like this, the Tally Table will smoke most every other method. Because of it's blazing speed, I use a Tally Table instead of ROW_NUMBER() in many areas of 2k5. Further, Tally Tables enable 2k to achieve the same blazing speed when the only other method available in 2k would be a Cursor or a While Loop.
Yeah, I know you said you don't want another table to maintain... trust me, this one is very worth it and, once setup (takes just a couple of seconds), needs no further maintenance. If you really hate the idea of a utility table, then I recommend you use the ROW_NUMBER() method instead of recursion.
Here's a link to show you how simple it is to make a Tally Table...
http://qa.sqlservercentral.com/scripts/Advanced+SQL/62486/
--Jeff Moden
March 23, 2008 at 2:22 pm
ace (3/20/2008)
Based on your suggestions I ended up constructing a query that uses a Common Table Expression to create a temporary table of dates to join against
Why a temporary table? Why not keep it around in case you need it again? It sholdn't take up that much space.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 23, 2008 at 2:27 pm
I agree with Matt and Barry... a permanent date table would be much faster than any other method. Lot's more convenient, too, because all the dates are always available. Like Barry said, they don't take much room either.
--Jeff Moden
March 24, 2008 at 12:17 pm
Excellent information!
My actual scenario is a little more complicated so the code I displayed was specifically written for this forum to eliminate project specifics.
We are using variables, have no fear. 🙂
Since this is a reporting application it actually pulls data from a number of databases so I was a little reluctant to pin the application to a single database with a physical table. However, I can see the performance impact so I'm going to reconsider.
Thanks again.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply