Need help adding dates and null values to results
-
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!
-
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.
- ZZartin - Friday, August 25, 2017 11:21 AM
Thank you so much! that worked perfectly!
-
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; -
One more option is to use CTE to generate dates
Declare @StartDt as Date,
@EndDt as DateSet @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
- Avi1 - Friday, August 25, 2017 12:40 PM
This hits the max recursion limit pretty quickly, but nice code.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply