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(Y
'c:\AutoLtrs\' + P.FolderPath AS FPath,
CONVERT(VARCHAR,DATEPART(y
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,
BETWEEN (Convert(INT,convert(FLOAT
AND (Convert(INT,convert(FLOAT
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
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(Y
'c:\AutoLtrs\' + P.FolderPath AS FPath,
CONVERT(VARCHAR,DATEPART(y
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,
BETWEEN (Convert(INT,convert(FLOAT
AND (Convert(INT,convert(FLOAT
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
Microsoft SQL Server 2005SQL
Last Comment
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?
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.
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.
Your help has saved me hundreds of hours of internet surfing.
fblack61
ASKER
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).
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).
ASKER
Based on the bottom where clause though, the case statement at the top would always be 1 would it not?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
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.
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.
ASKER
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,
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'
....
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'
....
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
ASKER
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.
....
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.
ASKER
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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
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.
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.
ASKER
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.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
ASKER
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
Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a questionASKER
Less than 20
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,