Need help adding dates and null values to results

Question

Post reply

Need help adding dates and null values to results

meichmann

SSCommitted

Points: 1900
More actions
August 25, 2017 at 11:04 am

#339470

Hello Everyone,
I have a table that stores invoice information. Now, invoices are not entered every day so there may be a 1 day gap between invoices.
I need to write a stored procedure that brings back invoice info for every day within a date range, even if no inovices were entered.
I have a temporary table that inserts the date range for each day.
Notice the date for 8/6.....
Here is my stored procedure:
@StartDate DATETIME,
@EndDate DATETIME,
@TempDate DATETIME = @StartDate
declare @temp Table
(
DayDate datetime
);
WHILE @TempDate <= @EndDate
begin
INSERT INTO @temp (DayDate) VALUES (@TempDate);
SET @TempDate = Dateadd(Day,1, @TempDate);
end ;
Select * from @temp
SELECT t.DayDate,
Invoice.GFNo,
Invoice.IDNum,
Invoice.InvoiceDate,
Invoice.Balance,
Invoice.GrandTotal
FROM @temp t
LEFT OUTER JOIN Invoice ON t.DayDate = Invoice.InvoiceDate
WHERE Invoice.InvoiceDate BETWEEN @StartDate AND DATEADD(MS, -1, DATEADD(D, 1, CONVERT(DATETIME2, @EndDate)))
And as you can see below, I need a row for 8/06 but it doesn't show:
I thought I had the procedure correct but I guess not. I just need a date of 8/06 ( or any date that doesn't have Invoice information) with nulls in the columns.
Anyone have any ideas?
Thanks!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

ZZartin SSC-Dedicated Points: 30921 More actions · Answer 1

You should be able to take this out altogether.

WHERE Invoice.InvoiceDate BETWEEN @StartDate AND DATEADD(MS, -1, DATEADD(D, 1, CONVERT(DATETIME2, @EndDate)))

Your temp table already controls the filtering as it's already populated with the date range you want. There's probably also a better to do that without a loop populating the temp table but this should work.

meichmann SSCommitted Points: 1900 More actions · Answer 2

ZZartin - Friday, August 25, 2017 11:21 AM
You should be able to take this out altogether.
WHERE Invoice.InvoiceDate BETWEEN @StartDate AND DATEADD(MS, -1, DATEADD(D, 1, CONVERT(DATETIME2, @EndDate)))
Your temp table already controls the filtering as it's already populated with the date range you want. There's probably also a better to do that without a loop populating the temp table but this should work.

Thank you so much! that worked perfectly!

Phil Parkin SSC Guru Points: 246987 More actions · Answer 3

Some additional advice.
Add SET NOCOUNT ON.
I suggest you use DATE datatypes rather than DATETIME.
Use a temp table rather than a table variable – they usually perform better.
Put a clustered index on the data in the temp table.
INSERT your dates in one hit, rather than using a loop. Here is some sample code to give you an idea how (thank you Jeff Moden)
DECLARE @StartDate DATE --Inclusive , @EndDate DATE --Exclusive , @Days INT;

SELECT @StartDate = '20170101' --Inclusive , @EndDate = '20170201' --Exclusive , @Days = DATEDIFF(dd, @StartDate, @EndDate);

SELECT TOP (@Days) TheDate = DATEADD(dd , ROW_NUMBER() OVER (ORDER BY ( SELECT NULL ) ) - 1 , @StartDate ) FROM sys.all_columns ac1 CROSS JOIN sys.all_columns ac2;

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Avi1 SSCrazy Points: 2323 More actions · Answer 4

One more option is to use CTE to generate dates
Declare @StartDt as Date,
@EndDt as Date

Set @StartDt = '2017-07-01'
Set @EndDt= '2017-07-31'

;WITH CTE_DatesTable
AS
(
SELECT @StartDt AS [date]
UNION ALL
select DATEADD(dd, 1, [date]) AS [date]
from CTE_DatesTable a
WHERE [date] <= DATEADD(dd, -1, @EndDt)
)

select * from CTE_DatesTable

Phil Parkin SSC Guru Points: 246987 More actions · Answer 5

Avi1 - Friday, August 25, 2017 12:40 PM
One more option is to use CTE to generate dates
Declare @StartDt as Date,
@EndDt as Date
Set @StartDt = '2017-07-01'
Set @EndDt= '2017-07-31'
;WITH CTE_DatesTable
AS
(
SELECT @StartDt AS [date]
UNION ALL
select DATEADD(dd, 1, [date]) AS [date]
from CTE_DatesTable a
WHERE [date] <= DATEADD(dd, -1, @EndDt)
)
select * from CTE_DatesTable

This hits the max recursion limit pretty quickly, but nice code.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel