0

I'm writing a query for sales targets per week, however I'm getting a little stuck on this. Targets are per month, per year, so for example, Brand A has a monthly target (changes each month)

The snippet from the report is as follows:

SELECT BrandName
      ,SUM((TargetMonThur *4) +TargetFriday)
WHERE [MONTH] IN (
        DATEPART(MM,@DAY1),
        DATEPART(MM,@DAY2),
        DATEPART(MM,@DAY3),
        DATEPART(MM,@DAY4),
        DATEPART(MM,@DAY5)
    ) 
    AND [YEAR] IN (
        DATEPART(YY,@DAY1),
        DATEPART(YY,@DAY2),
        DATEPART(YY,@DAY3),
        DATEPART(YY,@DAY4),
        DATEPART(YY,@DAY5)
    ) 

However the issue appears when the start of the week/ end of the week is a different month to the other days. at this point, it calculates both month's targets * 4.

I think I'm being a complete noob on this, but any help would be great.

Thanks in advance

  • Good first question 😄! In general providing an minimal reproducible example with sample DDL (perhaps on dbfiddle) is the gold standard for questions. Minifying the problem may even help you answer your own question (in which case you're encourage to post back what the solution you found was). In the meantime, do you have access to/have you tried using a static date table? – Peter Vandivier Oct 17 at 12:59
0

One solution to this problem would be to create a static calendar table. In the scope of your problem, this table would be important because there are holidays that must be considered when calculating the week's goals, as it is not fair to compare the targets an entire week with a partial week (holidays).

The downside of these static tables is proper management and organization of the calendar for the New Year's and holidays change. It is very important to put reminders in this routine to minimize the chance of a possible future problem.

Consider this risk for the proposed solution.

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.