It’s almost one month that I didn’t write anything on the blog due to some personal reason. I am really sorry for that.

Now , Lets talk about the scenario sometimes we need to generate a report of total sales in particular date range but the condition is you need to show all the dates whether there was any sales or not.

So first and most important thing for us is to determine all the dates between that particular date range and then determine total sales date wise.

To determine the all the dates which reside between from date & to date  we have 2 approches

First the classic approach with while loop  as shown below

DECLARE @StartDate AS DATE = '2005-07-01'
DECLARE @EndDate   AS DATE = '2005-07-29'
DECLARE @tblDateRange AS TABLE (salesDate DATE)
DECLARE @SeedDate AS DATE
SET @SeedDate = '2005-07-01'
WHILE @SeedDate <= @EndDate
BEGIN
  INSERT INTO @tblDateRange(salesDate) Values (@SeedDate)
  SET @SeedDate  = DATEADD(d,1,@seedDate)
END
SELECT * FROM @tblDateRange

Now second and interesting approach

DECLARE @StartDate AS DATE = '2005-07-01'
DECLARE @EndDate   AS DATE = '2005-07-29'
DECLARE @tblDateRange AS TABLE (salesDate DATE)

;WITH DatesCTE
AS (
SELECT @StartDate AS SalesDate
UNION ALL
SELECT DATEADD(d,1, SalesDate) As salesDate
FROM DatesCTE
WHERE DATEADD(d,1,SalesDate) <= @EndDate)

INSERT INTO @tblDateRange(salesDate)
SELECT * FROM DatesCTE

SELECT * FROM @tblDateRange

These are the 2 simple approaches which I like. I appreciate if you share other approaches which are simple & interesting.

Thanks

RJ

Enjoy !!!