title: How to get last 5 weeks of data from user input date
body: <p>I am not able to get count in weekwise i.e 7 days shipped orders to be one week, please check my query as follows:</p>
  
  <pre><code>Declare @CurrentDate nvarchar(50);
  SET @CurrentDate='2012-07-25'
  
  BEGIN 
  
  ;WITH DateList AS
  (
      SELECT  DATEADD(WEEK, 0, CONVERT(DATETIME, @CurrentDate)) AS CreateDateTime, 1 AS Cnter
      UNION ALL
      SELECT  DATEADD(WEEK, -1, CreateDateTime), DateList.Cnter + 1
      FROM DateList
      WHERE DateList.Cnter  &lt; 5
  )
  SELECT  DateList.CreateDateTime AS ShipWeek, COALESCE(Temp.TotalCount, 0) AS TotalCount
  FROM DateList
  LEFT JOIN  
  (
      SELECT  COUNT(Id) TotalCount
          ,DATEADD(WEEK, DATEDIFF(WEEK, '19000101',CreatedDateTime),'19000101') AS ShipWeek
      FROM ShipmentDetail 
      WHERE CreatedDateTime
      BETWEEN DATEADD(DAY,-30,@CurrentDate) AND @CurrentDate
      GROUP BY  DATEADD(WEEK, DATEDIFF(WEEK, '19000101',CreatedDateTime),'19000101') 
  ) Temp
      ON CONVERT(VARCHAR(10), DateList.CreateDateTime, 112) = Temp.ShipWeek
  END
  </code></pre>
  
  <p>Result Table is as follows:</p>
  
  <pre><code>ShipWeek                TotalCount
  2012-07-25 00:00:00.000 0
  2012-07-18 00:00:00.000 0
  2012-07-11 00:00:00.000 0
  2012-07-04 00:00:00.000 0
  2012-06-27 00:00:00.000 0
  </code></pre>
  
  <p>please give me the exact solution.</p>
  
  <p>If I enter  current date as like:</p>
  
  <pre><code>SET @CurrentDate='2012-07-30'
  </code></pre>
  
  <p>then result table will give data like:</p>
  
  <pre><code>ShipWeek                TotalCount
  2012-07-30 00:00:00.000 0
  2012-07-23 00:00:00.000 1406
  2012-07-16 00:00:00.000 0
  2012-07-09 00:00:00.000 0
  2012-07-02 00:00:00.000 0
  </code></pre>
  
  <p>why I am not getting if I enter current date like <code>'2012-07-27'</code></p>