Phil Phil - 4 months ago 11
SQL Question

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?

Answer

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
Comments