CODEWITHSUNDEEP
sqlsql-server
Lewis Williams
Lewis Williams

Reputation: 55

Calculate day from static date

Im trying to calculate targets based as if they were on 01/01/2018,

Targets are calculated as target/5(days)*network days in month and then reduced based on start date 1/3, 2/3's full

The below code works well however this is calculating targets as of today and i need to amend this to calculate as of 01/01/2018.

I will then creating a new column for each future month.

when i amend the code and input a date rather than get date i get the below error

when StartDate < cast('2018-01-01' as date)-150 then cast(WeeklyIssueTarget as numeric(17,2))/5*23

Msg 206, Level 16, State 2, Line 1 Operand type clash: date is incompatible with int

Any help would be appreciated

Thanks 

Select *,
    case when StartDate is null then cast(WeeklyIssueTarget as numeric(17,2))/5*23 
    when StartDate < GETDATE()-150 then cast(WeeklyIssueTarget as numeric(17,2))/5*23
    when StartDate < GETDATE()-120 then (cast(WeeklyIssueTarget as numeric(17,2))/5*23)/3*2
    when StartDate < GETDATE()-90 then (cast(WeeklyIssueTarget as numeric(17,2))/5*23)/3*1 
    when StartDate < GETDATE()-89 then 0      end as Jan18
from Details

Upvotes: 0

Views: 103

Answers (1)

sticky bit
sticky bit

Reputation: 37472

getdate() returns a datetime, not a date. So to be equivalent cast to datetime as well, instead of to date.

...
... cast('2018-01-01' as datetime) ...
...

To be safe and more verbose however, you might want to consider using dateadd() for the calculations.

Upvotes: 1

Related Questions