SQLTeam.com | Weblogs | Forums

Creating groups with same set of locations


#1

Hi,

I've run into a tricky issue where I need to create groups based on the combination of locations within sets and take the max deductible amount for each.

Here is a sample of the dataset that I have:

declare @DedTest TABLE
(
Area nvarchar(25),
AccountNum nvarchar(10),
PolicyNum nvarchar(10),
SetId int,
LocId int,
LocDedAmt decimal(18,2),
NonLocDedAmt decimal(18,2)
)

insert into @DedTest
select 'California' as Area,
'56756' as AccountNum,
'1003375' as PolicyNum,
80915 as SetId,
1 as LocId,
cast(2000000.00 as decimal(18,2)) as LocDedAmt,
cast(0 as decimal(18,2)) as NonLocDedAmt
UNION ALL
select 'California' as Area,
'56756' as AccountNum,
'1003375' as PolicyNum,
80915 as SetId,
2 as LocId,
cast(2000000.00 as decimal(18,2)) as LocDedAmt,
cast(0 as decimal(18,2)) as NonLocDedAmt
UNION ALL
select 'California' as Area,
'56756' as AccountNum,
'1003375' as PolicyNum,
80915 as SetId,
3 as LocId,
cast(2000000.00 as decimal(18,2)) as LocDedAmt,
cast(0 as decimal(18,2)) as NonLocDedAmt
UNION ALL
select 'California' as Area,
'56756' as AccountNum,
'1003375' as PolicyNum,
80915 as SetId,
4 as LocId,
cast(2000000.00 as decimal(18,2)) as LocDedAmt,
cast(0 as decimal(18,2)) as NonLocDedAmt
UNION ALL
select 'California' as Area,
'56756' as AccountNum,
'1003375' as PolicyNum,
80915 as SetId,
5 as LocId,
cast(2000000.00 as decimal(18,2)) as LocDedAmt,
cast(0 as decimal(18,2)) as NonLocDedAmt
UNION ALL
select 'California' as Area,
'56756' as AccountNum,
'1003375' as PolicyNum,
80916 as SetId,
6 as LocId,
cast(5000000.00 as decimal(18,2)) as LocDedAmt,
cast(0 as decimal(18,2)) as NonLocDedAmt
UNION ALL
select 'California' as Area,
'56756' as AccountNum,
'1003375' as PolicyNum,
80916 as SetId,
7 as LocId,
cast(5000000.00 as decimal(18,2)) as LocDedAmt,
cast(0 as decimal(18,2)) as NonLocDedAmt
UNION ALL
select 'California' as Area,
'56756' as AccountNum,
'1003375' as PolicyNum,
80921 as SetId,
1 as LocId,
cast(100000.00 as decimal(18,2)) as LocDedAmt,
cast(0 as decimal(18,2)) as NonLocDedAmt
UNION ALL
select 'California' as Area,
'56756' as AccountNum,
'1003375' as PolicyNum,
80922 as SetId,
2 as LocId,
cast(500000.00 as decimal(18,2)) as LocDedAmt,
cast(0 as decimal(18,2)) as NonLocDedAmt
UNION ALL
select 'California' as Area,
'56756' as AccountNum,
'1003375' as PolicyNum,
80924 as SetId,
2 as LocId,
cast(0 as decimal(18,2)) as LocDedAmt,
cast(500000 as decimal(18,2)) as NonLocDedAmt
UNION ALL
select 'California' as Area,
'56756' as AccountNum,
'1003375' as PolicyNum,
80924 as SetId,
3 as LocId,
cast(0 as decimal(18,2)) as LocDedAmt,
cast(500000 as decimal(18,2)) as NonLocDedAmt
UNION ALL
select 'California' as Area,
'56756' as AccountNum,
'1003375' as PolicyNum,
80924 as SetId,
4 as LocId,
cast(0 as decimal(18,2)) as LocDedAmt,
cast(500000 as decimal(18,2)) as NonLocDedAmt

select * from @DedTest

