SQL查询-如果没有可用的值，则选择0

Question

我在SQL Server2012中创建了以下存储过程...

ALTER PROCEDURE [dbo].[GET_DASHBOARD_LINECHART_DATA]    
AS
BEGIN
    SET NOCOUNT ON;

    -- PULL THE DATA
    SELECT COUNT(*) AS TICKET_COUNT, 
           CAST(DATE_ENTERED AS DATE) AS DATE_ENTERED,
           DATENAME(weekday,DATE_ENTERED) AS DAY_ENTERED
    FROM TICKETS
    WHERE DATE_ENTERED >= DATEADD(day,-7,GETDATE())
    GROUP BY DATENAME(weekday,DATE_ENTERED), CAST(DATE_ENTERED AS DATE)
    ORDER BY DATE_ENTERED ASC

    SET NOCOUNT OFF;
END

它将显示类似于下面的输出...

我想让它做的是显示前几周的票数（今天和前6天），即使其中一些天没有门票进入。对他们来说只会显示0。因此，我的输出将看起来包含周四、周五、周六、周日、周一、周二、周三。

Answer 1

您需要为前几天构建一个日期表，并对其执行右联接:

Alter Procedure [dbo].[GET_DASHBOARD_LINECHART_DATA]
As Begin
    Set NoCount On

    Declare @FromDate   Date = DateAdd(Day, -7, GetDate()),
            @ToDate     Date = GetDate()

    ;With Date (Date) As
    (
        Select  @FromDate Union All
        Select  DateAdd(Day, 1, Date)
        From    Date
        Where   Date < @ToDate
    )
    Select      Count(T.Date_Entered)       As  TICKET_COUNT, 
                D.Date                      As  DATE_ENTERED,
                DateName(WeekDay, D.Date)   As  DAY_ENTERED
    From        Tickets T
    Right Join  Date    D   On  D.Date = Convert(Date, T.DATE_ENTERED)
    Group By    DateName(WeekDay, D.Date), D.Date
    Order By    D.Date Asc
End