Link to home
Create AccountLog in
Avatar of rocky050371
rocky050371

asked on

Query Question

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
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

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,
Avatar of rocky050371
rocky050371

ASKER

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?
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.
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
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).
Based on the bottom where clause though, the case statement at the top would always be 1 would it not?
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.
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,
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'
....
yes but thats my point, the subquery at the top is surely redundant if the where clause at the bottom is executed.
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.
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.
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.
Yes but what is the point if you are then going to exclude them in the final where clause
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.
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
ASKER CERTIFIED SOLUTION
Avatar of Michael Carrillo
Michael Carrillo
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Less than 20