HOw to write cases in SQLSERVER

karthik parcha

15y1.8k

HI this ismy issue please help me
Contract Signing Date + 1 year - 1 day.
(Example if the contract signed was on 2/3/2011 then it would be active through 2/2/2012.Then i have to show textmarketing status active otherwise if the contract is not in between the dates then inactive please help me
IF text marketing status is active i have to show expiration date
inactive i have to expiration date NULL
I have written alomost but not getting result
SIGNING_DATE <= EXPIRATION_DATE(

Explanation:FIrst i have updated the Expiration date column by siging date
UPDATE T
SET T.EXPIRATION_DATE=(DATEADD(DAY,-1,(DATEADD(YEAR,1,T.SIGNING_DATE))))//Contract Signing Date + 1 year - 1 day.

//Iam checking the condition with expiration can u plesae write in one single update statement if possible

UPDATE #TEMP_SB SET TEXT_MARKETING_STATUS =
(CASE
WHEN SIGNING_DATE <= EXPIRATION_DATE THEN 'ACTIVE'
ELSE 'INACTIVE'
END)
UPDATE #TEMP_SB set EXPIRATION_DATE=NULL WHERE TEXT_MARKETING_STATUS='INACTIVE'
SELECT * FROM #TEMP_SB

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

karthik parcha15y ago
NO
In my table there is a SIGNED DATE COLUMN
(Example if the contract signed was on 2/3/2011 then it would be active through 2/2/2012
Contract Signing Date + 1 year - 1 day.
SO,
I have to write an update statement that using between operator in cases or
some other way please once read my pose please help me
0
Dhaval Patel15y ago
Dear karthik

are you want only update query for case then are you write this code

UPDATE #TEMP_SB
SET TEXT_MARKETING_STATUS =CASE WHEN SIGNING_DATE <= EXPIRATION_DATE
THEN 'ACTIVE' ELSE 'INACTIVE' END

this post help you than mark as "Accepted Answer"
0