0

I'm trying to create a fact table with data I was given. We have a monthly goal number, but we want to be able to figure out how many weeks are in that month and divide the goal number by the weeks in the month.

My query is very straight forward. Get the goal number for the month, calculate the number of weeks in the month, and divide that by the goal number to get the weekly goal number.

SELECT SUM(Goal) AS Goal
     , (DATEPART(dd, EOMONTH(GETDATE())) / 7) + CASE WHEN (DATEPART(dd, EOMONTH(GETDATE())) % 7) > 0 THEN 1 ELSE 0 END AS NumberOfWeeks
     , SUM(Goal) / (DATEPART(dd, EOMONTH(GETDATE())) / 7) + CASE WHEN (DATEPART(dd, EOMONTH(GETDATE())) % 7) > 0 THEN 1 ELSE 0 END AS WeeklyGoal
  FROM reporting.SalesGoals
 WHERE MonthNumber = MONTH(GETDATE())
   AND Year = YEAR(GETDATE())
   AND Branch IS NOT NULL

This will return this

Goal        NumberOfWeeks       WeeklyGoal
5000        5                   1000

What I'm looking to do is produce rows based on the number weeks. Hopefully to get this

Goal        NumberOfWeeks       WeeklyGoal
5000        1                   1000 
5000        2                   1000
5000        3                   1000 
5000        4                   1000
5000        5                   1000
3
  • 1
    is this a working query ? Seems like you are missing GROUP BY clause – Squirrel May 28 at 3:44
  • Could you please clarify your objectives? What do you mean by "we want to be able to figure out how many weeks are in that month"?...every month of the year always has 4 weeks plus a few days, except for February which has an even 4 weeks (unless it's a leap year). Is your objective to copy the row X number of times, where X = NumberOfWeeks from your first query (and decrement the NumberOfWeeks column value for each copy of the row)? – J.D. May 28 at 3:53
  • Yes this is a working query. J.D., if the month has days that go outside of the normal month (like the 31st ends on a Tuesday) we treat it as an additional "week". We can treat these as 4 weeks instead. My objective is what you stated. X = NumberOfWeeks, so if X = 4, we want to have 4 rows. If X = 5, we want to have 5 rows – jdids May 28 at 13:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.