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
Last activity: Oct 17, 2019, 05:18 PM