Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I am calculating a date based on 2 values being passed into load script below:
Sub_Contract_Start_Date,
Sub_Contract_Term,
AddMonths ([Sub_Contract_Start_Date],[Sub_Contract_Term]) as [Sub_Contract_End_Date]
What I want to also do is identify any of the dates I create as part of the load which are within the next 30 days i.e. the date created which is named Sub_Contract_End_Date and give them an indicator e.g. contract expiring in next 30 days.
Is it possible to do this within the same load script or do I need to create a resident table to do the calculation?
I was thinking extending the expression with an IF statement but wasnt sure whether that was possible.
Any help appreciated.
thanks
Martin
You should be able to do this in the same load statement, but I am just not sure what is this getting compared to? Today? may be this
Sub_Contract_Start_Date,
Sub_Contract_Term,
AddMonths([Sub_Contract_Start_Date], [Sub_Contract_Term]) as [Sub_Contract_End_Date],
If(AddMonths([Sub_Contract_Start_Date], [Sub_Contract_Term]) - Today() <= 30, 1, 0) as ContractExpiringFlag
You should be able to do this in the same load statement, but I am just not sure what is this getting compared to? Today? may be this
Sub_Contract_Start_Date,
Sub_Contract_Term,
AddMonths([Sub_Contract_Start_Date], [Sub_Contract_Term]) as [Sub_Contract_End_Date],
If(AddMonths([Sub_Contract_Start_Date], [Sub_Contract_Term]) - Today() <= 30, 1, 0) as ContractExpiringFlag
Hi Sunny this worked great for me, made some amends to your original solution which now allows me to generate a contract status based on the data.
If(Interval(AddMonths([Sub_Contract_Start_Date], [Sub_Contract_Term]) - Today()) >0 and Interval(AddMonths([Sub_Contract_Start_Date], [Sub_Contract_Term]) - Today()) <30, 'Contract Ending',if(AddMonths([Sub_Contract_Start_Date], [Sub_Contract_Term]) - Today() >30,'In Contract','Out Of Contract')) as Sub_Contract_Status
I think i have it right but many thanks for the steer.
cheers
Martin