lundi 29 décembre 2014

If field = Null then use a different field

I have written some Sql code to display all clients who's offers are about to expire in the next 90 days by using the dateOffered field. However there is another field in the database called OfferExpirydate I would use this field however it it not always filled out.


My question is i want the code to look at OfferExpirydate and if it has a value then use it or else use the Dateoffered field as my code below stats. ( if the OfferExpirydate is not filled out it is set to a NULL ) Any help on this would be great thanks



SELECT
DateOffered,
pr.ClientID,
pr.id AS profileID,
cf.Clntnme,
pm.Lender,
ABS(DATEDIFF(DAY, DateOffered, DATEADD(d,-90, GETDATE()))) AS 'NoOfDays'
FROM tbl_profile AS pr
INNER JOIN tbl_Profile_Mortgage AS pm
ON pr.id = pm.fk_profileID
INNER JOIN dbo.tbl_ClientFile AS cf
ON pr.ClientID = cf.ClientID
WHERE
DateCompleted IS NULL AND
DateOffered > DATEADD(d,-90, GETDATE())
AND DATEDIFF(DAY, DateOffered, DATEADD(d,-90, GETDATE())) > -15
ORDER BY DateOffered ASC

Aucun commentaire:

Enregistrer un commentaire