Override Table - 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

SQL Server 2008 Forums

Transact-SQL (2008)

Override Table

Author

Topic

KalebD
Starting Member

4 Posts

Posted - 2015-01-12 : 15:56:43

Hello,

I'm building an application that calculates risk scores based on a number of variables. One of those variables is a disease hierarchy (HCC's) and if one or more different hierarchies are present then some should be dropped. For example,

If HCC 5 is present then drop HCC 112,
if HCC 7 is present then drop all HCC's 8, 9, or 10
if HCC 8 is present then drop all HCC's 9 or 10

How can I accomplish this in SQL, not sure how I would structure the "Override" table. Thanks for any help!

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-01-12 : 16:03:03

Kinda unclear. What do you mean by "drop"? What happens when a hierarchy is dropped? Is a table dropped in Sql Server? Are rows deleted? Something else?

KalebD
Starting Member

4 Posts

Posted - 2015-01-12 : 16:03:50

By drop I mean to exclude from the data set. None of the data is modified.

KalebD
Starting Member

4 Posts

Posted - 2015-01-12 : 16:07:22

Here is a (hopefully) better example...
My query would return

HCC || Diagnosis
108 || 496
69 || 742
7 || 197
7 || 198
8 || 162.4
8 || 162.9

Since HCC 7 is present, then HCC's 8, 9, and 10 should be excluded from the results. I was thinking I could build a table to hold the exclusion values and write a query to exclude records that match those values. The reason I say that is because the exclusion values will change every year.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-01-12 : 16:32:23

basically:


select hcc, diagnosis
from hcc_diagnosis_table
where hcc in (8, 9, 10) 
  and not exists (select 1 from hcc_diagnosis_table where hcc = 7)
   OR hcc not in (8,9,10)

That's a basic idea. OTOH if you had a table that had columns:

hcc hcc_to_exclude
7 8
7 9
7 10

you could write (untested, but food for thought)


select hcc, diagnosis
from hcc_diagnosis_table h
left join hcc_exclusion_table x1 on h.hcc = x1.hcc
left join hcc_exclusion_table x2 on h.hcc = x2.hcc_to_exclude and x1.hcc = x2.hcc
where x1.hcc is null
   or x2.hcc_to_exclude is null

KalebD
Starting Member

4 Posts

Posted - 2015-01-12 : 17:57:11

GBRITTON I think that's a good start and addresses a single exclusion, but what are your thoughts on having multiple exlusions?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources