Link to home
Start Free TrialLog in
Avatar of Basssque
Basssque

asked on

SSIS/SSDT Derived Table - calculating dates within the last year

I'm trying to calculate dates within the last year and output a string if true

I've attempted the following syntax but it doesn't work
JOBS_CONTRACT_END_DATE < (DT_WSTR,10)(DT_DBDATE)(GETDATE()) && JOBS_CONTRACT_END_DATE > (DT_WSTR,10)(DT_DBDATE)(GETDATE()-365) ? "ALERT(Certificate Has Expired within the past year and employment status is yes)" : CERTIFICATES_TO_DATE

I was able to use the following syntax to output a string if the date is only less than the current date.  
I need to add something to only output the string for dates within the last year though.
JOBS_CONTRACT_END_DATE < (DT_WSTR,10)(DT_DBDATE)(GETDATE()) ? "ALERT(Certificate Has Expired)" : CERTIFICATES_TO_DATE

Thanks!!
ASKER CERTIFIED SOLUTION
Avatar of zephyr_hex (Megan)
zephyr_hex (Megan)
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Basssque
Basssque

ASKER

Fantastic!  Thank you so much!

I had to change my logic to accomplish the goal, but your answer was perfect!

Below is the functional end result
JOBS_CONTRACT_END_DATE > (DT_WSTR,10)(DT_DBDATE)(GETDATE()) && CERTIFICATES_TO_DATE < (DT_WSTR,10)(DT_DBDATE)DATEADD("yy",-1,GETDATE()) ? "ALERT(Certificate Has Expired within the past year AND contract end date is still current)" : CERTIFICATES_TO_DATE