Self Join Issue - Including previous month value, value or not 2

[AndyHorn]

Hi,

This is a bit long, sorry.

I've been stuck for days trying to get a specific result set and I hope somebody can help.

The results I'm after look simple:
Category, OrderValue, PrevMonth, Date

I need the results to show ALL categories for ALL days and populate a value in the OrderValue and/or PrevMonth fields if there was a sale on that day in that category, otherwise it should be NULL.

The PrevMonth value is the OrderValue one month prior to the Date shown in the results. To get the PrevMonth value to work correctly, I've created a Calendar table with every date from 2005 to 2010 (to cover the range of data). My first query returns every date in the Calendar with an OrderValue for each Category that was sold on that day. If there wasn't a sale on a day then it wont show the Category.

Sample of results from Query1:
Date, OrderValue, Category
2009-09-04, 49.9900, CAT1
2009-09-04, 42.9700, CAT2
2009-09-04, .0000,CAT3
2009-09-04, 446.4500, CAT4
2009-09-04, 35.9600, CAT5
2009-09-04, 183.9700, CAT6
2009-09-04, 299.9900, CAT7
2009-09-04, .0000,NULL
2009-09-04, 7679.6400, CAT8
2009-09-04, 290.4500, CAT9
2009-09-05, NULL,NULL
2009-09-06, NULL,NULL
2009-09-07, 20.9800, CAT6
2009-09-07, 199.9900, CAT10
2009-09-07, 54.9800, CAT2
2009-09-07, 49.9900, CAT1
2009-09-07, 24.9900, CAT5
2009-09-07, 475.4000, CAT9
2009-09-07, 12929.4000, CAT8
2009-09-07, 682.2900, CAT4
2009-09-07, .0000, NULL
2009-09-07, 6.9500, CAT3
2009-09-07, 209.9800, CAT7

My second query uses the results of the first query and performs a self join on the Date field in order to return the PrevMonth value, but it then has to join on the Category as well in order for the correct value to display by the correct Date and Category. This is where it falls over because there might not always be a Category for the prior month if there were no sales on that day.

Sample of results from Query2 (missing days 2009-09-05 & 2009-09-06):

2009-09-04, 49.9900, CAT1
2009-09-04, 42.9700, CAT2
2009-09-04, .0000, CAT3
2009-09-04, 446.4500, CAT4
2009-09-04, 35.9600, CAT5
2009-09-04, 183.9700, CAT6
2009-09-04, 299.9900, CAT7
2009-09-04, 7679.6400, CAT8
2009-09-04, 290.4500, CAT9
2009-09-07, 49.9900, CAT1
2009-09-07, 54.9800, CAT2
2009-09-07, 6.9500, CAT3
2009-09-07, 682.2900, CAT4
2009-09-07, 24.9900, CAT5
2009-09-07, 20.9800, CAT6
2009-09-07, 209.9800, CAT7
2009-09-07, 12929.4000, CAT8
2009-09-07, 475.4000, CAT9
2009-09-07, 199.9900, CAT10

To get the results I want I think I need to get all the Categories to show next to every date in Query1 whether there was a sale or not.

Something like this:
Date, OrderValue, Category
2009-09-04, 49.9900, CAT1
2009-09-04, 42.9700, CAT2
2009-09-04, .0000,CAT3
2009-09-04, 446.4500, CAT4
2009-09-04, 35.9600, CAT5
2009-09-04, 183.9700, CAT6
2009-09-04, 299.9900, CAT7
2009-09-04, 7679.6400, CAT8
2009-09-04, 290.4500, CAT9
2009-09-04, 90.1300, CAT10
2009-09-05, NULL, CAT1
2009-09-05, NULL, CAT2
2009-09-05, NULL, CAT3
2009-09-05, NULL, CAT4
2009-09-05, NULL, CAT5
2009-09-05, NULL, CAT6
2009-09-05, NULL, CAT7
2009-09-05, NULL, CAT8
2009-09-05, NULL, CAT9
2009-09-05, NULL, CAT10
2009-09-06, NULL, CAT1
2009-09-06, NULL, CAT2
2009-09-06, NULL, CAT3
2009-09-06, NULL, CAT4
2009-09-06, NULL, CAT5
2009-09-06, NULL, CAT6
2009-09-06, NULL, CAT7
2009-09-06, NULL, CAT8
2009-09-06, NULL, CAT9
2009-09-06, NULL, CAT10
2009-09-07, 49.9900, CAT1
2009-09-07, 54.9800, CAT2
2009-09-07, 6.9500, CAT3
2009-09-07, 682.2900, CAT4
2009-09-07, 24.9900, CAT5
2009-09-07, 20.9800, CAT6
2009-09-07, 209.9800, CAT7
2009-09-07, 12929.4000, CAT8
2009-09-07, 475.4000, CAT9
2009-09-07, 199.9900, CAT10

