SQL Server查询 - 使用理货表回填缺少的日期在日期测序的SQL [英] SQL Server query - 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

我使用上述codeS获得和使用选择在一个临时表中插入序列数据，在上述情况下，它插入2015年2月1日，2015年2月2日，02/03 / 2015年，2015年2月4日，和2015年2月5日

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   

然后我用左连接到显示丢失的日期，但该结果仅只有一个租户，这也使得tenantname空。像这样的：

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:

任何建议将是非常美联社preciated。

Any suggestion would be highly appreciated.

推荐答案

您可以使用的 理货表 。

You could do this using a Tally Table.

基本上，你可以使用理货表从 @startDate 生成日期的顺序 @EndDate 和 CROSS JOIN 到单独项目生成所有日期 - 项目组合。那么，结果将是左JOIN 版，以 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 来生成理货表：

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 |

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