CODEWITHSUNDEEP
sqlsql-server
Phil
Phil

Reputation: 4069

SQL Query - select 0 if no value available

I have the following stored procedure created in SQL Server 2012 ...

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

It will display an output similar to the following...

enter image description here

What I would like it to do is display the previous weeks worth of ticket counts (today and previous 6 days) even if some of those days have no tickets entered. It would just show 0 for them. So my output would look instead contain Thursday, Friday, Saturday, Sunday, Monday, Tuesday, Wednesday.

How can this be achieved?

Upvotes: 2

Views: 135

Answers (2)

Siyual
Siyual

Reputation: 16917

You need to build a date table for the previous days and do a RIGHT JOIN to it:

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

Upvotes: 4

Fuzzy
Fuzzy

Reputation: 3810

This should do it:

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

        -- PULL THE DATA
        WITH A
            AS (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)),
            B
            AS (
            SELECT 'Monday' AS DAY_ENTERED
            UNION
            SELECT 'Tuesday' AS DAY_ENTERED
            UNION
            SELECT 'Wednesday' AS DAY_ENTERED
            UNION
            SELECT 'Thursday' AS DAY_ENTERED
            UNION
            SELECT 'Friday' AS DAY_ENTERED
            UNION
            SELECT 'Saturday' AS DAY_ENTERED
            UNION
            SELECT 'Sunday' AS DAY_ENTERED)
            SELECT ISNULL(A.TICKET_COUNT,0) AS TICKET_COUNT
                , A.DATE_ENTERED
                , B.DAY_ENTERED
            FROM   A
                 RIGHT OUTER JOIN B ON A.DAY_ENTERED = B.DAY_ENTERED
            ORDER BY A.DATE_ENTERED ASC;
        SET NOCOUNT OFF;
    END;

Upvotes: 1

Related Questions