CODEWITHSUNDEEP
sql-server
Yohanes Pradono
Yohanes Pradono

Reputation: 293

get total orders for the last 1 month every week

I have an "Orders" table containing column CreatedDate (datetime) on when the order took place.

Today is Feb 13, 2014.

How to get total records for the last 4 weeks for every week.

it should return something like this:

PerDate     Total
2014-01-26  13    <--- sunday
2014-02-02  24    <--- sunday
2014-02-09  33    <--- sunday
2014-02-13  35    <--- this is today

13 from the first record is the total record from 2014-01-20 00:00:00 AM (monday) to 2014-01-26 12:00:00 PM (sunday)
24 from the 2nd record is the total record from 2014-01-27 00:00:00 AM (monday) to 2014-02-02 12:00:00 PM  (sunday)
33 from the 3rd record is the total record from 2014-02-03 00:00:00 AM (monday) to 2014-02-09 12:00:00 PM  (sunday)
35 from the 4th record is the total record from 2014-02-10 00:00:00 AM (monday) to 2014-02-13 (today)

so they are in Ascending order.

Upvotes: 1

Views: 328

Answers (1)

valex
valex

Reputation: 24144

You should use DATEPART(dw,CreatedDate) function to calculate date of begin and end of the week for each CreatedDate and then just group by this field:

WITH T AS
(
  SELECT 
       cast(floor(cast(CreatedDate as float)) as datetime)
           -DATEPART(dw,CreatedDate)+1 as BeginOfweek,        
       cast(floor(cast(CreatedDate as float)) as datetime)
           -DATEPART(dw,CreatedDate)+8 as EndOfWeek
  FROM ORDERS
  WHERE cast(floor(cast(CreatedDate as float)) as datetime) BETWEEN 
      DATEADD(WEEK,-4,GETDATE())
      AND 
      GETDATE() 

) 

SELECT 
 BeginOfWeek,
 MIN(CASE WHEN GETDATE()<EndOfWeek 
               THEN GETDATE() 
               ELSE EndOfWeek
      END) as EndOfWeek,
 Count(*) as OrdersCount
FROM T
Group by BeginOfWeek

SQLFiddle demo

Upvotes: 1

Related Questions