Group by week of a particular month in sql server

Question

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

`DATEDIFF(week, '2016-03-01', '2016-03-31')` always returns 4 Try this for finding the week in month http://stackoverflow.com/questions/13116222/how-to-get-week-number-of-the-month-from-the-date-in-sql-server-2008 — Liesel, Mar 11 '16 at 11:54
WeekNumber is always going to be the same number because you're using two literals in DATEDIFF. Have a look at DATEPART(wk, CDate) instead. — cf_en, Mar 11 '16 at 11:55

DhruvJoshi · Answer 1 · 2016-03-11T12:56:33.813

1

Your query should be like

   Select 
    Sum(COALESCE([ShipAmt],0.00)) [ShipAmt],
    Sum(COALESCE([Total],0.00)) [Total]
    from
    (
        SELECT 
        [ShipAmt],
        [Total],
        [Cdate],
        CAST((DAY([Cdate])-1)/7   AS INT) +1 AS WeekNumber
        FROM ProductOrders
        WHERE MONTH([Cdate])=3
        -- This checks for month
        UNION 
        SELECT NULL as ShipAmt,NULL as Total,NULL as Date,1 as WeekNumber
        UNION 
        SELECT NULL,NULL,NULL,2
        UNION 
        SELECT NULL,NULL,NULL,3
        UNION 
        SELECT NULL,NULL,NULL,4
        UNION 
        SELECT NULL,NULL,NULL,5
        )
    as t1
    GROUP BY
    WeekNumber

Output:

edited Mar 11 '16 at 12:56

answered Mar 11 '16 at 11:57

DhruvJoshi

17,041
6
41
60

I want the results only of a particular month, in my example it is 3rd month and the remaining 4 weeks output to be 0.0 @DhruvJoshi – Dheeraj Patnaik Mar 11 '16 at 12:03
There is a slight problem, it is showing 6 rows where I have to have 5 rows for 3rd month – Dheeraj Patnaik Mar 11 '16 at 12:26
@DheerajPatnaik Please check again. I made an update. This should work now – DhruvJoshi Mar 11 '16 at 12:56
@DheerajPatnaik Please accept as answer if this works for you and/or up-vote so that future seekers of similar problem find the question useful. – DhruvJoshi Mar 11 '16 at 12:59

score 1 · Answer 2 · answered Mar 11 '16 at 12:38

Try this

DECLARE @d1 DATETIME, @d2 DATETIME
SELECT @d1 = '2016-03-01' ,@d2= '2016-03-31'
;WITH dates ( [date] )
AS
(
SELECT @d1
UNION all
SELECT dateadd(d,1,date)
FROM dates
where date < @d2
)
select  DISTINCT DATEPART(week,[date]) AS WeekNumber
       , ISNULL(M.TotalShipAmt,0)
       ,ISNULL(M.Total,0) 
from dates 
LEFT OUTER JOIN(
SELECT 
SUM([ShipAmt]) AS TotalShipAmt,
SUM([Total])AS Total,
DATEPART(week,[Cdate]) AS WEEKNUMBER
FROM ProductOrders ProductOrders
GROUP BY DATEPART(week,[Cdate])
)AS M  ON M.WEEKNUMBER=DATEPART(week,[date])

score 0 · Answer 3 · answered Mar 11 '16 at 12:25

Plz try something like this

DECLARE @V_DATE DATE = '01-01-2016'
        ,@V_MDATE DATE

SET @V_MDATE = DATEADD(DAY,-1,(DATEADD(MONTH,1,@V_DATE)))

    ;WITH CTE_PRD
AS
(   SELECT  1 AS WNO,@V_DATE AS SDATE,DATEADD(DAY,6,@V_DATE) AS EDATE
    UNION ALL SELECT WNO+1,DATEADD(DAY,1,EDATE),DATEADD(DAY,7,EDATE) FROM CTE_PRD WHERE EDATE <= @V_MDATE
)

SELECT WNO ,ISNULL(SUM([ShipAmt]),0) [ShipAmt],ISNULL(SUM([Total]),0) [Total]
FROM CTE_PRD
INNER JOIN ProductOrders ON  [Cdate] BETWEEN SDATE AND (CASE WHEN EDATE < @V_MDATE THEN EDATE ELSE @V_MDATE END)
GROUP BY WNO

Group by week of a particular month in sql server

3 Answers3