Re: Nesting an IF in the ELSE of a WHEN

xyb

On 10 16 , 8 07 , jonefer <jone...@discussions.microsoft.com> wrote:
> I have a Case Statement that almost works.
>
> I have marked the troublesome pseudo portion with [[ around my desired
> outcome ]]
>
> CASE
> WHEN EXISTS (SELECT *
> FROM tbl_DelSup
> WHERE DelID =
> purchasing_reg.tbl_delegation.Del_ID AND DateApproved IS NOT NULL) THEN
> NULL
> ELSE
> SigDeadline [[BUT only if SigDeadline is Less than today]]
> END AS Archived
>
> I have this much: If purchasing_reg.tbl_Delegation.SigDeadline <
> DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)
> THEN SigDeadline
> Else
> NULL
>
> but it needs to be stuck into the ELSE of the WHEN and I can't seem to get
> it to work.
CASE
WHEN EXISTS (SELECT *
FROM tbl_DelSup
WHERE DelID =
purchasing_reg.tbl_delegation.Del_ID AND DateApproved IS NOT NULL)
THEN
NULL
when purchasing_reg.tbl_Delegation.SigDeadline < DATEADD(day,
DATEDIFF(day, 0, GETDATE()), 0) and
not EXISTS (SELECT * FROM tbl_DelSup
WHERE DelID =purchasing_reg.tbl_delegation.Del_ID aND
DateApproved IS NOT NULL) then
SigDeadline
else
null
END AS Archived