I have a table which has fields
first i need to group the data by Code and company. i.e
and so on
and in each of these groups i need to loop and apply some business rules to identify single record .My purpose is to identify one good record among all the records
My business rules are like
Kindly let me know how to approach this, Will cursors be the right choice or can i just do this with temp table.
I am using SQL Server 2008 R2.
Thanks in advance Kannan
Something like you have described should be possible to do with single select with jois to the lookup tables. No cursors or other kind of processing in loops is necessary.
A select similar to the one below should solve your problem:
It joins the lookup tables using left join.. If the ZIP code is found, then it assigns the LocateResult = 1 the highest one. If ZIP is not found, but the City was found than it assigns 2. In case non of those two is found, then in case the Type = 1 it assigns 3 and in all other cases it assigns 4.
Finally it uses the ROW_NUMBER() which is partitioned per [Code] and [Company] and inside that group the records are numbered by the Locate Results.
Final condition takes only the first record from each group, the GroupOrder is orderred according your business rules, it takes the first oen which meets your condition.
Eventually if you want a row, even there is no business rule met, then simply remove the
I don't think you need a cursor for this type of query - cursors are only needed if you want to apply logic to each row one at a time (and even then there are often set based ways of achieving what initially looks like something that has to be done row by row!).
From what I understand in this instance you want to apply the same rules to all records - so I would normally do this in a series of queries - one query for each business rule. I would select the output of each query into a temp table that is used as the input to the next query. Each query will limit the results based on the required logic until you have the final results with all rules applied.
You could probably also do this as you suggest with an IsValid flag. Again I would write one query for each rule, but rather than selecting the results into a temp table you would update the flag in the table instead.
answered Nov 14, 2012 at 10:19 AM