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.
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
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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,
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,
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.
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).
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.
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,
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'
....
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.
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.
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.
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
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?
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?
Premium Content
You need an Expert Office subscription to comment.Start Free Trial