Excluding in a sql query - 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 2000 Forums

Transact-SQL (2000)

Excluding in a sql query

Author

Topic

nbs
Starting Member

22 Posts

Posted - 2007-01-02 : 15:07:20

I am trying to accomplish the following.

There are two tables. the second one is a table that contains fields based on which the exclusion logic has to be written. It has 5 fields and there may be data in all of them or only in some of them.

The first and second table have one field in common - the Account number.(ACCT_NUM)
Also the second table is in a different database

the exclusion is based in steps..

1) if it has all the fields in the second table for that account number, then compare all of them..
2) if only 4 of them are present,compare and check for null for the rest
3) if only 3 of them are present,compare and check for null for the rest
4) if only 2 of them are present,compare and check for null for the rest.

how do i do this

thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-02 : 17:34:19

Dupes!
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76999
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76998

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-02 : 17:51:26

Do you have some sample data?
When comparing the two columns, does it matter which columns in table1 to be searched?
If two columns are present, does it matter which order the hits are matched? Do both columns has to be present? Or ony one of them? What it the two columns has same value (for example 25), does value 25 need to be present only one time or two times in table1 and same record?

Peter Larsson
Helsingborg, Sweden

nbs
Starting Member

22 Posts

Posted - 2007-01-02 : 18:23:09

I dont think I explained it quite right..I have a pic attached, might make it a little more understandable

I have a table from database1, which has policy numbers and a lot of other fields using some filters. ( i mean some conditions in where clause) Now I have this exclusion table in another database which has 5 fields for each policy number. Based on their occurrence I need to exclude the policy numbers in the main query which satisfy the criteria..

eg.. in the exclusion table i have

acct num 9002924 suffx 02 section 00 year 20041 date 2004-12-10

so i need to exclude when all these are satisfied..so rows 42-46 should not appear in the result.
But this exclusion has to be done in a sequence
first compare all the fields. if not then comapre the first four fields. if not then compare the first three fields and if not the first two.
so if i just have in one case
acct num 9005412 suffx 02
then it should step down through all the process and reach the step where only comparison of two fields is done..

hope this provides more clarity.

I have a 5 not exists( ) statements written in the where clause for each comparison, but it does not work right

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-02 : 18:25:34

Post your non-working query here and we will take a look at it.

Peter Larsson
Helsingborg, Sweden

nbs
Starting Member

22 Posts

Posted - 2007-01-02 : 18:40:45

Select c.SrcSys_CDE,c.ClaimLine_CDE,c.Claim_NBR, c.Policy_NBR, c.Assign_DTM,c.Accnt_Full_NBR,l.LossRpt_DTE,c.AccntSufx_NBR,
c.PolicySectn_NBR,c.PolicyYear_CDE,left(c.PolicyYear_CDE,4)

From Claim c inner join loss l

Where
c.SrcSys_CDE='C'
and c.ClaimLine_CDE='C'
and c.Accnt_Full_NBR = '9002924'
AND
(not exists (Select 1 From table2.database2 k Where k.POL_ACCT = c.Accnt_Full_NBR And k.POL_SUFX = c.AccntSufx_NBR And k.POL_SECT= c.PolicySectn_NBR And k.POL_YEAR =left(c.PolicyYear_CDE,4) And k.PERM_EXCLUSN = 'Y')

or not exists (Select 1 From table2.database2 k Where k.POL_ACCT = c.Accnt_Full_NBR And k.POL_SUFX = c.AccntSufx_NBR And k.POL_SECT= c.PolicySectn_NBR And k.POL_YEAR =left(c.PolicyYear_CDE,4) And k.PERM_EXCLUSN = 'N' and k.EXC_END_DATE > l.LossRpt_DTE)

or not exists (Select 1 From table2.database2 k Where k.POL_ACCT = c.Accnt_Full_NBR And k.POL_SUFX = c.AccntSufx_NBR And k.POL_SECT= c.PolicySectn_NBR And k.POL_YEAR =left(c.PolicyYear_CDE,4) And k.PERM_EXCLUSN = 'N' and k.EXC_END_DATE is null)

or not exists (Select 1 From table2.database2 k Where k.POL_ACCT = c.Accnt_Full_NBR And k.POL_SUFX = c.AccntSufx_NBR And k.POL_SECT= c.PolicySectn_NBR And k.POL_YEAR =left(c.PolicyYear_CDE,4))

or not exists (Select 1 From table2.database2 k Where k.POL_ACCT = c.Accnt_Full_NBR And k.POL_SUFX = c.AccntSufx_NBR And k.POL_SECT= c.PolicySectn_NBR)

or not exists (Select 1 From table2.database2 k Where k.POL_ACCT = c.Accnt_Full_NBR And k.POL_SUFX = c.AccntSufx_NBR)
)
) order by c.Accnt_Full_NBR

nbs
Starting Member

22 Posts

Posted - 2007-01-04 : 13:00:40

Can I accomplish this using a searched case in the where clause?

any ideas?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-08 : 18:46:52

You better post some sample data, and your expected output based on the provided sample data.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-08 : 18:48:06

Please read all answers if you want your problem solved.

Peter Larsson
Helsingborg, Sweden