| Author |
Topic |
|
scubagirl
Starting Member
3 Posts |
 Posted - 2011-10-26 : 18:41:19
|
| When pulling data, I need to summarize totals by Week of the current month.e.g. if the 1st of the current month falls on a Wednesday, Week 1 would be Wednesday through Sunday that week. Week 2 Would be the next Monday through Sunday - and so forth - so it's not as straight forward as 7 days counts as a week.Any ideas how to do this in SQL? |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2011-10-26 : 19:25:31
|
[code]week_no = ((day(date_col) - 1) / 7) + 1[/code]
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
scubagirl
Starting Member
3 Posts |
Posted - 2011-10-26 : 19:39:48
|
quote:
Originally posted by khtan
week_no = ((day(date_col) - 1) / 7) + 1
KH[spoiler]Time is always against us[/spoiler]
No go - with that logic, 9/28/11 is showing week 4 when it should be week 5.As an example, for this month, Week 1 would only be 10/1 -10/2.Week 2 would be Monday 10/3 through Sunday 10/9, Week 3 would be 10/10-10/17....etc with this month ending Week 6 as 10/31 only. |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2011-10-26 : 21:46:04
|
Sorry, misunderstood your requirement.week_no = datepart(iso_week, dte_col) - datepart(iso_week, dateadd(month, datediff(month, 0, date_col), 0)) + 1
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
waterduck
Aged Yak Warrior
982 Posts |
Posted - 2011-10-26 : 22:55:20
|
| Set datefirst 1Select datepart(wk, date_col ) from yours_table |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2011-10-26 : 23:04:38
|
wd,Should be datefirst 1. The week is from Monday to Sunday. Also OP wanted the week no starts from 1 for each month.
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
waterduck
Aged Yak Warrior
982 Posts |
Posted - 2011-10-26 : 23:11:54
|
| Hahaaa....sorry sorry...i still blur mode... |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2011-10-26 : 23:13:13
|
quote:
Originally posted by waterduck
Hahaaa....sorry sorry...i still blur mode...
You need coffee 
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
waterduck
Aged Yak Warrior
982 Posts |
Posted - 2011-10-26 : 23:22:14
|
| Mummy say coffee not good for body.....beer instead =P |
|
|
|
scubagirl
Starting Member
3 Posts |
Posted - 2011-10-27 : 01:04:20
|
| found a solution - got the ISO Week code for the date in my table, also the ISO Week code for the first of the current month, did the subtraction of the two and added +1. Thanks for trying to help. |
|
|
|
|
Need help getting weeks of current month