sql-server-2012buckets

creating weekly buckets in date column in sql


How can i create weekly buckets for a date column.

My data looks like :

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 Financial Year Calendar file containing the week start and end 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 task here is I have to group all the line items in A table fall under each bucket and find the amount value per week.

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.Kindly need your help.


Solution

  • You need here Correlated Subqueries https://technet.microsoft.com/en-us/library/ms187638(v=sql.105).aspx. Let's assume data is in table data, calendar in table calendar. Then your query 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