troubleshooting Question

Query Question

Avatar of rocky050371
rocky050371 asked on
Microsoft SQL Server 2005SQL
18 Comments1 Solution397 ViewsLast Modified:
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
ASKER CERTIFIED SOLUTION
Michael Carrillo
Information Systems Manager
Join our community to see this answer!
Unlock 1 Answer and 18 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 18 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros