Sample Header Ad - 728x90

SQL Query- field calculation based on date

0 votes
1 answer
63 views
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
Asked by Daniel Harris (1 rep)
Oct 17, 2019, 12:39 PM
Last activity: Oct 17, 2019, 05:18 PM