vendredi 7 août 2015

How to modify the following code to ignore rows with 0 and shows the output weekly

My following query gives me the output shown in figure 1. I want to change it so that it gives the output in figure 2. I want to show the weekwise (even if the value is 0) Here is the query. Please help.

SELECT @MaxDate = @MaxDate
    ,@MinDate = dateadd(week, (@LastXWeeks + 1), @MaxDate);

WITH AllDates
AS (
    SELECT @MinDate AS xDate

    UNION ALL

    SELECT Dateadd(day, 1, xDate)
    FROM AllDates AS ad
    WHERE ad.xDate < @MaxDate
    )
SELECT 'playing' AS activity
    ,ad.xDate
    ,Isnull(t.TimePerDay, 0) AS TimePerDay
FROM AllDates AS ad WITH (NOLOCK)
LEFT JOIN @test AS t ON ad.xDate = t.DATE

Figure 1

Figure 2

Aucun commentaire:

Enregistrer un commentaire