Target Value that increases throughout the year 1

[Brianfree]

Hello, i am looking to create an sql query or an sql view that calculates a Target Value that increases throughout the year depending on what month you are in.

For example:-

The year start date is 2018-07-01 - there has currently been 8 days elapsed.

In July i want each day to have a value of 220
In August i want each day to have a value of 148
In September i want each day to have a value of 263
and so on through to next June

So, depending on what day we are on it will be the sum of the previous days for the right month..

I hope this helps?

Can anyone advise?

Many thanks

Brian

 

[IanWaterman]

Something like this should give you what you want

DEclare @StartDate as DATE = '2018-07-01'
DEclare @Testdate as date = '2018-09-10'--Getdate()

Select Case when Month(@Testdate) = Month(@StartDate) then 220 * day(@Testdate)
when Month(@Testdate) = Month(@StartDate)+1 then 148 * day(@Testdate)
when Month(@Testdate) = Month(@StartDate)+2 then 264 * day(@Testdate)

.....
else Month(@Testdate) = Month(@StartDate)+11 then (june2019 value) * day(@Testdate) end dayvalue

Ian

 

[Brianfree]

Hi Ian, thanks for replying - this seems to do what i need it to do, i can add the previous months total also so the new month starts with the previous months end total

Many thanks

Brian

 

[Brianfree]

When @Testdate starts a new year, the code down not work, how can i modify to work with a new year (2019)

This is my code so far..

Declare @Testdate as date = '2019-03-01'

Select 

Case 
WHEN Month(@Testdate) = Month('2018-07-01') THEN 220 * day(@Testdate) -- JULY
WHEN Month(@Testdate) = Month('2018-07-01') + 1 THEN 148 * day(@Testdate) + (6820) -- AUGUST
WHEN Month(@Testdate) = Month('2018-07-01') + 2 THEN 263 * day(@Testdate) + (6820 + 4582) -- SEPTEMBER
WHEN Month(@Testdate) = Month('2018-07-01') + 3 THEN 236 * day(@Testdate) + (6820 + 4582 + 7898) -- OCTOBER
WHEN Month(@Testdate) = Month('2018-07-01') + 4 THEN 241 * day(@Testdate) + (6820 + 4582 + 7898 + 7326) -- NOVEMBER
WHEN Month(@Testdate) = Month('2018-07-01') + 5 THEN 195 * day(@Testdate) + (6820 + 4582 + 7898 + 7326 + 7228) -- DECEMBER
WHEN Month(@Testdate) = Month('2018-07-01') + 6 THEN 195 * day(@Testdate) + (6820 + 4582 + 7898 + 7326 + 7228 + 6044) -- JANUARY
WHEN Month(@Testdate) = Month('2018-07-01') + 7 THEN 163 * day(@Testdate) + (6820 + 4582 + 7898 + 7326 + 7228 + 6044 + 5056) -- FEBRUARY

end as [Target]

Many thanks

Brian

 

[Olaf Doschke]

Well, MONTH() of any date will be betwwen 1 and 12. You use Month('2018-07-01'), which is 7, so what numbers do you get? 7,8,9,10,11,12,13,14.
Just write out the numbers, your initial case is 7, But in January this has to skip back to 1 and then continue up to 6=June.

If you want that continue with the offset for all of July 2018 to June 2019 in July 2019, then you'd rather need to compute DATEDIFF(month,'2018-07-01',@Testdate) to get full months passed. In that case the condition would change beginning with WHEN DATEDIFF(month,'2018-07-01',@Testdate)=0 for this month, July 2018, WHEN DATEDIFF(month,'2018-07-01',@Testdate)=1 for AUGUST, etc. but would never roll up.

You can also count the days with DATEDIFF(day,'2018-07-01',@Testdate), but the values change per month, so you'd rather stay this way or think of a table for such meta data, that you then could also adapt for next year.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[IanWaterman]

Silly mistake on my part, Olaf's solution works. Not sure what he means by will not roll up?

Declare @Testdate as date = '2019-02-01'

Select

Case
WHEN DATEDIFF(month,'2018-07-01',@Testdate)=0 THEN 220 * day(@Testdate) -- JULY
WHEN DATEDIFF(month,'2018-07-01',@Testdate)= 1 THEN 148 * day(@Testdate) + (6820) -- AUGUST
WHEN DATEDIFF(month,'2018-07-01',@Testdate)= 2 THEN 263 * day(@Testdate) + (6820 + 4582) -- SEPTEMBER
WHEN DATEDIFF(month,'2018-07-01',@Testdate)= 3 THEN 236 * day(@Testdate) + (6820 + 4582 + 7898) -- OCTOBER
WHEN DATEDIFF(month,'2018-07-01',@Testdate)= 4 THEN 241 * day(@Testdate) + (6820 + 4582 + 7898 + 7326) -- NOVEMBER
WHEN DATEDIFF(month,'2018-07-01',@Testdate)= 5 THEN 195 * day(@Testdate) + (6820 + 4582 + 7898 + 7326 + 7228) -- DECEMBER
WHEN DATEDIFF(month,'2018-07-01',@Testdate)= 6 THEN 195 * day(@Testdate) + (6820 + 4582 + 7898 + 7326 + 7228 + 6044) -- JANUARY
WHEN DATEDIFF(month,'2018-07-01',@Testdate)= 7 THEN 163 * day(@Testdate) + (6820 + 4582 + 7898 + 7326 + 7228 + 6044 + 5056) -- FEBRUARY

end as [Target]

 

[Olaf Doschke]

Will not roll up" is not really database or English standard vocabulary. I think I had "rollover" in mind, doesn't matter much.

What I mean is, if you continue this for @testdate values in 2019 Month(@Testdate) would repeat at 1,2,3,4...and you'll get weird results. Now that you're using DATEDIFF(month,'2018-07-01',@Testdate) this will get higher unbound and you'll never get back to earlier cases unless you change the reference date '2018-07-01'. And indeed, one way to make this code year independent would be to change this '2018-07-01' reference date once yearly, perhaps each July, if July is the first month of your company's business year.

I guess you have in mind to change the query once a year, maybe next year the values for each month will differ anyway, then that's fine. As a developer, my goal would always be code, which I don't need to change, not even yearly. Only regarding the data it uses, at best.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name