使用 Tally 表在 SQL 中按日期排序填充缺失的日期 [英] Fill Missing Dates In a Date-Sequenced in SQL using Tally Table

问题描述

我在数据库中有一个包含租户数量的表格，每个租户列出了他们每个日期的销售记录.在某些情况下，租户在特定日期没有销售，因此没有销售的日期在表格中没有打破正确日期顺序的记录.请参阅下面的示例表:

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:

我在 SQL 中使用了这个选择查询来显示上面的输出

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'

我需要什么作为正确的输出:根据 where 子句中选定的日期范围显示完整日期，当租户在特定日期没有记录时，查询应在该特定租户中添加日期记录并添加销售列中的空值，如下图所示:

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:

1. 作为我最初的解决方案，我想创建一个临时表，根据所选日期范围插入一系列日期，并使用它与实际表左连接.

下面是我的开始:

@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
            
            

我使用上述代码从使用选择中获取并插入临时表中的序列数据，在上述情况下，它插入 02/01/2015, 02/02/2015, 02/03/2015, 02/04/2015 和 02/05/2015

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.

基本上，您使用 Tally Table 生成从 @startDate 到 @endDate 和 CROSS JOIN 将其以DISTINCT Item 生成所有Date-Item 组合.然后，结果将被 LEFT-JOINed 到 tblSales 以实现所需的输出.

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-JOINed to tblSales to achieve the desired output.

SQL 小提琴

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

<小时>

这是另一种选择.代替级联的 CTE，使用 sys.columns 来生成 Tally Table.:

---

Here is an alternative. Instead of the cascading CTEs, 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

<小时>

结果

|    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 |

这篇关于使用 Tally 表在 SQL 中按日期排序填充缺失的日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！