Merging results grouped by date with missing dates

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 2005 Forums

Transact-SQL (2005)

Merging results grouped by date with missing dates

Author

Topic

sdp
Starting Member

12 Posts

Posted - 2007-11-07 : 04:45:30

Hi folks, I found it tricky to explain what I am trying to achieve in the small subject field, hopefully I can explain myself well enough to get some decent feedback. I'm sure what I'm trying to do has been done many times before, I just can't find the right google phrase to find an example!

I have a query which returns a list of Years, Months, Categories and Totals(£'s), something like...


2007, 10, Mileage, £334
2007, 11, Public Transport, £220
2007, 11, Mileage, £120
2007, 11, Hospitality, £500

In my data there will quite often be months of the year where some categories haven't got any sub total, but I need the query to return...


2007, 10, Public Transport, £0***
2007, 10, Mileage, £334
2007, 10, Hospitality, £0***
2007, 11, Public Transport, £220
2007, 11, Mileage, £120
2007, 11, Hospitality, £500

*** Indicates row currently missing from dataset

I somehow need to union my results with each category that isn't in the results set. Currently my query looks like this:


set dateformat dmy;
SELECT DATEPART(YY,eo.DateAuthorised) AS 'Year', DATEPART(mm,eo.DateAuthorised) AS 'Month', ec.Name AS 'Category', SUM(ei.Amount) AS 'Total'

FROM ExpensesItems ei RIGHT JOIN ExpensesCategory ec ON ec.ID = ei.Category JOIN ExpensesOverview eo ON ei.ExpenseID=eo.ID 

WHERE eo.DateAuthorised >= '01/01/2007' AND eo.DateAuthorised <= '08/11/2007'
		
GROUP BY DATEPART(YY,eo.DateAuthorised), DATEPART(mm,eo.DateAuthorised), ec.Name

ExpensesItems contains a list of individual items that make up a whole claim, they each have a category and subtotal. The grand total is stored in ExpensesOverview whilst the categories are stored in ExpensesCategories.

I know I probably then want to union my results with some sort of list of dates/categories but I don't know how to do that. I'm familiar with the 'WITH x AS' statement, which I think may help here, but my SQL really isn't that great.

I realise this is my first post here, but I do plan to stay around as I really want to improve my SQL. Thanks for any help :)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-07 : 05:15:59

[code]-- Prepare sample data to mimic your environment
DECLARE @Sample TABLE (theYear SMALLINT, theMonth TINYINT, theTitle VARCHAR(20), thePrice SMALLMONEY)

INSERT @Sample
SELECT 2007, 10, 'Mileage', £334 UNION ALL
SELECT 2007, 11, 'Public Transport', £220 UNION ALL
SELECT 2007, 11, 'Mileage', £120 UNION ALL
SELECT 2007, 11, 'Hospitality', £500

-- Show the expected output
SELECT Yak.theYear,
Yak.theMonth,
Yak.theTitle,
COALESCE(s.thePrice, 0) AS thePrice
FROM (
SELECT DISTINCT s1.theYear,
s2.theMonth,
s3.theTitle
FROM @Sample AS s1
CROSS JOIN @Sample AS s2
CROSS JOIN @Sample AS s3
) AS Yak
LEFT JOIN @Sample AS s ON s.theYear = Yak.theYear
AND s.theMonth = Yak.theMonth
AND s.theTitle = Yak.theTitle[/code]

E 12°55'05.25"
N 56°04'39.16"

sdp
Starting Member

12 Posts

Posted - 2007-11-07 : 05:39:34

Thank you very much! It works well, I don't fully understand HOW it works just yet, but I will be looking it up :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources