Systems Engineering and RDBMS

Including non-existent transaction dates

Posted by decipherinfosys on June 17, 2010

This was a question from one of our readers:

“We have a report in which we aggregate the product sales in a wide variety of ways.  And then when we drill into it, we want to show the data on a daily basis – which works fine when there is a sale for a product on that day but if there is no sale for a product on a day in a store, that record does not show up – what I want to show is the date and then 0 against the quantity column for that product in the store.  And I do not want to create dummy records in the table just to make the report work.  Is there any way to do this in SQL?”

This is a very common requirement and we have covered it before in some of our blog posts where we talked about the number table generation or a calendar table generation using a UDF or a CTE (MVP Itzik has also published several articles on this).   Using AdventureWorks2008R2 as the database, and using the Production.TransactionHistory table, here is how we can achieve this:

declare @startdate datetime, @enddate datetime
select @startdate = '2007-10-07', @enddate = '2007-10-10'


;with
N1 (n) AS (SELECT 1 UNION ALL SELECT 1),
N2 (n) AS (SELECT 1 FROM N1 AS X, N1 AS Y),
N3 (n) AS (SELECT 1 FROM N2 AS X, N2 AS Y),
N4 (n) AS (SELECT 1 FROM N3 AS X, N3 AS Y),
N5 (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY X.n%10) - 1 FROM N4 AS X, N4 AS Y)

SELECT
DATEADD(dd, n*1, @startdate)            as Transaction_Date
,    P.Name                                    as Product_Name
,    P.ProductNumber                            as Product_Number
,    Coalesce(PT.Quantity, 0)                as Quantity_Sold
,    Coalesce(PT.ActualCost, 0.00)            as Actual_Cost
FROM N5
cross join production.Product as p
LEFT OUTER JOIN Production.TransactionHistory    AS PT
ON DATEADD(dd, n*1, @startdate) = PT.TransactionDate
and p.ProductID = pt.ProductID
WHERE DATEADD(dd, n*1, @startdate) <= @enddate

So, what we have done above is that we have first generated an incremental list of the dates from the start date to the end date.  If we execute just that portion of the code:

declare @startdate datetime, @enddate datetime
select @startdate = '2007-10-07', @enddate = '2007-10-10'

;with
N1 (n) AS (SELECT 1 UNION ALL SELECT 1),
N2 (n) AS (SELECT 1 FROM N1 AS X, N1 AS Y),
N3 (n) AS (SELECT 1 FROM N2 AS X, N2 AS Y),
N4 (n) AS (SELECT 1 FROM N3 AS X, N3 AS Y),
N5 (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY X.n%10) - 1 FROM N4 AS X, N4 AS Y)

SELECT
DATEADD(dd, n*1, @startdate)            as Transaction_Date
FROM N5
WHERE DATEADD(dd, n*1, @startdate) <= @enddate

We will get this output:

2007-10-07 00:00:00.000
2007-10-08 00:00:00.000
2007-10-09 00:00:00.000
2007-10-10 00:00:00.000

So, we got an incremental series of dates.  Then we did a cross join with the products to make sure that for every date between the start date and the end date, we have a list of the products for each of those dates.  And then we took this set and did a LEFT OUTER JOIN with the TransactionHistory table with 2 join conditions – one for the list of the dates that we generated through the CTE and the other for the productID.  That way, if for a given product on a given date, there was no transaction – it will not have a record in the TransactionHistory table but our SQL will still spew that record out as NULL and we can simply coalesce() that output to 0 for quantity and 0.00 for the actual_cost.

You can run the code above in AdventureWorks2008R2 and see the output.  You will see that it will give you a list of the transaction date – all products in the system on that date and their quantity and actual costs will be listed next to them.

Using this simple method, we can generate the data with SQL without a need to create dummy records in the system just for the sake of reporting.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: