Solved

# MS SQL Query De-Mystified

Posted on 2009-07-13
184 Views
Can some one translate this query so it can be understood as to what it is doing?  Feel free to explain it as if you were talking to a Monkey or 3 year old

SELECT P.County_ID, P.Parcel_ID, P.Rate_ID, P.Add_ID, P.Exempt, IIf(P.UnitAssessment=-1,P.UnitAssessmentValue,IIf(P.BuildHT>=4,(P.CTaxLand*(P.Benef*0.01))+((P.CTaxBuild/P.BuildHT)*3*(P.Benef*0.01))+((P.CTaxBuild/P.BuildHT)*(P.BuildHT-3)*0.06),IIf(P.Partial_Prot=-1,IIf(P.Bldg_Incl=-1,(P.CTaxLand*(P.Wet_Perc*0.01)*0.03)+(P.CTaxBuild*0.03),P.CTaxLand*(P.Wet_Perc*0.01)*0.03),(P.CTaxLand+P.CTaxBuild)*(P.Benef*0.01)))) AS dblBenefit, BR.Bond_rate, TR.Tax_Rate, P.id, P.UnitAssessment, P.UnitAssessmentValue, TR.TaxRate_Description, P.MainCounty
FROM BondRate AS BR, MATCHEDTAXPARCELS AS P INNER JOIN TaxRate AS TR ON P.Rate_ID=TR.CharCode;
0

LVL 15

Accepted Solution

mohan_sekar earned 500 total points
ID: 24841937
Pseudo code:

if p.UnitAssessment = -1 then
dblBenefit = p.UnitAssessmentValue
else
if (p.BuildHT >= 4) then
dblBenefit = (P.CTaxLand*(P.Benef*0.01))+((P.CTaxBuild/P.BuildHT)*3*(P.Benef*0.01)) +
((P.CTaxBuild/P.BuildHT)*(P.BuildHT-3)*0.06)
else
if P.Partial_Prot = -1 then
if P.Bldg_Incl = -1 then
dblBenefit = (P.CTaxLand*(P.Wet_Perc*0.01)*0.03)+(P.CTaxBuild*0.03)
else
dblBenefit = P.CTaxLand*(P.Wet_Perc*0.01)*0.03)
end if
else
dblBenefit = (P.CTaxLand+P.CTaxBuild)*(P.Benef*0.01)
end if
end if
0

LVL 4

Expert Comment

ID: 24842301
The best way to understand a query is to actually format it.  Once I did that, I could see better.  Without understanding the exact nature of the query, the vast majority of it is straightforward.  It is a join where most of the fields are selected.  the field dblBenefit looks like it has some serious logic to determine it's value.  That would take an understanding of the domain to do it justice.  Also, one thing I did notice is that there are 3 tables but only two of them are joined.  BondRate is not joined with anything.  You have MatchedTaxParcels joined with Tax Rate and the result of that will be FULL OUTER JOINED with BondRate.  This is more than likely a mistake.
``````SELECT P.County_ID,

P.Parcel_ID,

P.Rate_ID,

P.Exempt,

IIf(P.UnitAssessment=-1,P.UnitAssessmentValue,

IIf(P.BuildHT>=4,(P.CTaxLand*(P.Benef*0.01))+((P.CTaxBuild/P.BuildHT)*3*(P.Benef*0.01))+((P.CTaxBuild/P.BuildHT)*(P.BuildHT-3)*0.06),

IIf(P.Partial_Prot=-1,IIf(P.Bldg_Incl=-1,(P.CTaxLand*(P.Wet_Perc*0.01)*0.03)+(P.CTaxBuild*0.03),P.CTaxLand*(P.Wet_Perc*0.01)*0.03),(P.CTaxLand+P.CTaxBuild)*(P.Benef*0.01)))) AS dblBenefit,

BR.Bond_rate,

TR.Tax_Rate,

P.id,

P.UnitAssessment,

P.UnitAssessmentValue,

TR.TaxRate_Description,

P.MainCounty

FROM BondRate AS BR, MATCHEDTAXPARCELS AS P

INNER JOIN TaxRate AS TR ON P.Rate_ID=TR.CharCode
``````
0

## Featured Post

### Suggested Solutions

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be \$37.1B.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…