Group by week of a particular month in sql server

Viewed 2537

I have a table with few columns [ShipAmt], [Cdate], ....

I want to sum the columns by week of a particular month and display it.

The week considered to be starting from 1 to end of the month.

And if there are no ProductOrders in a week, I want to show their sum as 0.00

Example

Week 1 ---> 2016/03/01 - 2016/03/07

Week 2 ---> 2016/03/08 - 2016/03/14

Week 3 ---> 2016/03/15 - 2016/03/21

Week 4 ---> 2016/03/22 - 2016/03/28

Week 5 ---> 2016/03/29 - 2016/03/31

Table

create table ProductOrders
( 
   [ID] [int] IDENTITY(1,1) NOT NULL,
   [ShipAmt] float not null,
   [Total] float not null,
   [Cdate] datetime not null
)

Values

insert into ProductOrders ([ShipAmt], [Total], [Cdate]) 
values (26.99 , 40.00, '2016-03-04 20:26:54.000');
insert into ProductOrders ([ShipAmt], [Total], [Cdate]) 
values (45.00 , 70.88, '2016-03-04 20:28:41.000');
insert into ProductOrders ([ShipAmt], [Total], [Cdate]) 
values (75.00 ,125.00, '2016-03-05 05:39:03.000');
insert into ProductOrders ([ShipAmt], [Total], [Cdate]) 
values (49.00 , 71.99, '2016-02-26 11:21:02.000');

Now I want to sum all the columns of 3rd month and I tried this.

Select 
Sum([ShipAmt]) [ShipAmt],
Sum([Total]) [Total]
from
(
    SELECT 
    [ShipAmt],
    [Total],
    [Cdate],
    DATEDIFF(week, '2016-03-01', '2016-03-31') AS WeekNumber
    FROM ProductOrders
)
as t1
GROUP BY
WeekNumber

The output is

ShipAmt Total
195.99  307.87

Desired output

ShipAmt Total
146.99  235.88
0.00     0.00
0.00     0.00
0.00     0.00
3 Answers
Related