Wednesday, March 28, 2012

If Else Condition

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 = CASE
WHEN 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!!!!!!!!

Wink

Julia

|||A tip 'o the hat to Steve Kass for nudging me to consider a mapping table...

No comments:

Post a Comment