MS SQL Query De-Mystified

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;
LVL 1
Jason JonesGIS/APP DevAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mohan_sekarCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
j_s_kelleyCommented:
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.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

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.