x

group by and loop

I have a table which has fields

Code,Company,City,Zip,County,State,Type

first i need to group the data by Code and company. i.e

group A
Code:001 and Company:A
group B 
Code:002 and Company:A

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

  1. Do join to another table on zip code (if zip is not null) and eliminate rest of the records in that set which does not have matching zip code. 
  2. Do join to another table on City and State (if zip is null) and eliminate rest of the records in that set which does not have matching City and State. 
  3. If there are any sets which does not satisfy 3) and 4) above, get the record with Type of 1 in the set.

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

more ▼

asked Nov 14, 2012 at 08:01 AM in Default

aRookieBIdev gravatar image

aRookieBIdev
2.3k 53 57 62

I am thinking of adding a new field to my temp table called isValid and use it for flagging the valid and invalid records .. any suggestions ??
Nov 14, 2012 at 08:18 AM aRookieBIdev
i have managed a way to apply the business rules . now how do i loop them.
Nov 14, 2012 at 10:18 AM aRookieBIdev
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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:

WITH Data AS (
    SELECT
        T.[Code],
        T.[Company],
        T.[City],
        T.[Zip],
        T.[County],
        T.[State],
        T.[Type],
        CASE
            WHEN CZ.ZIP IS NOT NULL THEN 1
            WHEN C.City IS NOT NULL THEN 2
            WHEN T.[Type] = 1 THEN 3
            ELSE 4
        END AS LocateResult,
        ROW_NUMBER() OVER(PARTITION BY T.[Code], T.[Company] ORDER BY 
                            CASE
                                WHEN CZ.ZIP IS NOT NULL THEN 1
                                WHEN C.City IS NOT NULL THEN 2
                                WHEN T.[Type] = 1 THEN 3
                                ELSE 4
                            END 
                        ) AS GroupOrder
    FROM [YourTable] T
    LEFT JOIN [ZipCodeTable] ZC ON T.Zip = ZC.Zip
    LEFT JOIN [CityTable] C ON T.City = C.City
),
SELECT
    *
FROM Data
WHERE GroupOrder = 1 AND LocateResult <> 4

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 AND LocateResult<>4. It will take one of the rows which do not meet the business rules if there is no record which meets those rules. (In that case you can remove the LocateReult from the CTE and keep only the GroupOrder.
more ▼

answered Nov 14, 2012 at 02:09 PM

Pavel Pawlowski gravatar image

Pavel Pawlowski
22.3k 9 11 21

(comments are locked)
10|1200 characters needed characters left

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.
more ▼

answered Nov 14, 2012 at 10:19 AM

mjharper gravatar image

mjharper
1.4k 3 4 7

thanks for that.. i some how figured out a way to apply all my rules in a temp table. Now my problem is how do i group the data and assign it to the temp table .. i.e first i need to take Code:001 Company :A in the temp table and apply my rules then i need to move on to Code:002 and Company:A and apply the rules and so on .. Thanks.
Nov 14, 2012 at 12:05 PM aRookieBIdev
I'm not clear on why you need to loop round each code rather than applying the rules against all codes at the same time. If you can post some sample data and table schema then that would probably help me understand what you need. Thanks.
Nov 14, 2012 at 12:18 PM mjharper

hii sorryif i wasnt clear.. any way i have figured out a way for that now. i meant i need to group the data based on code and company and for each of this distinct groups i have to apply the business rules. I used temp tables for each of this groups and the did the updates.

Thanks for ur time..
Nov 14, 2012 at 01:42 PM aRookieBIdev
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x596
x4

asked: Nov 14, 2012 at 08:01 AM

Seen: 733 times

Last Updated: Nov 14, 2012 at 02:11 PM