CODEWITHSUNDEEP
sqlsql-server
Dheeraj Patnaik
Dheeraj Patnaik

Reputation: 375

Group by week of a particular month in sql server

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

Upvotes: 3

Views: 3334

Answers (3)

DhruvJoshi
DhruvJoshi

Reputation: 17126

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:

enter image description here

Upvotes: 1

Jaydip Jadhav
Jaydip Jadhav

Reputation: 12309

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])

Upvotes: 1

Abdul Rasheed
Abdul Rasheed

Reputation: 6709

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

Upvotes: 0

Related Questions