So what I need to come out with is a grouping (let's call the groups A, B, C, etc...) for each combination of Area, AccountNum, POlicyNum, SetId and Location where the locations are part of the same combination of Area, AccountNum, PolicyNum, and SetId.

So in my sample data, set 80915 has 5 locations. Location 1 is also part of set 80921. Location 2 is also part of sets 80922 and 80924. Set 80924 also has locations 3 and 4 which are also part of set 80915. Set 80916 has locations 6 and 7 by themselves. Location 5 is only in set 80915.

So in the end I need this:

declare @DedResults TABLE
(
Area nvarchar(25),
AccountNum nvarchar(10),
PolicyNum nvarchar(10),
GroupNm nvarchar(100),
LocDedAmt decimal(18,2),
NonLocDedAmt decimal(18,2)
)

insert into @DedResults
select 'California' as Area,
'56756' as AccountNum,
'1003375' as PolicyNum,
'A ( this group is for Location 1 which is in both set 80915 and 80921)' as GroupNm,
2000000 as LocDedAmt,
0 as NonLocDedAmt
Union ALL
select 'California' as Area,
'56756' as AccountNum,
'1003375' as PolicyNum,
'B ( this group is for Location 2 which is in set 80915, 80922 and 80924)' as GroupNm,
2000000 as LocDedAmt,
500000 as NonLocDedAmt
Union ALL
select 'California' as Area,
'56756' as AccountNum,
'1003375' as PolicyNum,
'C ( this group is for Locations 6 and 7 which are only part of set 80916)' as GroupNm,
2000000 as LocDedAmt,
0 as NonLocDedAmt
Union ALL
select 'California' as Area,
'56756' as AccountNum,
'1003375' as PolicyNum,
'D ( this group is for Locations 3 and 4 which are part of set 80915 and 80924)' as GroupNm,
2000000 as LocDedAmt,
500000 as NonLocDedAmt
Union ALL
select 'California' as Area,
'56756' as AccountNum,
'1003375' as PolicyNum,
'E ( this group is for Location 5 which only part of set 80915)' as GroupNm,
2000000 as LocDedAmt,
0 as NonLocDedAmt

select * from @DedResults

Here is the tricky part. You will notice that Location 2 is part of group B but removed from group D because it it was also part of set 80922 by itself. So I need to separate the locations in set 80924 because of location 2 being in another set where location 3 and 4 are not (80922).

Once i know the groups, i need to get the max on the LocDedAmt and NonLocDedAmt for the group of locations and Set. For example, Location 1 was in set 80915 and 80921 but the higher LocDedAmt was the one in 80915 (2,000,000 vs 100,000) so that is what I need to grab.

I was thinking doing some type of while loop and trying to figure out the correct groups for each location and set, but I keep hitting road blocks. Any help would be appreciated.

thanks

scott


#2

with type
as
(
Select Area, AccountNum,PolicyNum, 
'GroupNm'=(
Case when LocId=1 then 'A' 
when LocId=2 then 'B'
when LocId=6 or LocId=7 then 'C'
when LocId=3 or LocId=4 then 'D'
when LocId=5 then 'E' end), LocDedAmt, NonLocDedAmt
   from #DedTest 
)
select Area, AccountNum, PolicyNum, GroupNm, Max(LocDedAmt)as LocDedAmt,Max(NonLocDedAmt) as NonLocDedAmt  from type
group by Area,AccountNum, PolicyNum,GroupNm

#3

thanks scarela, but i needed something a little more dynamic. The Groups will change with each account and policy, so for this example, we end up with A thru E, but other accounts could have more or less


#4

I'm having a real hard time understanding your explanation. Would it be possible for you to show us what the expected output would be?


#5

Can you explain a little bit better how the groups are going to be combined. What's the pattern that they must follow. And show an example in how the results should look like with differents accounts


#6

Would this work?

SELECT 
    Area 
  , AccountNum
  , PolicyNum
  , SetId
  , LocId
  , Max(LocDedAmt) Max_LocDedAmt
  , Max(NonLocDedAmt) Max_NonLocDedAmt
  , Area+'-'+AccountNum+'-'+PolicyNum+'-'+Cast(SetId AS varchar)+'-'+Cast(LocId AS varchar) GroupName
FROM @DedTest
GROUP BY
    Area 
  , AccountNum
  , PolicyNum
  , SetId
  , LocId ;