How to generate distinct Month/ Years between two dates 1

[eo]

SQL Server 2008 (Std)

I have a SP, but the data generated will ignore where no transactions have occurred. For example the first dataset is grouped in the second, and you can see April 2010 obviously ommitted as there where now data in April 2010:

Dataset 1:
TransDate  Amount
01/01/2010 100.00
06/01/2010 150.00
28/02/2010 120.00
28/02/2010  90.00
15/03/2010 160.00
10/05/2010 300.00
11/05/2010 250.00

Dataset 2:
TransMonth Amount(SUM)
Jan 2010   250.00
Feb 2010   210.00
Mar 2010   160.00
May 2010   550.00

What I need to show is:

Dataset 2:
TransMonth Amount(SUM)
Jan 2010   250.00
Feb 2010   210.00
Mar 2010   160.00
Apr 2010     0.00
May 2010   550.00

The only way I can think of achieving this is to create a dummy dataset that shows all months for the lower and upper date parameters (for example between and including 01/01/2010 to 31/05/2010). Such a query would return
Jan 2010, Feb 2010, Mar 2010, Apr 2010 and May 2010

...and then to use this as the primary table in the query with a left join to the rest. Can anyone tell me how such a dummy data output can be achieved using a @StartDate and @EndDate parameter

EO
Hertfordshire, England

 

[RiverGuy]

It's common to have a Date table in a database. It might contain columns such as Date, Day of Week, Month, Week of Year, Is Holiday, etc.

If you had a date table, you could use that to join your transactions against. I would suggest a permanent date table over having your stored procedure generate a temporary table on the fly.

Here's an example to fill up a temporary Date table with the dates from Jan 1 2000 through Jan 1 2020, along with the month name and year. It would be easy to modify to make this into a permanent table once you get the columns you need.

CREATE TABLE #DateTable 
(Date_Value DATETIME,
 Month_Name VARCHAR(100),
 Year_Value INT)
 
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME

SELECT @StartDate = '20000101'
SELECT @EndDate = '20200101'
WHILE @StartDate <= @EndDate 
BEGIN
	INSERT INTO #DateTable SELECT @StartDate, DATENAME(month, @StartDate), YEAR(@StartDate)
	SELECT @StartDate = DATEADD(DAY, 1, @StartDate) 
END

SELECT * FROM #DateTable 


DROP TABLE #DateTable