SQL Server - 显示缺少日期的行 [英] SQL Server - show rows for missing dates

问题描述

这是我在 sql server 2008 中的 SP:

SELECTCONVERT(NVARCHAR(30), YMD, 101) AS [日期],SUM(ISNULL(Jobs, 0)) AS [工作]，SUM(ISNULL(SELL_VALUE, 0)) AS [成本]发件人[YMD] @DateFrom 和 @DateTo 之间的位置YMD分组由 YMD DESC 订购

这里我的@DateFrom 和 @DateTo 是 7/23/2014 到 7/29/2014.

结果是:

<前>DATE 工作成本7/29/2014 1 0.00 美元7/28/2014 4 0.00 美元7/27/2014 3 0.06 美元7/25/2014 4 0.00 美元7/23/2014 1 0.00 美元

现在我想要的是:

<前>DATE 工作成本7/29/2014 1 0.00 美元7/28/2014 4 0.00 美元7/27/2014 3 0.05 美元7/26/2014 0 0.00 美元7/25/2014 4 0.00 美元7/24/2014 0 0.00 美元7/23/2014 1 0.00 美元

即它应该向我显示一行缺失的日期，除了日期一之外的所有列都输入了0".

解决方案

我同意 @bluefeet 方法

为了创建一个动态的 from-to 日历，您可以使用这样的递归 cte:

DECLARE @DateFrom AS DATE声明 @DateTo 为 DATESET @DateFrom='7/23/2014'SET @DateTo ='7/29/2014';与 r AS(SELECT @DateFrom AS calendardate联合所有SELECT DATEADD(d,1,calendardate) FROM rWHERE DATEADD(d,1,calendarddate)<=@DateTo)选择 * 从 r

This is my SP in sql server 2008:

SELECT      
    CONVERT(NVARCHAR(30), YMD, 101) AS [DATE],               
    SUM(ISNULL(Jobs, 0)) AS [Jobs],     
    SUM(ISNULL(SELL_VALUE, 0)) AS [COST]
    FROM    S       
    WHERE [YMD] BETWEEN @DateFrom AND @DateTo 
GROUP BY YMD
ORDER BY YMD DESC

Here my @DateFrom AND @DateTo is 7/23/2014 to 7/29/2014.

The result comes to:

DATE      Jobs  Cost
7/29/2014   1   $0.00 
7/28/2014   4   $0.00 
7/27/2014   3   $0.06 
7/25/2014   4   $0.00 
7/23/2014   1   $0.00 

Now What I would like to have is below:

       
DATE      Jobs  Cost
7/29/2014   1   $0.00 
7/28/2014   4   $0.00 
7/27/2014   3   $0.05 
7/26/2014   0   $0.00 
7/25/2014   4   $0.00 
7/24/2014   0   $0.00 
7/23/2014   1   $0.00 

i.e. it should shows me a row for missing dates with "0" entered for all the columns except the date one.

解决方案

I agree with @bluefeet approach

in order to create a dynamic from-to calendar you can use a recursive cte like this one:

DECLARE @DateFrom AS DATE
DECLARE @DateTo as DATE 
SET @DateFrom='7/23/2014'
SET @DateTo ='7/29/2014'
;WITH r AS
(SELECT @DateFrom AS calendardate
UNION ALL
SELECT DATEADD(d,1,calendardate) FROM r 
WHERE DATEADD(d,1,calendardate)<=@DateTo
) 
SELECT * FROM r

这篇关于SQL Server - 显示缺少日期的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！