Query Question

rocky050371
rocky050371 used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
Because of the ( )'s, the SQL block below HERE >>> acts as a WHERE clause for the above SQL block.

Return the SQL in the block above it, only (WHERE) if the count of all the rows equals zero in the CheckRecords table that have ItemRef=37, Checked=1 (True), and ContractRef = tenent table (above t), Reference column,

Author

Commented:
Still not sure I understand, the first part of the SQL

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,

Checks if the flag is ticked if it is then it returns 0 else it returns once, not sure how this relates to the bottom where. If there are 10 records half checked, half not where does the bottom where come in?
Michael CarrilloInformation Systems Manager
Top Expert 2012

Commented:
The bottom Where is the outer Where that effects everything.  Since each condition is linked with an 'AND' these conditions must be true for everything within.

Once you have evaluated the bottom Where then you can look at the returned values of the select which have their own conditions.

The last part of the Where clause is requiring that there be some records that meet the requirements.
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Author

Commented:
it is strange becuase there will only be an entry in checkrecords if there is a tick, so if the bottom where clause is evaluated and returns a set of records then can't understand why the intial select statement starts with a clause
Michael CarrilloInformation Systems Manager
Top Expert 2012

Commented:
I think I see part of the confusion.

The initial case statement seems to be a work around for the outer where clause. It is pulling up the MarkedForRenewal while the bottom Where is pulling those that are NOT MarkedForRenewal.

What this Select is doing is pulling up a count of those that are MarkedForRenewal and providing information about everything else (those that are current or NOT MarkedForRenewal).

Author

Commented:
Based on the bottom where clause though, the case statement at the top would always be 1 would it not?
Michael CarrilloInformation Systems Manager
Top Expert 2012

Commented:
You know, I think I got it reversed.
Those marked for Renewal are actually a value of 0.

To answer your question;
No, its a count of those marked as 0.  The statement looks for those marked as 1 and puts a value of 0 for that count. When it encounters a value marked as 0 then it puts a value of 1 for the count (it is accumulated as part of the count). Thus the first statement counts how many have a value of 0.

I hope this makes sense.

Author

Commented:
I have found out a little bit more from the program which sits on top of it, a record will only appear in CheckRecords if it is ticked i.e. 1


So if non of the records have a tick then the bottom WHERE clause will return 0 i.e.it should proceed.

(SELECT COUNT (*)
FROM
CheckRecords
WHERE
CheckRecords.ItemRef = 37
AND
CheckRecords.Checked = 1
AND
CheckRecords.ContactRef = T.Reference) =0

The top case statement will always return 1 i.e MarkedForRenewal = True

I can't see the need for this case statement, I am obviously missing something here.

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,
Michael CarrilloInformation Systems Manager
Top Expert 2012

Commented:
It looks like I have to eat my words; The first CASE statement is a subquery and is returning a count independent of the bottom Where clause.

However, it looks like the first case statement is selecting only a portion of the where clause for its count. That is why you can't do a straight count. The first count includes values that were exclude in the first part of the Where clause;

....
      AND T.Expired = 0
      AND T.Archive = 0
      AND p.Branch LIKE 'Test'
....

Author

Commented:
yes but thats my point, the subquery at the top is surely redundant if the where clause at the bottom is executed.
Michael CarrilloInformation Systems Manager
Top Expert 2012

Commented:
Not redundant. The Where clause includes:
....
      AND T.Expired = 0
      AND T.Archive = 0
      AND p.Branch LIKE 'Test'
....

The Case does not.  Thus your count can be greater than the records returned.

Author

Commented:
Lol I still dont get it, the case at the top is simply evaluating whether or not there are any records with a check but these have already been excluded from the second part of the where clause at the bottom.
Michael CarrilloInformation Systems Manager
Top Expert 2012

Commented:
To make an analogy;

It like saying I want a count of all orders and information on all completed orders.
So the first field would include both completed and uncompleted orders in the count. While the remaining fields would only look at completed order information.

Remember the first case statement is actually a subquery and can collect information independently of the remaining SQL.

Author

Commented:
Yes but what is the point if you are then going to exclude them in the final where clause
Michael CarrilloInformation Systems Manager
Top Expert 2012

Commented:
I would think it is some kind of validation.  In the example, I provided, if you had a large number of uncompleted orders you would look for a problem.  Perhaps, this is a validation to confirm that you are not accidently excluding too many records. This is a complex query.

Author

Commented:
Maybe your right, I just don't see the need for it, perhaps they were testing something initially. he point is if you exclude records that are not in the checkrecords table then everything else by default needs executing
Information Systems Manager
Top Expert 2012
Commented:
Your right.  However, this might be a built in check to make sure everything is working as expected.  If you don't get the expected number of records, either they are mis-classified or don't exist. This test might help you find records that are expired or archived that should not have been flagged as such. In any event, it does not appear that this code is preventing your application from working correctly.  

That being said, I would keep it as it is unless you are experiencing a performance issue.  How may records does this application typically pull and does it take a long time?

Author

Commented:
Less than 20

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial