creating groups with same set of locations
-
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
-
absolutely great job on providing the set up sample scripts.
Am i underthinking this, that it is simply a group by problem, maybe with a NOT EXISTS?
it looked to me like this got the results:
select Area,AccountNum,PolicyNum,SetID,max(LocDedAmt) As MaxLocDedAmt
FROM @DedTest
--Not exists?
group by Area,AccountNum,PolicyNum,SetID
/*
--Results
AreaAccountNumPolicyNumSetIDMaxLocDedAmt
California567561003375809152000000.00
California567561003375809165000000.00
California56756100337580921100000.00
California56756100337580922500000.00
California567561003375809240.00
*/
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 Area,AccountNum,PolicyNum,SetID,max(LocDedAmt) As MaxLocDedAmt
FROM @DedTest
group by Area,AccountNum,PolicyNum,SetID
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Try this
with src as (
select d.Area,d.AccountNum,d.PolicyNum,d.SetId,d.LocId,d.LocDedAmt,d.NonLocDedAmt,
(select cast(d2.SetId as varchar(10)) + ',' as "text()"
from @DedTest d2
where d2.Area = d.Area
and d2.AccountNum = d.AccountNum
and d2.PolicyNum = d.PolicyNum
and d2.LocId = d.LocId
order by d2.SetId
for xml path('')) as grp
from @DedTest d
)
select Area,AccountNum,PolicyNum,
char(ascii('A') - 1 + row_number() over(order by Area,AccountNum,PolicyNum)) as GroupNm,
max(LocDedAmt) as LocDedAmt, max(NonLocDedAmt) as NonLocDedAmt,
grp as GroupMember
from src
group by Area,AccountNum,PolicyNum,grp;
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
First, I find it a lot easier to read if you use the Table Value Constructor
insert into @DedTest
VALUES
('California', '56756', '1003375', 80915, 1, cast(2000000.00 as decimal(18,2)), cast(0 as decimal(18,2))),
('California', '56756', '1003375', 80915, 2, cast(2000000.00 as decimal(18,2)), cast(0 as decimal(18,2))),
('California', '56756', '1003375', 80915, 3, cast(2000000.00 as decimal(18,2)), cast(0 as decimal(18,2))),
('California', '56756', '1003375', 80915, 4, cast(2000000.00 as decimal(18,2)), cast(0 as decimal(18,2))),
('California', '56756', '1003375', 80915, 5, cast(2000000.00 as decimal(18,2)), cast(0 as decimal(18,2))),
('California', '56756', '1003375', 80916, 6, cast(5000000.00 as decimal(18,2)), cast(0 as decimal(18,2))),
('California', '56756', '1003375', 80916, 7, cast(5000000.00 as decimal(18,2)), cast(0 as decimal(18,2))),
('California', '56756', '1003375', 80921, 1, cast(100000.00 as decimal(18,2)), cast(0 as decimal(18,2))),
('California', '56756', '1003375', 80922, 2, cast(500000.00 as decimal(18,2)), cast(0 as decimal(18,2))),
('California', '56756', '1003375', 80924, 2, cast(0 as decimal(18,2)), cast(500000 as decimal(18,2))),
('California', '56756', '1003375', 80924, 3, cast(0 as decimal(18,2)), cast(500000 as decimal(18,2))),
('California', '56756', '1003375', 80924, 4, cast(0 as decimal(18,2)), cast(500000 as decimal(18,2))
)
If your descriptions of your groups are completely accurate, then you could use a simple CASE statement. (I've put it in a CROSS APPLY so that I could give it an alias).
SELECT dt.Area, dt.AccountNum, dt.PolicyNum, g.GroupNm, MAX(dt.LocDedAmt) AS LocDedAmt, MAX(dt.NonLocDedAmt) AS NonLocDedAMt
FROM @DedTest dt
CROSS APPLY(
VALUES
(CASE
WHEN dt.LocID = 1 THEN 'A'
WHEN dt.LocID = 2 THEN 'B'
WHEN dt.LocID = 3 THEN 'D'
WHEN dt.LocID = 4 THEN 'D'
WHEN dt.LocID = 5 THEN 'E'
WHEN dt.LocID = 6 THEN 'C'
WHEN dt.LocID = 7 THEN 'C'
END)
) g(GroupNm)
GROUP BY dt.Area, dt.AccountNum, dt.PolicyNum, g.GroupNm
Drew
PS: When setting up sample data, it helps to have more variety. Four of your five groups have the exact same LocDedAmt for the final result.
PPS: When providing results, it helps if they match the sample data. The entries for both LocIDs 6 and 7 have 5000000, but the desired results lists 2000000.
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply