Creating weekly buckets on date column in sql
How to create weekly buckets for date column.
My data looks like this:
ID LOC DATE Amount
1 AAA 21-07-2015 3000
2 AAA 22-07-2015 1000
3 AAA 23-07-2015 0
4 AAA 27-07-2015 300
5 AAA 29-07-2015 700
I also have a fiscal year calendar calendar containing week start and end week ranges and which week each bucket falls on. It looks like
Year WeekStart WeekEnd Week
2015 20-07-2015 26-07-2015 1
2015 27-07-2015 02-08-2015 2
so on till 2020...
The challenge here is that I have to group all the items in table A under each bucket and find the value for the week's sum.
Output:
ID LOC WEEk Amount
1 AAA 1 4000
2 AAA 2 1000
Not sure how to start the process itself or how to link these both files. Finally your help is needed.
+3
source to share
1 answer
Correlated subqueries are needed here https://technet.microsoft.com/en-us/library/ms187638(v=sql.105).aspx . Suppose the data is in tabular data, calendar in calendar calendar. Then your request will look like
select
loc, week, sum(amount)
from
(select
(select top 1 week from calendar t1 where t1.WeekStart <= t2.date and t2.date <= t1.WeekEnd) as week,
loc,
amount
from
data t2) as subsel1
group by
loc, week
+2
source to share