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 !!!
Comments
First, use of a recursive CTE is not "simple", it's more advanced than a simple "While" loop. The use of a recursive CTE is elegant and efficient, but it's definitely above the level of a simple "While" loop.
Second, why not be consistent with your naming convention? "@tbl" is a table type. CTE is a table type. If you name one table "@tblDateRange", shouldn't you name the CTE table "cteDates"?
You can also use a tally table to generate those dates, however the CTE is probably a better approach. See an example of a tally implementation below (in a real life scenario the tally table should be a real table, not just a temporary object):
CREATE TABLE #tally (N INT)
INSERT INTO #tally(N)
SELECT ROW_NUMBER() OVER (ORDER BY c1.column_id) – 1 N
FROM sys.columns c1
CROSS JOIN sys.columns c2
DECLARE @StartDate DATE, @EndDate DATE, @Days INT
SET @StartDate = '2005-07-01'
SET @EndDate = '2005-07-29'
SET @Days = DATEDIFF(DAY, @StartDate, @EndDate)
SELECT DATEADD(DAY, N, @StartDate) Date
FROM #tally
WHERE N <= @Days
DROP TABLE #tally
Here is a solution with out a loop or CTE.
DECLARE @StartDate AS DATE = '2005-07-01'
DECLARE @EndDate AS DATE = '2005-07-29'
DECLARE @tblDateRange AS TABLE (salesDate DATE)
DECLARE @SeedDate AS DATE
INSERT INTO @tblDateRange
select top (DATEDIFF(day,@StartDate,@EndDate)+1) dateadd(day,Row_number() over(partition by @StartDate order by @StartDate)-1,@StartDate) Dates
FROM sys.columns C1, sys.columns C2
select *
from @tblDateRange