I have just picked up the following query, can anyone tell me what the bottom and clause is doing?
SELECT
CASE
WHEN
(SELECT COUNT (*)
FROM
CheckRecords
WHERE
CheckRecords.ItemRef = 37
AND
CheckRecords.Checked = 1
AND
CheckRecords.ContactRef = T.Reference) =1
THEN 0
ELSE 1
END AS MarkedForRenewal,
CASE
WHEN T.aemail = 1
THEN 'Email'
ELSE 'Printed' END As TTContact,
CASE
WHEN L.aemail = 1
THEN 'Email'
ELSE 'Printed' END As LLContact,
Ten.REFerence AS TenancyRef,
Ten.Agreementname,
t.reference AS TUREF,
T.Archive,
T.Surname,
T.Dear AS TDear,
T."End Date" AS TenendDate,
DATENAME (dd,T."End Date")+' '+DATENAME (MM,T."End Date")+' '+DATENAME (YY,T."End Date") AS RenDate,
T.email AS TTEmail,
T.aemail AS TTAUTOEmail,
getdate()+77 AS REFDate,
isnull(p.Address1,'')+' '+isnull(p.Address2,'')+' '+isnull(p.Address3,'')+' '+isnull(p.Address4,'')+' '+isnull(p.Address5,'')+' '+isnull(p.Address6,'') AS Propaddy,
p.reference AS PUREF,
P.Address1,
P.Address2,
P.Address3,
P.Address4,
P.Address5,
P.Address6,
-- RIGHT(CONVERT(VARCHAR,100 + DATEPART(DD, getdate()) ) ,2) + RIGHT(CONVERT(VARCHAR,100 + DATEPART(MM, getdate()) ) ,2) + CONVERT(VARCHAR,DATEPART(YY, getdate())) + CONVERT(VARCHAR,DATEPART(HH, getdate())) + CONVERT(VARCHAR,DATEPART(n, getdate())) + CONVERT(VARCHAR,DATEPART(SS, getdate())) AS FileID,
'c:\AutoLtrs\' + P.FolderPath AS FPath,
CONVERT(VARCHAR,DATEPART(yyyy, getdate())) + '.' + RIGHT(CONVERT(varchar,100 + DATEPART(m, getdate()) ) ,2) + '.' + RIGHT (CONVERT(varchar,100 + DATEPART(d, getdate()) ) ,2) AS FileID,
L.REFerence AS LUREF,
l.email AS LLEmail,
l.aemail AS LLAUTOEmail,
L.Address1 AS LAddress1,
L.Address2 AS LAddress2,
L.Address3 AS LAddress3,
L.Address4 AS LAddress4,
L.Address5 AS LAddress5,
L.Address6 AS LAddress6,
L.AgreementName AS LAGNAME,
L.Dear AS LDear,
DATENAME (dd, getdate())+' '+DATENAME (MM,getdate())+' '+DATENAME (YY,getdate()) AS LetterDate
FROM Tenant T
LEFT JOIN Tenancy Ten ON Ten.TenantRef = T.REFerence
JOIN Property P ON P.REFerence = T."Property Ref"
JOIN Landlord L ON L.REFerence = P."Landlord Ref"
WHERE
Convert(INT,convert(FLOAT,T.[END DATE]))
BETWEEN (Convert(INT,convert(FLOAT,getdate())) + 70)
AND (Convert(INT,convert(FLOAT,getdate())) + 77)
AND T.Expired = 0
AND T.Archive = 0
AND p.Branch LIKE 'Test'
AND
HERE >>>>>>>>
(SELECT COUNT (*)
FROM
CheckRecords
WHERE
CheckRecords.ItemRef = 37
AND
CheckRecords.Checked = 1
AND
CheckRecords.ContactRef = T.Reference) =0
Premium Content
You need an Expert Office subscription to comment.Start Free Trial