Query2 should then work shouldn't it?.
How can I achieve these results above? I've tried all sorts of joins to no avail.

The code of my queries:

Query 1 (dbo.usr_vw_DespatchedOrdersByCategory2):

SELECT TOP 100 PERCENT DATEADD(dd, 0,DATEDIFF(dd, 0, Cal.CalDate)) as CalDate, SUM(oih.GrossValue) AS OrderValue,
Cat.Category
FROM dbo.CategoryCodes Cat RIGHT OUTER JOIN
dbo.Stock ON Cat.CategoryCode = dbo.Stock.CategoryCode RIGHT OUTER JOIN
dbrderItemHeader oih ON dbo.Stock.Sku = oih.Sku RIGHT OUTER JOIN
(SELECT CalDate
FROM Dashboard.dbo.usr_Calendar
WHERE (CalDate < GETDATE())) Cal ON DATEADD(dd, 0, DATEDIFF(dd, 0, oih.ItemDate)) = Cal.CalDate
GROUP BY DATEADD(dd, 0, DATEDIFF(dd,0, Cal.CalDate)), Cat.Category
ORDER BY DATEADD(dd, 0, DATEDIFF(dd, 0, Cal.CalDate))

Query2:
SELECT TOP 100 PERCENT Cat.Category, SUM(Curr.OrderValue) AS OrderValue, SUM(Prev.OrderValue) AS PrevMonth,
Curr.CalDate
FROM CategoryCodes Cat LEFT JOIN dbo.usr_vw_DespatchedOrdersByCategory2 Curr ON Cat.Category = Curr.Category LEFT OUTER JOIN
dbo.usr_vw_DespatchedOrdersByCategory2 Prev ON DateAdd(m,-1,Curr.[Date]) = Prev.[Date] AND
Prev.Category = Cat.Category
WHERE Cat.Type = 3
GROUP BY ALL Curr.CalDate, Cat.Category
ORDER BY Curr.CalDate, Cat.Category


I hope somebody can understand all that. Any guidance appreciated.

Thanks

 

[RiverGuy]

You need a date table, which has one record per date, between y and z dates. For example, the schema would look like this:

DateTable
----------
ActualDate DATETIME
MonthName VARCHAR(20)
IsHoliday BIT
etc...

And the data would look like this

2009-01-01     January 2009     1
2009-01-02     January 2009     0
etc...

Obviously, the first column is the important one you are after. The second two are just possibilities for data to store which describes the date.

Anyways, once you have your date table, you can outer join your transactions onto the date table to make sure no dates are missed.

 

[AndyHorn]

Thanks for your suggestion RiverGuy.

I have already created a Calendar table which my first query references in order for every date to be returned. What I can't achieve is to get every Category returned with every date and to display the values or nulls if a value doesn't exist for that category on that date.

I hope that makes more sense than my original post?

Thanks

 

[RiverGuy]

When you want to return every combination between two tables, you can use a CROSS JOIN.

For example, if you have 365 dates in your calendar table, and 5 product categories, then the following query would yield a count of 1825--one row for each date/product category combination

SELECT c.DateColumn, p.ProductCategory
FROM CalendarTable c
CROSS JOIN ProductCategoryTable p

You could then use that resultset as the basis for your query, and outer join your transactions to it.

SELECT a.*,
ISNULL(b.SalesAmount, 0) AS SalesAmount
FROM

(SELECT c.DateColumn, p.ProductCategory
FROM CalendarTable c
CROSS JOIN ProductCategoryTable p) a

LEFT OUTER JOIN

(SELECT DateColumn, ProductCategory, SUM(SalesAmount) AS SalesAmount FROM TransactionTable GROUP BY DateColumn, ProductCategory
)  b

ON a.DateColumn = b.DateColumn
AND a.ProductCategory = b.ProductCategory

 

