title: How to weekly aggregate data in sql and include skipped weeks too
body: <p>I am working in SQL Server 2008. I am writing a stored procedure which aggregates data on a weekly basis.</p>
  
  <p>Code for aggregation is </p>
  
  <pre><code>ALTER PROCEDURE [dbo].[ups_URLBatchStats] 
     (@startDate datetime, @endDate datetime,@source varchar(8))
  AS BEGIN
      SELECT 
         DATEADD(wk, DATEDIFF(wk, 0, ScheduleDate), 6) AS [Week Commencing], 
         SUM(Unresolved) AS Unresolved,
         SUM(Resolved) AS Resolved,
         SUM(TurkSpend) AS TurkSpend
      FROM 
         dbo.V_URLBatchStats 
      WHERE
         ScheduleDate BETWEEN @startDate AND @endDate 
         AND Source = ISNULL(@source, Source)
      GROUP BY
         DATEADD(wk, DATEDIFF(wk, 0, ScheduleDate), 6)
      ORDER BY 
         DATEADD(wk, DATEDIFF(wk, 0, ScheduleDate), 6)
  END
  </code></pre>
  
  <p>Show it gives me correct result, but if there are no data for any week I have to show it with values of '0' in the columns.</p>
  
  <p>So how can I handled these skipped weekly rows?</p>
  
  <p>Thanks in advance.  </p>