Home Weekly Group by not including Sunday
Reply: 2

Weekly Group by not including Sunday

Ankesh Kumar
1#
Ankesh Kumar Published in 2017-11-14 12:54:52Z

I am grouping the data by week in the following queries

 select id,sum(quantity) ,dateadd(wk, datediff(wk,0, [createddate]), 0) from table1 where    
 createddate >= '2017-10-02' and createddate <= '2017-11-12'  
 and [id] = 111      
 group by [id] , dateadd(wk, datediff(wk,0, [createddate]), 0) 

Its grouping the data upto Sat i.e. Mon - Sat, not Mon to Sun. Can someone explain what I am missing.

I cannot change the database start day

Sample data is :

  • 02/10/2017 9.00
  • 03/10/2017 10.50
  • 04/10/2017 10.00
  • 08/10/2017 1.00
Palle Due
2#
Palle Due Reply to 2017-11-14 13:23:17Z

You could group by week number instead:

 group by [id] , datepart(ISO_WEEK, [createddate])

ISO weeks start on Monday.

Edward N
3#
Edward N Reply to 2017-11-14 13:26:15Z

The first day of the week is based on your language settings (default value is us_english is 7 (Sunday)). And SET DATEFIRST has no effect on DATEDIFF

You can try this query instead

set DATEFIRST 1

select id,sum(quantity) ,dateadd(wk, datediff(wk,0, [createddate]), 0) from table1 where    
createddate >= '2017-10-02' and createddate <= '2017-11-12'  
and [id] = 111
group by [id] , dateadd(dd,(-1)*(datepart(dw,[createddate])-1),[createddate])

set DATEFIRST 7 

Updated:

dateadd(dd,(-1)*(datepart(dw,[createddate])-1),[createddate])

It will return the Monday of the week as your original query.

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.30295 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO