title: Report generation by week between the given dates in SQL Server
body: <p>I need to split a report by work week, and work week is from Monday to Sunday. Let's say I give the <code>start_date</code> as <code>01-12-2012</code> and <code>end_date</code> as <code>29-12-2011</code>. Now, I want to split 1st-4th as week1, 5th-11th as week2, 12th-18th as week3, 19th-25th as week4, 26th-29th as week5.</p>
  
  <p>I have tried the following code, but not getting the accurate. Any modification or suggestion would be grateful. Thanks in advance.</p>
  
  <pre><code>SELECT 
      ta.account, ta.customer, 
  
      isnull(sum(CASE WHEN (datepart(wk, ta.dt)- datepart(wk,dateadd(m, DATEDIFF(M, 0, ta.dt), 0))+ 1) = '1' 
  THEN amount END),0) AS "1",
  
      isnull(sum(CASE WHEN (datepart(wk, ta.dt)- datepart(wk,dateadd(m, DATEDIFF(M, 0, ta.dt), 0))+ 1)= '2' 
      AND (datepart(dw,ta.dt)= 1)
      THEN amount END),0) AS "1",
  
      isnull(sum(CASE WHEN (datepart(wk, ta.dt)- datepart(wk,dateadd(m, DATEDIFF(M, 0, ta.dt), 0))+ 1)= '2' 
      AND (datepart(dw,ta.dt)&lt;&gt; 1)
      THEN amount END),0) AS "2",
  
      isnull(sum(CASE WHEN (datepart(wk, ta.dt)- datepart(wk,dateadd(m, DATEDIFF(M, 0, ta.dt), 0))+ 1)= '3' 
      AND (datepart(dw,ta.dt) = 1)    
      THEN amount END),0) AS "2",
  
      isnull(sum(CASE WHEN (datepart(wk, ta.dt)- datepart(wk,dateadd(m, DATEDIFF(M, 0, ta.dt), 0))+ 1)= '3' 
      AND (datepart(dw,ta.dt)&lt;&gt; 1)    
      THEN amount END),0) AS "3",
  
      isnull(sum(CASE WHEN (datepart(wk, ta.dt)- datepart(wk,dateadd(m, DATEDIFF(M, 0, ta.dt), 0))+ 1) = '4' 
      AND (datepart(dw,ta.dt) = 1)        
      THEN amount END),0) AS "3",
  
      isnull(sum(CASE WHEN (datepart(wk, ta.dt)- datepart(wk,dateadd(m, DATEDIFF(M, 0, ta.dt), 0))+ 1) = '4' 
  AND (datepart(dw,ta.dt) &lt;&gt; 1)   
      THEN amount END),0) AS "4",
  
      isnull(sum(CASE WHEN (datepart(wk, ta.dt)- datepart(wk,dateadd(m, DATEDIFF(M, 0, ta.dt), 0))+ 1) = '5' 
      AND (datepart(dw,ta.dt) = 1)    
      THEN amount END),0) AS "4",
  
      isnull(sum(CASE WHEN (datepart(wk, ta.dt)- datepart(wk,dateadd(m, DATEDIFF(M, 0, ta.dt), 0))+ 1) = '5' 
      AND (datepart(dw,ta.dt) &lt;&gt; 1)   
      THEN amount END),0) AS "5"
  
  FROM 
      (SELECT * FROM tablename
       WHERE dt &gt;= '12/01/2011 00:00:00' AND dt &lt; '12/26/2011 00:00:00'
      ) ta
  GROUP BY 
      ta.account, ta.customer
  </code></pre>
  
  <p>The desired o/p is:</p>
  
  <p>Account       Customer    Week1     Week2     Week3    Week4    Week5</p>
  
  <p>For the above given dates,
  week1 should be 1st-4th
  week2 should be 5th-11th
  week2 should be 12th-18th
  week2 should be 19th-25th
  week2 should be 26th-01st(of Jan 2012)</p>
  
  <p>As I do the report on every Monday, I want the total amount from Monday to Sunday as one week.</p>
  
  <p>There may be a case that, the customer may not have any usage. So, in that case I want it to be as 0.00 as week5 in the above example.</p>
  
  <p>Hope you understand my question. Thanks</p>