Force appearance of missing dates in Select

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)

Force appearance of missing dates in Select

Author

Topic

R
Constraint Violating Yak Guru

328 Posts

Posted - 2009-12-18 : 06:29:27

I am running a SELECT statement on a table to produce a list of costs asssociated with training and development exercises.

The results table shows cost SUMs grouped by month, along with different currencies, like this:


Date			regionCultureCode	Totals
-------------------------------------------------
December 2003	£ GBP 		175.00
February 2004	£ GBP 		1,880.00
December 2004	£ GBP 		970.00
February 2005	£ GBP 		1,020.00
August 2005		£ GBP 		200.00
October 2005	£ GBP 		409.00
January 2007	£ GBP 		4,010.00
January 2007	$ USD 		14,950.00
January 2007	kr NOK 		250.00
February 2007	£ GBP 		720.00
March 2007		£ GBP 		2,010.00
April 2007		£ GBP 		1,720.00
May 2007		£ GBP 		1,420.00
December 2009	£ GBP 		5,850.00
December 2009	$ USD 		3,570.00
December 2009	kr NOK 		780.00

This is achieve by running an initial SELECT statement and saving the results into a table varialbe (@tmp). I then run the query to group the results:


SELECT
	SUM(costAmount) AS [Totals],
	datename(month, [date])+' '+CAST(Year(date) AS nvarchar) AS [Date],
	regionCultureCode
FROM 
	@tmp 
GROUP BY
	regionCultureID, regionCultureCode, datename(month, [date])+' '+CAST(Year(date) AS nvarchar), month([date]), year([date])
ORDER BY
	year([date]),
	month([date]),
	regionCultureCode

How can I get the results to show EVERY month even if there is no data for that particular month? Ideally I'd like to show every currency for every month between the first and last months (these dates being used as parameters in the intial query).

Please help!

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-12-18 : 07:47:21

You'll need a table with all the months in it that you can left join to @tmp
Jim

DECLARE @Months TABLE (Months datetime)

INSERT INTO @Months
SELECT DATEADD(month,spt.number,'01/01/2000')
FROM
 master..spt_values spt

where type = 'P'
and spt.number < 240


SELECT
	SUM(ISNULL(costAmount)) AS [Totals],
	datename(month, [date])+' '+CAST(Year(date) AS nvarchar) AS [Date],
	regionCultureCode
FROM 

 @months m
left join
	@tmp  t
on
  m.months =   dateadd(month,datediff(month,0,t.date),0)
GROUP BY
	regionCultureID, regionCultureCode, datename(month, [date])+' '+CAST(Year(date) AS nvarchar), month([date]), year([date])
ORDER BY
	year([date]),
	month([date]),
	regionCultureCode

Everyday I learn something that somebody else already knew

R
Constraint Violating Yak Guru

328 Posts

Posted - 2009-12-18 : 11:08:07

Hi Jim

Thanks for your response. Rather than use the master table to get the month numbers, could you please show me how to amend the code to take the months from two datetime parameters, @startdate and @enddate?

Thanks again.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources