SQL Query- field calculation based on date 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
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.