SQL如何获得0值 [英] SQL How to get 0 values

问题描述

我正在使用此 SQL 生成以下图表 (RS2008R2):

I'm generating the graph below (RS2008R2) using this SQL:

SELECT  Merchant.Name as MerchantName, 
    CONVERT(char(8), 
    VoucherRedemption.RedeemedAtUTC, 112) as ShortDateRedeemed,
    COUNT(CONVERT(char(8), VoucherRedemption.RedeemedAtUTC, 112)) as NumberRemeemedOnDay
FROM    Merchant 
INNER JOIN  Terminal ON Merchant.MerchantUID = Terminal.MerchantUID 
INNER JOIN  VoucherRedemption ON Terminal.TerminalUID = VoucherRedemption.TerminalUID
WHERE       Merchant.Name = 'Merchant 1'
GROUP BY    merchant.Name, 
            CONVERT(char(8), VoucherRedemption.RedeemedAtUTC, 112)

问题:如何在图表的底部轴上获得稳定的日期时间线?即我想在此图中显示大量 0 列数据，并显示所有日期.

Question: How do I get a steady date timeline on the bottom axis of the graph? ie I want to show a lot of 0 column data in this graph with all dates showing.

虽然 Reporting Services 2008R2 可以很好地处理这个问题，但还没有找到一个好的例子.

A though is that Reporting Services 2008R2 may handle this well, but haven't found a good example yet.

推荐答案

首先，您的查询可以写得更简单

To begin with, your query can be written more simply

SELECT  Merchant.Name as MerchantName, 
    CONVERT(char(8), VoucherRedemption.RedeemedAtUTC, 112) as ShortDateRedeemed,
    COUNT(VoucherRedemption.RedeemedAtUTC) as NumberRemeemedOnDay
FROM    Merchant 
INNER JOIN  Terminal ON Merchant.MerchantUID = Terminal.MerchantUID 
INNER JOIN  VoucherRedemption ON Terminal.TerminalUID = VoucherRedemption.TerminalUID
WHERE       Merchant.Name = 'Merchant 1'
GROUP BY    merchant.Name,  VoucherRedemption.RedeemedAtUTC

最终的解决方案是

;with tmp as (
    SELECT  Merchant.Name as MerchantName, 
        VoucherRedemption.RedeemedAtUTC as ShortDateRedeemed,
        COUNT(VoucherRedemption.RedeemedAtUTC) as NumberRemeemedOnDay
    FROM    Merchant 
    INNER JOIN  Terminal ON Merchant.MerchantUID = Terminal.MerchantUID 
    INNER JOIN  VoucherRedemption ON Terminal.TerminalUID = VoucherRedemption.TerminalUID
    WHERE       Merchant.Name = 'Merchant 1'
    GROUP BY    merchant.Name,  VoucherRedemption.RedeemedAtUTC
), dates as (
    select min(ShortDateRedeemed) theDate, max(ShortDateRedeemed) endDate
    from tmp
    union all
    select theDate + 1, endDate
    from dates
    where theDate < endDate
)
SELECT  A.MerchantName, 
        CONVERT(char(8), theDate, 112) as ShortDateRedeemed,
        isnull(A.NumberRemeemedOnDay,0)
FROM dates B
left join tmp A on B.theDate = A.ShortDateRedeemed

这篇关于SQL如何获得0值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！