title: I want to get count of communication logged in weekly then its previous week and so on in sql
body: <p>I have tried this -</p>
  
  <pre><code>declare @maxD datetime 
  select @maxD= max(loggingdate) FROM CommunicationLogs 
  
  SELECT Count(loggingdate),@maxD FROM CommunicationLogs 
  WHERE loggingdate  BETWEEN (@maxD - 6) AND @maxD and clientid ='20154'
  
  4   2017-08-21 11:18:24.930
  </code></pre>
  
  <p>The above output means communication logged before 2017-08-21 11:18:24.930 and before its 7 days are 4. But the problem is its specific to max date. I want to get same result for all the logged date.</p>
  
  <p>I tried but giving wrong data-</p>
  
  <pre><code>SELECT COUNT(id),loggingdate FROM CommunicationLogs 
  WHERE loggingdate  BETWEEN (loggingdate - 6) AND loggingdate and clientid ='20154'
  group by loggingdate
  ORDER BY loggingdate desc
  </code></pre>
  
  <p>The sql data is -</p>
  
  <pre><code>31935   2017-08-21 11:18:24.930
  31936   2017-08-21 00:00:00.000
  31933   2017-08-18 14:53:03.707
  31934   2017-08-18 00:00:00.000
  31924   2017-08-10 12:06:00.397
  31918   2017-07-26 00:00:00.000
  31919   2017-07-26 00:00:00.000
  31920   2017-07-26 00:00:00.000
  31921   2017-07-26 00:00:00.000
  31922   2017-07-26 00:00:00.000
  31923   2017-07-26 00:00:00.000
  31898   2017-07-25 00:00:00.000
  31899   2017-07-25 00:00:00.000
  31900   2017-07-25 00:00:00.000
  31901   2017-07-25 00:00:00.000
  31902   2017-07-25 00:00:00.000
  31903   2017-07-25 00:00:00.000
  31904   2017-07-25 00:00:00.000
  31905   2017-07-25 00:00:00.000
  31906   2017-07-25 00:00:00.000
  31907   2017-07-25 00:00:00.000
  31908   2017-07-25 00:00:00.000
  31909   2017-07-25 00:00:00.000
  31910   2017-07-25 00:00:00.000
  31911   2017-07-25 00:00:00.000
  31912   2017-07-25 00:00:00.000
  31913   2017-07-25 00:00:00.000
  31914   2017-07-25 00:00:00.000
  31915   2017-07-25 00:00:00.000
  31916   2017-07-25 00:00:00.000
  31917   2017-07-25 00:00:00.000
  31889   2017-07-24 00:00:00.000
  31890   2017-07-24 00:00:00.000
  </code></pre>