[AndyHorn]

Thanks RiverGuy, you're a legend. The Cross Join was exactly what I needed. I'd heard about them before but had never read how they worked or seen one used so it didn't even cross my mind to look at using it. The name of it makes perfect sense!

 

[AndyHorn]

I'm stuck on one last thing and hope you can help.

In the results of my query I have a DespatchValue field and a PrevMonth field amongst others. The PrevMonth field should show the DespatchValue from the previous month, against the current month.

I nearly got this to work by using this in the join: DateAdd(m, - 1, ST.CalDate) = Prev.Date.

The problem is that by doing it on a date field, not all months have the same amount of days. So I then grouped my data by year and month, DatePart(yyyy,Sales.[Date]),DatePart(mm,Sales.[Date]) and used the this in the join: ST.[Year] = Prev.[Year] AND (ST.MonthNo)-1 = Prev.MonthNo

That doesn't work in any January month because it doesn't know to roll back a year as well as a month.

Is there a simple way to achieve this?

My Code as grouped by month and year:
(please excuse the improper use of the reserved words)

SELECT TOP 100 PERCENT ST.CategoryCode, ST.Description, ISNULL(Curr.OrderValue,0) as DespatchValue, ISNULL(Prev.OrderValue,0)as PrevMonth, 0 as CreditValue, 0 as Quantity, ST.[Month], ST.[MonthNo], ST.[Year]
FROM
(SELECT TOP 100 PERCENT Cat.CategoryCode, Cat.DESCRIPTION, Cal.[Month], Cal.MonthNo, Cal.[Year]
FROM dbo.CategoryCodes Cat CROSS JOIN
(SELECT TOP 100 PERCENT DateName(m,CalDate)as Month, DatePart(mm,CalDate) as MonthNo, DatePart(yyyy,CalDate)as Year
FROM Dashboard.dbo.usr_Calendar
WHERE (CalDate < GETDATE())) Cal
WHERE Cat.Type = 3 AND CategoryCode NOT IN ('SH01','SH02','HD','NEW')
GROUP BY Cat.CategoryCode, Cat.Description, Cal.[Month], Cal.MonthNo, Cal.[Year]
ORDER BY Cal.[Year], Cal.MonthNo, Cat.CategoryCode) ST --SourceTable
LEFT OUTER JOIN
(SELECT TOP 100 PERCENT Sales.CategoryCode, Sales.Description, SUM(Sales.OrderValue) AS OrderValue, DateName(m,Sales.[Date])as Month, DatePart(mm,Sales.[Date]) as MonthNo, DatePart(yyyy,Sales.[Date])as Year
FROM dbo.usr_vw_DespatchedOrdersByCategory Sales
GROUP BY Sales.CategoryCode, Sales.Description, DatePart(yyyy,Sales.[Date]),DatePart(mm,Sales.[Date]),DateName(m,Sales.[Date]))Curr
ON ST.[Year] = Curr.[Year] AND ST.MonthNo = Curr.MonthNo AND ST.CategoryCode = Curr.CategoryCode LEFT OUTER JOIN
(SELECT TOP 100 PERCENT Sales.CategoryCode, Sales.Description, SUM(Sales.OrderValue) AS OrderValue, DateName(m,Sales.[Date])as Month, DatePart(mm,Sales.[Date]) as MonthNo, DatePart(yyyy,Sales.[Date])as Year
FROM dbo.usr_vw_DespatchedOrdersByCategory Sales
GROUP BY Sales.CategoryCode, Sales.Description, DatePart(yyyy,Sales.[Date]),DatePart(mm,Sales.[Date]),DateName(m,Sales.[Date]))Prev
ON ST.[Year] = Prev.[Year] AND (ST.MonthNo)-1 = Prev.MonthNo AND ST.CategoryCode = Prev.CategoryCode

Thanks

 

[RiverGuy]

Try replacing your last line (the last one with the ON clause) with this:

ON ((ST.MonthNo <> 1 AND ST.[Year] = Prev.[Year] AND (ST.MonthNo)-1 = Prev.MonthNo) OR (ST.MonthNo = 1 AND ST.[Year]  -1 = Prev.[Year] AND Prev.MonthNo = 12)) 
AND (ST.CategoryCode = Prev.CategoryCode)

 

[AndyHorn]

Thanks again RiverGuy. Worked a treat!