I have a table in database with numbers of tenants, each tenant lists a record of their sales per date. There are instance where in a tenant has NO SALES in particular date/s, therefore the date with no sales has NO RECORD in the table breaking a proper date sequence. Please see the sample table for illustration below:
I used this select query in SQL to display the output above
select tenant, date, sales
from tblSales
where date between '01/01/2015' and '01/05/2014'
What I need as a correct output: display complete date based on the selected date range on the where clause, when tenant has no record in a particular date, the query should add a record of date in that particular tenant and just add null value in the sales column like in this image:
Here's what I have started:
@dateFrom datetime = '02/01/2015',
@dateTo date = '02/05/2015'
declare @MaxNumDays int
declare @Counter int
set @Counter = 0
set @MaxNumDays = DATEDIFF(day, @dateFrom , @dateto) + 1
create table #DSRTdate (
Date datetime
)
WHILE @Counter < @MaxNumDays
BEGIN
insert into #DSRTdate (Date) values (DATEADD(day,@Counter,@dateFrom ))
SET @Counter += 1
END
I used the above codes to get and insert in a temporary table the sequence data from the use selection, in the above case, it inserts 02/01/2015, 02/02/2015, 02/03/2015, 02/04/2015, AND 02/05/2015
select tenantcode, date, sales
into #DSRT2
from DAILYMOD
where (date between @dateFrom and @dateTo)
select *
from #dsrtdate a
left join #DSRT2 b on a.date = b.date
order by b.tenantcode, a.date
Then I used left join to display the missing dates but this results only to ONE TENANT only and it makes also the tenantname null. Like this:
Any suggestions would be highly appreciated.
You could do this using a Tally Table.
Basically, you use the Tally Table
to generate sequence of dates from @startDate
to @endDate
and CROSS JOIN
it to DISTINCT Item
to generate all Date
-Item
combination. Then, the result will be LEFT-JOIN
ed to tblSales
to achieve the desired output.
DECLARE
@startDate DATE = '20140101',
@endDate DATE = '20140105';
WITH E1(N) AS(
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
)
,E2(N) AS(SELECT 1 FROM E1 a, E1 b)
,E4(N) AS(SELECT 1 FROM E2 a, E2 b)
,Tally(N) AS(
SELECT TOP (DATEDIFF(DAY, @startDate, @endDate) + 1)
ROW_NUMBER() OVER(ORDER BY(SELECT NULL))
FROM E4
)
,CteAllDates(Item, dt) AS(
SELECT x.Item, DATEADD(DAY, N - 1, @startDate)
FROM Tally
CROSS JOIN(
SELECT DISTINCT Item
FROM tblSales
WHERE [Date] BETWEEN @startDate AND @endDate
) AS x
)
SELECT d.*, ts.Sales
FROM CteAllDates d
LEFT JOIN tblSales ts
ON ts.Item = d.Item
AND ts.Date = d.dt
WHERE
ts.[Date] BETWEEN @startDate AND @endDate
ORDER BY d.Item, d.dt
Here is an alternative. Instead of the cascading CTE
s, use sys.columns
to generate the Tally Table
.:
DECLARE
@startDate DATE = '20140101',
@endDate DATE = '20140105';
WITH Tally(N) AS(
SELECT TOP (DATEDIFF(DAY, @startDate, @endDate) + 1)
ROW_NUMBER() OVER(ORDER BY(SELECT NULL))
FROM sys.columns a, sys.columns b
)
,CteAllDates(Item, dt) AS(
SELECT x.Item, DATEADD(DAY, N - 1, @startDate)
FROM Tally
CROSS JOIN(
SELECT DISTINCT Item
FROM tblSales
WHERE [Date] BETWEEN @startDate AND @endDate
) AS x
)
SELECT d.*, ts.Sales
FROM CteAllDates d
LEFT JOIN tblSales ts
ON ts.Item = d.Item
AND ts.Date = d.dt
WHERE
ts.[Date] BETWEEN @startDate AND @endDate
ORDER BY d.Item, d.dt
Result
| Item | dt | Sales |
|---------|------------|--------|
| tenant1 | 2014-01-01 | 100 |
| tenant1 | 2014-01-02 | 100 |
| tenant1 | 2014-01-03 | 100 |
| tenant1 | 2014-01-04 | NULL |
| tenant1 | 2014-01-05 | 100 |
| tenant2 | 2014-01-01 | 100 |
| tenant2 | 2014-01-02 | NULL |
| tenant2 | 2014-01-03 | NULL |
| tenant2 | 2014-01-04 | 100 |
| tenant2 | 2014-01-05 | NULL |
| tenant3 | 2014-01-01 | 100 |
| tenant3 | 2014-01-02 | NULL |
| tenant3 | 2014-01-03 | 100 |
| tenant3 | 2014-01-04 | NULL |
| tenant3 | 2014-01-05 | 100 |