Basically I have a table with sales projections by month for several territories. Here is a typical example for just one territory all 12 months of a year. The Monthly_Projection is what is actually forecasted, but the boss wants that number broken done into weeks, based on how many weeks are actually in the month (Week_Count) which does kind of change up slightly from year to year.
Year Territory Month Week_Count Monthly_Projection Weekly_ Projection
2015 USA 1 5 299461.00 45892.20
2015 USA 2 4 397579.00 99394.75
2015 USA 3 4 382480.00 95620.00
2015 USA 4 5 491100.00 98220.00
2015 USA 5 4 438172.00 109543.00
2015 USA 6 4 540125.00 135031.25
2015 USA 7 5 744535.00 148907.00
2015 USA 8 4 675421.00 168855.25
2015 USA 9 4 459785.00 114946.25
2015 USA 10 5 556781.00 111356.20
2015 USA 11 4 298651.00 74662.75
2015 USA 12 4 265910.00 66477.50
To be able to report on this easy, I need to create something like this… Basically, if there are 5 weeks in Month 1, I want to take the Monthly_Projection for month 1 and divide it by 5 (this is already done in Weekly_Projection field) and then Insert 5 records into another table while incrementing the Week_Num. I want to do this for all 12 months and all 52 (or 53 weeks) of the year. Below is a sample for the first quarter (13 weeks) and what is should look like to give you a good idea what I am after.
Year Territory Month Week_Num Weekly_ Projection
2015 USA 1 1 45892.20
2015 USA 1 2 45892.20
2015 USA 1 3 45892.20
2015 USA 1 4 45892.20
2015 USA 1 5 45892.20
2015 USA 2 6 99394.75
2015 USA 2 7 99394.75
2015 USA 2 8 99394.75
2015 USA 2 9 99394.75
2015 USA 2 10 95620.00
2015 USA 2 11 95620.00
2015 USA 2 12 95620.00
2015 USA 2 13 95620.00
I am really stuck on how to do this. I have never used a While loop but nothing else seems to make sense to me. Does anyone have any ideas or an example on how to get this done?
Would very much appreciate someone taking the time to explain this to me.
SELECT Year, Territory, Month, Week_Num = ROW_NUMBER() OVER ORDER BY (Month, n.n),
Weekly_ Projection
FROM [sales projections] p
INNER JOIN tally n ON n.n >= 1 AND n.n <= p.Week_Count
Thanks for the quick reply!! Didn't even know of the function ROW_NUMBER() OVER so I will give it a try.
In the sample code you gave I am not sure what n.n represents and I am stuck trying to figure it out. I see it referenced in 3 places. Can you explain where n.n came from?
Thanks again for taking the time to answer. So far I learned 2 things from you already! First, the ROW_NUMBER() OVER function and now the Tally table. I can definitely see you are on to something with the solution provided, but I am still having a little trouble if you don't mind helping a little more.
I am kind of new to SQL, but from what I read, I will need to create this Tally myself, which is just a table called Tally with one field in it called "n" with sequential numbers in it. If this is correct, then how many rows does it need to have for my application? Any number as long as it's greater than Week_Count? If not correct, could you school me a little on this?
Good news! I almost got it working. Awesome Idea you had. Something is off a little though, can you take a look. The Week_Num is not sequential for some reason, but I do get all 52 weeks of the year. Ideas on what is wrong?
Thanks,
Gkaz
SELECT p.Budget_Year, p.Territory, p.FinMo, Week_Num = ROW_NUMBER() OVER (ORDER BY n.n),
p.Weekly_Sales_Budget
FROM [TestTable2] p
INNER JOIN Tally n ON n.n >= 1 AND n.n <= p.Week_Count
order by p.FinMo
2016 USA 1 1 74865.25
2016 USA 1 13 74865.25
2016 USA 1 25 74865.25
2016 USA 1 37 74865.25
2016 USA 2 38 99394.75
2016 USA 2 26 99394.75
2016 USA 2 14 99394.75
2016 USA 2 2 99394.75
2016 USA 3 3 95620.00
2016 USA 3 15 95620.00
2016 USA 3 27 95620.00
2016 USA 3 39 95620.00
2016 USA 4 40 98220.00
2016 USA 4 28 98220.00
2016 USA 4 16 98220.00
2016 USA 4 4 98220.00
2016 USA 4 49 98220.00
2016 USA 5 5 109543.00
You can either create it or if you do not wish to create one but need one for small number range you can build one using recursive cte. for example
; with numbers as
(
select n = 1
union all
select n = n + 1
from numbers
where n <= 9
)
select *
from numbers
the number of rows will be depends on your requirement. For your week no case, the max number of week per year is 52 or 53. If you have other usage, just add on number of rows.
Edit : your no of week is per month so it is max 5 only
The budget is per year, per territory, broken down in monthly buckets. You were right and I missed the FinMo in the "ROW_NUMBER() OVER (ORDER BY" and now it works like it should.
I was going to ask about how to add in that PARTITION Clause, since there are multiple years, territories, and month b buckets I have to deal with.
Thanks for all you help tonight. Your solution was awesome and I would have never thought it would have been this simple. It's getting late here and I think I will try to get it working with the PARTITION on a fully loaded sales projection table tomorrow morning. I'll let you know how it turns out.
