Solved

MS SQL Query De-Mystified

Posted on 2009-07-13
2
184 Views
Last Modified: 2012-05-07
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
Comment
Question by:upgraders
2 Comments
 
LVL 15

Accepted Solution

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

by:j_s_kelley
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.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

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

'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…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now