Query Expression Optimisation

danielw.au

Registered User.
Local time
, 10:19
Joined
Mar 12, 2014
Messages
10
Hi Everyone,

I'm wanting some advice on how to optimise a query expression.

I have the below query that determines the employees rate, based on which bracket their total income falls into.

I'm concerned that I have not writting this expression in the most efficient manner and would appreciate any suggestions.

SepEmployeeIncomeTaxRate: IIf([SepEmployeeTotalIncome] Between [Sep_Txl_TaxGroup1_Min] And [Sep_Txl_TaxGroup1_Max],[Sep_Txl_TaxGroup1_Rate],IIf([SepEmployeeTotalIncome] Between [Sep_Txl_TaxGroup2_Min] And [Sep_Txl_TaxGroup2_Max],[Sep_Txl_TaxGroup2_Rate],IIf([SepEmployeeTotalIncome] Between [Sep_Txl_TaxGroup3_Min] And [Sep_Txl_TaxGroup3_Max],[Sep_Txl_TaxGroup3_Rate],IIf([SepEmployeeTotalIncome] Between [Sep_Txl_TaxGroup4_Min] And [Sep_Txl_TaxGroup4_Max],[Sep_Txl_TaxGroup4_Rate],IIf([SepEmployeeTotalIncome] Between [Sep_Txl_TaxGroup5_Min] And [Sep_Txl_TaxGroup5_Max],[Sep_Txl_TaxGroup5_Rate],0)))))

Thank you
daniel
 
Optimise means many things - do you mean readability? speed of operation? something else?

You could try using the switch function instead.

Also for readability, try reducing the length of your field names - it makes my eyes ache;)

if Sep_Txl_TaxGroup1_Max=Sep_Txl_TaxGroup2_Min or only different by 0.01 then you can use < or > against one of them

Code:
 IIf([SepEmployeeTotalIncome] < [Sep_Txl_TaxGroup1_Max],[Sep_Txl_TaxGroup1_Rate],
 IIf([SepEmployeeTotalIncome] < [Sep_Txl_TaxGroup2_Max] etc
 
I would create a user defined function to return the group rate based on the TotalInclome. Then the SQL will be simple.
 
Another problem is that the data is not normalized. If the Group1, Group2, ..., GroupN data was in different rows in the table, rather than numbered columns, then you could find the result with a single very simple query. With this design you have to check all five column sets explicitly, which is very cumbersome.
 

Users who are viewing this thread

Back
Top Bottom