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

问题描述

我在数据库中有一个有租户数量的表，每个租户列出了他们每个日期的销售记录。有一个实例，其中租户在特定日期没有销售，因此没有销售的日期在表中没有记录打破一个适当的日期序列。请参阅下面的示例表格：

code>选择租户，日期，销售
从tblSales
其中日期在'01 / 01/2015'和'01 / 05/2014'之间

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

1. 作为我的初始解决方案，




创建一个临时表，插入一个基于所选日期范围的日期序列，并使用该日期范围与实际表格连接。 以下是我开始的：

  @dateFrom datetime = '02 / 01/2015'，
 @dateTo date = '02 / 05/2015'
 
 
声明@MaxNumDays int 
声明@Counter int 
 
设置@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 
  

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

 选择tenantcode，日期，销售
到＃DSRT2 
从DAILYMOD 
其中（日期BETWEEN @dateFrom和@dateTo）
 
 
 SELECT * 
 from #dsrtdate a left join＃DSRT2 b 
 on a.date = b.date 
 order by b.tenantcode，a.date 
  

然后我使用左连接来显示缺失的日期，但是这只导致一个租户，它也使tenantname为null。像这样：





<

解决方案

您可以使用 Tally表 。





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





SQL Fiddle

  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 E2 a，E2 b）
，Tally（N）AS（
 SELECT TOP（DATEDIFF（DAY，@startDate，@endDate）+1）
 ROW_NUMBER（）OVER 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表。：

  DECLARE 
 @startDate DATE ='20140101'，
 @endDate DATE ='20140105'; 
 
 with Tally（N）AS（
 SELECT TOP（DATEDIFF（DAY，@startDate，@endDate）+1）
 ROW_NUMBER（）OVER ）
 FROM sys.columns a，sys.columns b 
）
，CteAllDates（Item，dt）AS（
 SELECT x.Item，DATEADD（DAY，N  - @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 
  
 
 
 
 
 
 
 结果 
  |项目| dt |销售| 
 | --------- | ------------ | -------- | 
 | 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 | 
  
 
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:


as my initial solution, I thought of creating a temp table inserting a sequence of date based on the date range selected and use that to left join with the actual table.
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 suggestion  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-JOINed to tblSales to achieve the desired output.

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




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 |


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