A better way - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

A better way

Author

Topic

Magua1
Starting Member

15 Posts

Posted - 2014-07-09 : 09:41:19

The code below seems to do the job but I was wondering if there was a better or more elegent solution?

--Total up the number of SED_JSR fields that have a non-null entry.
--Where there is more than 1 entry, get the records where the value of the
--entry is duplicated in another field.
CASE WHEN CAST(ISNULL(SED_JSR1, 0) AS INT) > 0 THEN 1 ELSE 0 END +
CASE WHEN CAST(ISNULL(SED_JSR2, 0) AS INT) > 0 THEN 1 ELSE 0 END +
CASE WHEN CAST(ISNULL(SED_JSR3, 0) AS INT) > 0 THEN 1 ELSE 0 END +
CASE WHEN CAST(ISNULL(SED_JSR4, 0) AS INT) > 0 THEN 1 ELSE 0 END +
CASE WHEN CAST(ISNULL(SED_JSR5, 0) AS INT) > 0 THEN 1 ELSE 0 END +
CASE WHEN CAST(ISNULL(SED_JSR6, 0) AS INT) > 0 THEN 1 ELSE 0 END +
CASE WHEN CAST(ISNULL(SED_JSR7, 0) AS INT) > 0 THEN 1 ELSE 0 END +
CASE WHEN CAST(ISNULL(SED_JSR8, 0) AS INT) > 0 THEN 1 ELSE 0 END +
CASE WHEN CAST(ISNULL(SED_JSR9, 0) AS INT) > 0 THEN 1 ELSE 0 END > 1
AND
(SED_JSR1 IS NOT NULL AND SED_JSR1 IN(SED_JSR2,SED_JSR3,SED_JSR4,SED_JSR5,SED_JSR6,SED_JSR7,SED_JSR8,SED_JSR9))
OR
(SED_JSR2 IS NOT NULL AND SED_JSR2 IN(SED_JSR1,SED_JSR3,SED_JSR4,SED_JSR5,SED_JSR6,SED_JSR7,SED_JSR8,SED_JSR9))
OR
(SED_JSR3 IS NOT NULL AND SED_JSR3 IN(SED_JSR1,SED_JSR2,SED_JSR4,SED_JSR5,SED_JSR6,SED_JSR7,SED_JSR8,SED_JSR9))
OR
(SED_JSR4 IS NOT NULL AND SED_JSR4 IN(SED_JSR1,SED_JSR2,SED_JSR3,SED_JSR5,SED_JSR6,SED_JSR7,SED_JSR8,SED_JSR9))
OR
(SED_JSR5 IS NOT NULL AND SED_JSR5 IN(SED_JSR1,SED_JSR2,SED_JSR3,SED_JSR4,SED_JSR6,SED_JSR7,SED_JSR8,SED_JSR9))
OR
(SED_JSR6 IS NOT NULL AND SED_JSR6 IN(SED_JSR1,SED_JSR2,SED_JSR3,SED_JSR4,SED_JSR5,SED_JSR7,SED_JSR8,SED_JSR9))
OR
(SED_JSR7 IS NOT NULL AND SED_JSR7 IN(SED_JSR1,SED_JSR2,SED_JSR3,SED_JSR4,SED_JSR5,SED_JSR6,SED_JSR8,SED_JSR9))
OR
(SED_JSR8 IS NOT NULL AND SED_JSR8 IN(SED_JSR1,SED_JSR2,SED_JSR3,SED_JSR4,SED_JSR5,SED_JSR6,SED_JSR7,SED_JSR9))
OR
(SED_JSR9 IS NOT NULL AND SED_JSR9 IN(SED_JSR1,SED_JSR2,SED_JSR3,SED_JSR4,SED_JSR5,SED_JSR6,SED_JSR7,SED_JSR8))

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-07-09 : 15:41:55

[code]-- Simplified
WHERE (
CASE WHEN SED_JSR1 > 0 THEN 1 ELSE 0 END +
CASE WHEN SED_JSR2 > 0 THEN 1 ELSE 0 END +
CASE WHEN SED_JSR3 > 0 THEN 1 ELSE 0 END +
CASE WHEN SED_JSR4 > 0 THEN 1 ELSE 0 END +
CASE WHEN SED_JSR5 > 0 THEN 1 ELSE 0 END +
CASE WHEN SED_JSR6 > 0 THEN 1 ELSE 0 END +
CASE WHEN SED_JSR7 > 0 THEN 1 ELSE 0 END +
CASE WHEN SED_JSR8 > 0 THEN 1 ELSE 0 END +
CASE WHEN SED_JSR9 > 0 THEN 1 ELSE 0 END > 1
)
AND (
SED_JSR1 IN (SED_JSR2, SED_JSR3, SED_JSR4, SED_JSR5, SED_JSR6, SED_JSR7, SED_JSR8, SED_JSR9)
OR SED_JSR2 IN (SED_JSR1, SED_JSR3, SED_JSR4, SED_JSR5, SED_JSR6, SED_JSR7, SED_JSR8, SED_JSR9)
OR SED_JSR3 IN (SED_JSR1, SED_JSR2, SED_JSR4, SED_JSR5, SED_JSR6, SED_JSR7, SED_JSR8, SED_JSR9)
OR SED_JSR4 IN (SED_JSR1, SED_JSR2, SED_JSR3, SED_JSR5, SED_JSR6, SED_JSR7, SED_JSR8, SED_JSR9)
OR SED_JSR5 IN (SED_JSR1, SED_JSR2, SED_JSR3, SED_JSR4, SED_JSR6, SED_JSR7, SED_JSR8, SED_JSR9)
OR SED_JSR6 IN (SED_JSR1, SED_JSR2, SED_JSR3, SED_JSR4, SED_JSR5, SED_JSR7, SED_JSR8, SED_JSR9)
OR SED_JSR7 IN (SED_JSR1, SED_JSR2, SED_JSR3, SED_JSR4, SED_JSR5, SED_JSR6, SED_JSR8, SED_JSR9)
OR SED_JSR8 IN (SED_JSR1, SED_JSR2, SED_JSR3, SED_JSR4, SED_JSR5, SED_JSR6, SED_JSR7, SED_JSR9)
OR SED_JSR9 IN (SED_JSR1, SED_JSR2, SED_JSR3, SED_JSR4, SED_JSR5, SED_JSR6, SED_JSR7, SED_JSR8)
)[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources