Show all dates with left joins. (1 Viewer)

[Emmanuel]

Good day to all,

I have a very interesting dilema, but hopefully a simple one. I have one table that has the date of the order and the order number. My problem is that I need to show on the results of the query all the dates for a specific month and null values on the dates that don't have any orders. For example:

This is my table as it is now.
Date Order
7/1/2007 123
7/3/2007 142
7/9/2007 851
7/11/2007 552
7/15/2007 225

This is what I would like to see in the results:
Date Order
7/1/2007 123
7/2/2007 Null
7/3/2007 142
7/4/2007 Null
7/5/2007 Null
7/6/2007 Null
7/7/2007 Null
7/8/2007 Null
7/9/2007 851
7/10/2007 Null
7/11/2007 552
7/12/2007 Null
7/13/2007 Null
7/14/2007 Null
7/15/2007 225

I know how to do this with a temp table that has all the dates, but can this be done with a formula? For example a user enters a date range of July 1,2007 to July 15, 2007 and gets the above results.

Thanks in advace for your help.

 

[pdx_man]

This should work (EmmIssue is your table):

DECLARE @StartDate SMALLDATETIME
DECLARE @EndDate SMALLDATETIME
SET @StartDate = '07/01/2007'
SET @EndDate = '07/15/2007'

DECLARE @IncDate SMALLDATETIME
DECLARE @TheResults TABLE (ResDate SMALLDATETIME, ResOrder INT)

SET @IncDate = @StartDate

WHILE @IncDate <= @EndDate
BEGIN
	INSERT INTO @TheResults
	SELECT @IncDate, (SELECT e.TheOrder FROM EmmIssue e WHERE e.TheDate = @IncDate)
	
	SET @IncDate = DATEADD(DAY,1,@IncDate)
END

SELECT * 
FROM @TheResults

 

[Emmanuel]

Awesome. Thanks much!