Hi guys,
I have 2 tables which are connected to each other by casenumber:
CodeTable
CaseNumber OldCode
001 05
002 05
003 05
004 05
005 06
ConnectionTable
CaseNumber ConnectionType
001 G
001 H
001 N
002 M
002 H
003 G
003 H
003 I
003 N
003 N
004 X
004 N
--
With following Mapping Condition:
OldCode NewCode
05 (with connectionType G) GE
05 (with ConnectionType H) GP
05 (With ConnectionType I) GPE
05 (With ConnectionType G OR H) GPE
--
With the following Rules:
1. Search for connection type that is mapped and disregard all other connection types.
2. If none of the mapped types are connected to the case, map as follows : 05 to GPE
3. If multiple types are connected to the case, map as follows: 05 to GPE.
I would like to map the old code to the new code on the CodeTable without getting duplicate CaseNumber.
My code:
SELECT DISTINCT c1.CaseNumber,
CASE WHEN c1.OldCode = '05' THEN
CASE c2.ConnectionType WHEN 'G' THEN 'GE'
WHEN 'H' THEN 'GP'
WHEN 'I' THEN 'GPE'
ELSE 'GPE' END END AS NewCode,
FROM CodeTable AS c1
LEFT JOIN(SELECT MIN(ConnectionType) [ConnectionType],CaseNumber
FROM ConnectionTable GROUP BY CaseNumber) AS c2
ON (c1.CaseNumber = c2.CaseNumber)
I did not get all answer correctly because let say i have Casenumber 001 with connection G, H, and N; as given in the rule, I need to map the multiple types to GPE, but what i get is GE (which is OldCode 'G'). This is probably because i select MIN from connectionType and the first one i get is G, that's why i get GE for the new code instead of GPE.
I hope you guys will help me on this. Thanks so much!!!
Jul.
If I understand your requirements correctly, perhaps something like this:
Code Snippet
SET NOCOUNT ON
DECLARE @.Codes table
( CaseNumber varchar(10),
OldCode varchar(5)
)
INSERT INTO @.Codes VALUES ( '001', '05' )
INSERT INTO @.Codes VALUES ( '002', '05' )
INSERT INTO @.Codes VALUES ( '003', '05' )
INSERT INTO @.Codes VALUES ( '004', '05' )
INSERT INTO @.Codes VALUES ( '005', '05' )
INSERT INTO @.Codes VALUES ( '006', '05' )
INSERT INTO @.Codes VALUES ( '007', '05' )
INSERT INTO @.Codes VALUES ( '008', '06' )
INSERT INTO @.Codes VALUES ( '009', '06' )
DECLARE @.Connections table
( CaseNumber varchar(10),
ConnectionType varchar(5)
)
INSERT INTO @.Connections VALUES ( '001', 'G' )
INSERT INTO @.Connections VALUES ( '001', 'H' )
INSERT INTO @.Connections VALUES ( '001', 'N' )
INSERT INTO @.Connections VALUES ( '002', 'M' )
INSERT INTO @.Connections VALUES ( '003', 'G' )
INSERT INTO @.Connections VALUES ( '003', 'H' )
INSERT INTO @.Connections VALUES ( '003', 'I' )
INSERT INTO @.Connections VALUES ( '003', 'N' )
INSERT INTO @.Connections VALUES ( '003', 'N' )
INSERT INTO @.Connections VALUES ( '004', 'G' )
INSERT INTO @.Connections VALUES ( '005', 'H' )
INSERT INTO @.Connections VALUES ( '006', 'I' )
INSERT INTO @.Connections VALUES ( '007', 'G' )
INSERT INTO @.Connections VALUES ( '008', 'H' )
SELECT DISTINCT
c1.CaseNumber,
c1.OldCode,
NewCode = CASE
WHEN ( c1.OldCode = '05' ) THEN
CASE
WHEN ( dt.CaseCount = 1 ) THEN
CASE WHEN ( c2.ConnectionType = 'G' ) THEN 'GE'
WHEN ( c2.ConnectionType = 'H' ) THEN 'GP'
ELSE 'GPE'
END
WHEN ( dt.CaseCount <> 1 ) OR ( dt.CaseNumber IS NULL ) THEN 'GPE'
END
ELSE 'GPE'
END
FROM @.Codes c1
LEFT JOIN (SELECT
CaseNumber,
CaseCount = count( CaseNumber )
FROM @.Connections
GROUP BY CaseNumber
) dt
ON c1.CaseNumber = dt.CaseNumber
LEFT JOIN @.Connections c2
ON c1.CaseNumber = c2.CaseNumber
CaseNumber OldCode NewCode
- - -
001 05 GPE
002 05 GPE
003 05 GPE
004 05 GE
005 05 GP
006 05 GPE
007 05 GE
008 06 GPE
009 06 GPE
Thanks Arnie,
CaseNumber ConnectionType
001 G
001 H
001 N
002 M
002 H
003 G
003 H
003 I
003 N
003 N
004 X
004 N
The appropriate result is supposed to be:
CaseNumber NewCode
001 GPE (Because there are multiple types refer rul 3)
002 GP (Because H is in there, and ignore M because M is not
in one of the mapping criteria of code 05) [refer to rule 1.)
003 GPE (Because there are multiple types refer rule 3)
004 GPE (Because none of the mapped types are connected to the case -- refer rule 2)
Can you help me on this please? thanks........
|||Hi Jul
Hope this suits your every requirement, I have done few modification to the code Arnie. Assuming that u require something similar like this.
Regards
Vijai K
SET NOCOUNT ON
Declare @.final table(
CaseNumber varchar(10),
NewCode varchar(5)
);
DECLARE @.Codes table(
CaseNumber varchar(10),
OldCode varchar(5)
)
INSERT INTO @.Codes VALUES ( '001', '05' )
INSERT INTO @.Codes VALUES ( '002', '05' )
INSERT INTO @.Codes VALUES ( '003', '05' )
INSERT INTO @.Codes VALUES ( '004', '05' )
INSERT INTO @.Codes VALUES ( '005', '05' )
INSERT INTO @.Codes VALUES ( '006', '05' )
INSERT INTO @.Codes VALUES ( '007', '05' )
INSERT INTO @.Codes VALUES ( '008', '06' )
INSERT INTO @.Codes VALUES ( '009', '06' )
INSERT INTO @.Codes VALUES ( '010', '05' )
DECLARE @.Connections table(
CaseNumber varchar(10),
ConnectionType varchar(5)
)
INSERT INTO @.Connections VALUES ( '001', 'G' )
INSERT INTO @.Connections VALUES ( '001', 'H' )
INSERT INTO @.Connections VALUES ( '001', 'N' )
INSERT INTO @.Connections VALUES ( '002', 'M' )
INSERT INTO @.Connections VALUES ( '002', 'H' )
INSERT INTO @.Connections VALUES ( '003', 'G' )
INSERT INTO @.Connections VALUES ( '003', 'H' )
INSERT INTO @.Connections VALUES ( '003', 'I' )
INSERT INTO @.Connections VALUES ( '003', 'N' )
INSERT INTO @.Connections VALUES ( '003', 'N' )
INSERT INTO @.Connections VALUES ( '004', 'X' )
INSERT INTO @.Connections VALUES ( '004', 'N' )
INSERT INTO @.Connections VALUES ( '005', 'H' )
INSERT INTO @.Connections VALUES ( '005', 'X' )
INSERT INTO @.Connections VALUES ( '005', 'N' )
INSERT INTO @.Connections VALUES ( '006', 'I' )
INSERT INTO @.Connections VALUES ( '007', 'G' )
INSERT INTO @.Connections VALUES ( '007', 'N' )
INSERT INTO @.Connections VALUES ( '008', 'H' )
INSERT INTO @.Connections VALUES ( '009', 'H' )
INSERT INTO @.Connections VALUES ( '010', 'G' )
INSERT INTO @.Connections VALUES ( '010', 'X' )
insert into @.final
SELECT DISTINCT
c1.CaseNumber,
NewCode = CASE
WHEN ( c1.OldCode = '05' ) THEN
CASE
WHEN ( dt.CaseCount = 1 ) OR ( dt.CaseCount = 2 ) THEN
CASE WHEN ( c2.ConnectionType = 'G' ) THEN 'GE'
WHEN ( c2.ConnectionType = 'H' ) THEN 'GP'
ELSE 'GPE'
END
WHEN ( dt.CaseCount > 2 ) OR ( dt.CaseNumber IS NULL ) THEN 'GPE'
END
ELSE 'GPE'
END
FROM @.Codes c1
LEFT JOIN (SELECT CaseNumber, CaseCount = count(CaseNumber)
FROM @.Connections
GROUP BY CaseNumber) dt
ON c1.CaseNumber = dt.CaseNumber
LEFT JOIN @.Connections c2
ON c1.CaseNumber = c2.CaseNumber
delete from @.final where Newcode = 'GPE' and casenumber in( select Casenumber from @.final group by casenumber having count(*) >1 )
select * from @.final
|||Here's a guess. It's probably not quite there yet, but it may give you some ideas.
Code Snippet
DECLARE @.Codes table
( CaseNumber varchar(10),
OldCode varchar(5)
)
INSERT INTO @.Codes VALUES ( '001', '05' )
INSERT INTO @.Codes VALUES ( '002', '05' )
INSERT INTO @.Codes VALUES ( '003', '05' )
INSERT INTO @.Codes VALUES ( '004', '05' )
INSERT INTO @.Codes VALUES ( '005', '05' )
INSERT INTO @.Codes VALUES ( '006', '05' )
INSERT INTO @.Codes VALUES ( '007', '05' )
INSERT INTO @.Codes VALUES ( '008', '06' )
INSERT INTO @.Codes VALUES ( '009', '06' )
DECLARE @.Connections table
( CaseNumber varchar(10),
ConnectionType varchar(5)
)
INSERT INTO @.Connections VALUES ( '001', 'G' )
INSERT INTO @.Connections VALUES ( '001', 'H' )
INSERT INTO @.Connections VALUES ( '001', 'N' )
INSERT INTO @.Connections VALUES ( '002', 'M' )
INSERT INTO @.Connections VALUES ( '003', 'G' )
INSERT INTO @.Connections VALUES ( '003', 'H' )
INSERT INTO @.Connections VALUES ( '003', 'I' )
INSERT INTO @.Connections VALUES ( '003', 'N' )
INSERT INTO @.Connections VALUES ( '003', 'N' )
INSERT INTO @.Connections VALUES ( '004', 'G' )
INSERT INTO @.Connections VALUES ( '005', 'H' )
INSERT INTO @.Connections VALUES ( '006', 'I' )
INSERT INTO @.Connections VALUES ( '007', 'G' )
INSERT INTO @.Connections VALUES ( '008', 'H' )
DECLARE @.MappedTypes table (
ID int,
OldCode varchar(5),
mappedType varchar(5)
)
INSERT INTO @.MappedTypes VALUES (0, '05', 'G')
INSERT INTO @.MappedTypes VALUES (1, '05', 'H')
INSERT INTO @.MappedTypes VALUES (2, '05', 'I')
DECLARE @.Mapping table (
OldCode varchar(5),
Connections varchar(10),
Bitmask varbinary(16),
newCode varchar(5)
)
INSERT INTO @.Mapping VALUES ( '05', 'G', 0x1, 'GE')
INSERT INTO @.Mapping VALUES ( '05', 'H', 0x2, 'GP')
INSERT INTO @.Mapping VALUES ( '05', 'I', 0x4, 'GPE')
INSERT INTO @.Mapping VALUES ( '05', NULL, NULL, 'GPE');
-- last row indicates code to use when there is some
-- mapped value present, but when the particular collection
-- of mapped values does not have a specific mapping.
with B(CaseNumber, Bitmask, OldCode) as (
select
C.CaseNumber,
sum(distinct coalesce(power(2,ID),0)) as Bitmask,
D.OldCode
from @.Connections as C
join @.Codes as D
on D.CaseNumber = C.CaseNumber
join @.MappedTypes AS M
on C.ConnectionType = M.mappedType
and D.OldCode = M.OldCode
group by C.CaseNumber, D.OldCode
)
select
CaseNumber,
coalesce(newCode,(select newCode from @.Mapping where Bitmask is null))
from B
left outer join @.Mapping as M
on M.OldCode = B.OldCode
and M.Bitmask = B.Bitmask
Steve Kass
Drew University
http://www.stevekass.com
|||
Jul,
I looked at this problem again, and came to the conclusion that using a Mapping table would be useful. I think that this satisfies your rules and matches your expected output.
Code Snippet
SET NOCOUNT ON
DECLARE @.Codes table
( CaseNumber varchar(10),
OldCode varchar(5)
)
INSERT INTO @.Codes VALUES ( '001', '05' )
INSERT INTO @.Codes VALUES ( '002', '05' )
INSERT INTO @.Codes VALUES ( '003', '05' )
INSERT INTO @.Codes VALUES ( '004', '05' )
INSERT INTO @.Codes VALUES ( '005', '06' )
DECLARE @.Connections table
( CaseNumber varchar(10),
ConnectionType varchar(5)
)
INSERT INTO @.Connections VALUES ( '001', 'G' )
INSERT INTO @.Connections VALUES ( '001', 'H' )
INSERT INTO @.Connections VALUES ( '001', 'N' )
INSERT INTO @.Connections VALUES ( '002', 'H' )
INSERT INTO @.Connections VALUES ( '002', 'M' )
INSERT INTO @.Connections VALUES ( '003', 'G' )
INSERT INTO @.Connections VALUES ( '003', 'H' )
INSERT INTO @.Connections VALUES ( '003', 'I' )
INSERT INTO @.Connections VALUES ( '003', 'N' )
INSERT INTO @.Connections VALUES ( '003', 'N' )
INSERT INTO @.Connections VALUES ( '004', 'N' )
INSERT INTO @.Connections VALUES ( '004', 'X' )
DECLARE @.CodeMap table
( OldCode char(2),
OldType char(1),
NewCode varchar(3)
)
INSERT INTO @.CodeMap VALUES ( '05', 'G', 'GE' )
INSERT INTO @.CodeMap VALUES ( '05', 'H', 'GP' )
SELECT
c.CaseNumber,
NewCode = CASE
WHEN count( m.NewCode ) = 1 AND min( m.NewCode ) IS NOT NULL THEN min( m.NewCode )
ELSE 'GPE'
END
FROM @.Codes c
LEFT JOIN @.Connections c2
ON c.CaseNumber = c2.CaseNumber
LEFT JOIN @.CodeMap m
ON c2.ConnectionType = m.OldType
GROUP BY c.CaseNumber
CaseNumber NewCode
- -
001 GPE
002 GP
003 GPE
004 GPE
005 GPE
The following Code BY Arnie is the most appropriate answer. Thank's guys!
SET NOCOUNT ON
DECLARE @.Codes table
( CaseNumber varchar(10),
OldCode varchar(5)
)
INSERT INTO @.Codes VALUES ( '001', '05' )
INSERT INTO @.Codes VALUES ( '002', '05' )
INSERT INTO @.Codes VALUES ( '003', '05' )
INSERT INTO @.Codes VALUES ( '004', '05' )
INSERT INTO @.Codes VALUES ( '005', '06' )
DECLARE @.Connections table
( CaseNumber varchar(10),
ConnectionType varchar(5)
)
INSERT INTO @.Connections VALUES ( '001', 'G' )
INSERT INTO @.Connections VALUES ( '001', 'H' )
INSERT INTO @.Connections VALUES ( '001', 'N' )
INSERT INTO @.Connections VALUES ( '002', 'H' )
INSERT INTO @.Connections VALUES ( '002', 'M' )
INSERT INTO @.Connections VALUES ( '003', 'G' )
INSERT INTO @.Connections VALUES ( '003', 'H' )
INSERT INTO @.Connections VALUES ( '003', 'I' )
INSERT INTO @.Connections VALUES ( '003', 'N' )
INSERT INTO @.Connections VALUES ( '003', 'N' )
INSERT INTO @.Connections VALUES ( '004', 'N' )
INSERT INTO @.Connections VALUES ( '004', 'X' )
DECLARE @.CodeMap table
( OldCode char(2),
OldType char(1),
NewCode varchar(3)
)
INSERT INTO @.CodeMap VALUES ( '05', 'G', 'GE' )
INSERT INTO @.CodeMap VALUES ( '05', 'H', 'GP' )
SELECT
c.CaseNumber,
NewCode = CASE
WHEN count( m.NewCode ) = 1 AND min( m.NewCode ) IS NOT NULL THEN min( m.NewCode )
ELSE 'GPE'
END
FROM @.Codes c
LEFT JOIN @.Connections c2
ON c.CaseNumber = c2.CaseNumber
LEFT JOIN @.CodeMap m
ON c2.ConnectionType = m.OldType
GROUP BY c.CaseNumber
CaseNumber NewCode
- -
001 GPE
002 GP
003 GPE
004 GPE
005 GPE
Arnie, seems like it does not work if instead of having
INSERT INTO @.Connections VALUES ( '001', 'G' )
INSERT INTO @.Connections VALUES ( '001', 'H' )
INSERT INTO @.Connections VALUES ( '001', 'N' )
but i'm having these values:
INSERT INTO @.Connections VALUES ( '001', 'H' )
INSERT INTO @.Connections VALUES ( '001', 'H' )
INSERT INTO @.Connections VALUES ( '001', 'N' )
On the rules given, if I have H (without G or I) then map to 'GP'
If i have multiple matched connections let say 'H' AND 'G' (or 'H' AND 'I') then map to 'GPE'.
But i have a case where the connection in the case is like the values above, which is H, H, and N. When i tried your code, it gives me 'GPE', what i need is 'GE'. Everything else works perfect. I am really a beginner in sql, hope you can help. thanks.
|||Of course, I overlooked that possiblitilty. Thanks for bringing it to my attention.
If you add 'DISTINCT' to the count( m.NewCode ) it 'should' take care of that situation.
Code Snippet
NewCode = CASEWHEN count( DISTINCT m.NewCode ) = 1 AND min( m.NewCode ) IS NOT NULL THEN min( m.NewCode )
And this 'should' handle any additional 'OldCodes' just by inserting rows into the Mapping table. (Unless your rules change substaintially.)
|||Awesome........Thanks so much!!!!!!!!
Julia
|||A tip 'o the hat to Steve Kass for nudging me to consider a mapping table...
No comments:
Post a Comment