SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Structure of WHERE clause using "and" / "or" in a multiple criteria query


Structure of WHERE clause using "and" / "or" in a multiple criteria query

Author
Message
LoosinMaMind
LoosinMaMind
Say Hey Kid
Say Hey Kid (664 reputation)Say Hey Kid (664 reputation)Say Hey Kid (664 reputation)Say Hey Kid (664 reputation)Say Hey Kid (664 reputation)Say Hey Kid (664 reputation)Say Hey Kid (664 reputation)Say Hey Kid (664 reputation)

Group: General Forum Members
Points: 664 Visits: 565
Hi

I have a set of data (example script below) and i have to exclude certain rows based on the rules I have been supplied with. Some of the rules apply to all of the dataset based on a single field. (that bit i can do :-) )

However within the where clause i need to be able to add further criteria to exclude more and more comlpex query rules.

iI have included a script below with some sample data which i think covers all the rules i have been given at present.

Some of these "exclusion" rules apply from a single field, where as others look at a combination of fields to determine wether or not the row is excluded.

These are the rules I have. (i need all data except based on the following inclusion/exclusion rules)

1) Exclude all rows where apptype = 'PPNEW' or 'PPREV'
2) Exclude all rows with appdate_dte not in Dec 2012.
3) Only Include rows where appstatus in ('A','B','DIS','DI','EXT','H','L','S')
4) Only Include rows where Flag = 'OP'
5) Exclude all rows where clinic in 'DHEMIWCHP'
6) Exclude all rows where clinic_spec in ('DIET','MSC')
7) Exclude all rows where clinic_spec in ( 'ANAES','PAEDS') and Consultant IN ( 'MDT','GABHOS')
8) Exclude all rows where clinic in ('ALLSAEWCH','PLATECWCH') and appsource IN ( 'A&E','A&EAT','SELF',A&ECI').

i understand that i am going to have to use "and" & "or"'s within the where clause but unsure of the structure


I have added a "expected_in_results" column (y/N) to the sample data and a exclusion description to help. (last 2 columns)

To see the expected results select only those rows with Yes in the "expected_in_results" column.

Is there by any chance, a way of using exclusion refernce tables and building them into the query so as more rules are applied it would simply be a case of updating (or adding) the exclusion codes to the ref tables. How would this work when comparing more than one field to deterine a valid exclusion?

Thanks in advance for any help given.


sample data script (results script below)

--------------------------------------------------------------------------------------------------

CREATE TABLE PS_TestForOnline
(
ROW_NO nvarchar (20),
clinic VARCHAR (20),
appdate_dte datetime ,
--rt_date date NULL,

CLINIC_SPEC VARCHAR (20), consultant VARCHAR (50), apptype VARCHAR (50), appstatus VARCHAR (50),
appsource VARCHAR (50), FLAG VARCHAR (50), expected_in_results VARCHAR (50),
RULE_DESC VARCHAR (50),

)



INSERT INTO PS_TestForOnline
VALUES( '1' ,'MFYAGCIC','2012-11-14','OPHTH','MF','REV','CNA','CONS','OP','NO','DATE RANGE EXCLUSION')
INSERT INTO PS_TestForOnline
VALUES( '2' , 'BAZKACIC' , '2012-12-12' , 'DERM' , 'BAZKA' , 'REV' , 'X' , 'CONS' , 'OP' , 'NO' , 'APPSTATUS OUTSIDE RANGE' )
INSERT INTO PS_TestForOnline
VALUES( '3' , 'JJNRTHY' , '2012-12-28' , 'RTHER' , 'JJN' , 'REV' , 'L' , 'CONS' , 'OP' , 'YES' , 'NO RULE - SHOULD BE INCLUDED IN RESULTS' )
INSERT INTO PS_TestForOnline
VALUES( '4' , 'FLHCRCIC' , '2012-12-11' , 'COLORL' , 'FLH' , 'PPNEW ' , 'DIS' , 'CONS' , 'OP' , 'NO' , 'APPTYPE <> PPNEW or PPREV' )
INSERT INTO PS_TestForOnline
VALUES( '5' , 'JJNRTHY' , '2012-12-07' , 'RTHER' , 'JJN' , 'PPREV' , 'L' , 'CONS' , 'OP' , 'NO' , 'APPTYPE <> PPNEW or PPREV' )
INSERT INTO PS_TestForOnline
VALUES( '6' , 'DHEMIWCHP' , '2012-12-03' , 'OPHTHS' , 'MF', 'REV' , 'L' , 'CONS' , 'OP' , 'NO' , 'GLOBAL CLINIC EXCLUSION' )
INSERT INTO PS_TestForOnline
VALUES( '7' , 'MHCARDIO' , '2012-12-20' , 'CARDIO' , 'HAMM' , 'RAPAC' , 'L' , 'GP' , 'WA' , 'NO' , 'GLOBAL FLAG <> OP EXCLUSION' )
INSERT INTO PS_TestForOnline
VALUES( '8' , 'ERONUROL' , '2012-12-11' , 'DIET' , 'UMEON' , 'REV' , 'CC' , 'CONS' , 'OP' , 'NO' , 'GLOBAL CLINIC SPEC EXCLUSION' )
INSERT INTO PS_TestForOnline
VALUES( '9' , 'DHEMIWCHP' , '2012-12-14' , 'MED' , 'HASS' , 'REV' , 'L' , 'CONS' , 'OP' , 'NO' , 'GLOBAL CLINIC EXCLUSION' )
INSERT INTO PS_TestForOnline
VALUES( '10' ,'HAEME' , '2012-12-07' , 'MSC' , 'HO' , 'REV' , 'CP' , 'GP' , 'OP' , 'NO' , 'GLOBAL CLINIC SPEC EXCLUSION' )
INSERT INTO PS_TestForOnline
VALUES( '11' , 'FERGRWH' , '2012-12-20' , 'ANAES' , 'FERG' , 'REV' , 'CC' , 'CONS' , 'OP' , 'YES' , 'NO RULE - SHOULD BE INCLUDED IN RESULTS' )
INSERT INTO PS_TestForOnline
VALUES( '12' , 'HAEME' , '2012-12-14' , 'ANAES' , 'MDT' , 'REV' , 'L' , 'GP ' , 'OP' , 'NO' , 'CLINIC_SPEC CONSULTANT CONBINATION EXCLUSION' )
INSERT INTO PS_TestForOnline
VALUES( '13' , 'JENSRCIC' , '2012-12-14' , 'PAEDS' , 'GABHOS' , 'REV' , 'L' , 'CONS' , 'OP' , 'NO' , 'CLINIC_SPEC CONSULTANT CONBINATION EXCLUSION' )
INSERT INTO PS_TestForOnline
VALUES( '14' , 'ALLSAEWCH' , '2012-12-20' , 'RENA_N' , 'FERG' , 'REV' , 'L' , 'A&E' , 'OP' , 'NO' , 'CLINIC APPSOURCE COMBINATION EXCLUSION' )
INSERT INTO PS_TestForOnline
VALUES( '15' , 'ALLSAEWCH' , '2012-12-04' , 'ELDC' , 'JG' , 'REV' , 'CNA' , 'SELF' , 'OP' , 'NO' , 'CLINIC APPSOURCE COMBINATION EXCLUSION' )
INSERT INTO PS_TestForOnline
VALUES( '16' , 'ALLSAEWCH' , '2012-12-20' , 'ORAL' , 'XXX' , 'REV' , 'L' , 'CONS' , 'OP' , 'YES' , 'NO RULE - SHOULD BE INCLUDED IN RESULTS' )
INSERT INTO PS_TestForOnline
VALUES( '17' , 'STUWDACIC' , '2012-10-14' , 'RHEU_N' , 'STUW' , 'REV' , 'DNA' , 'CONS' , 'OP' , 'NO' , 'DATE RANGE EXCLUSION')
INSERT INTO PS_TestForOnline
VALUES( '18' , 'AINGSC' , '2012-12-18' , 'OPHTH' , 'AING' , 'REV' , 'L' , 'CONS' , 'OP' , 'YES' , 'NO RULE - SHOULD BE INCLUDED IN RESULTS' )
INSERT INTO PS_TestForOnline
VALUES( '19' , 'DNDAVS' , '2012-12-03' , 'OPHTH' , 'DND' , 'REV' , 'L' , 'CONS' , 'OP' , 'YES' , 'NO RULE - SHOULD BE INCLUDED IN RESULTS' )
INSERT INTO PS_TestForOnline
VALUES ('20' , 'REVBCCIC' , '2012-12-03' , 'BRSURG' , 'MW' , 'REV' , 'DNA' , 'CONS' , 'OP' , 'YES' , 'NO RULE - SHOULD BE INCLUDED IN RESULTS' )

--data i have as an example
select * from PS_TestForOnline

--------------------------------------------------------------------------------------------------

results expected / rows left after exclusions

------------------------------------
select * from PS_TestForOnline

where expected_in_results = 'Yes'

-------------------------------

many thanks again
steve 50602
steve 50602
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 Visits: 7
So which ones are you stuck on? Just 7 and 8?

I don't see why you need to use OR, just AND...

Surely something like:

SELECT * from table
WHERE NOT (clinic_spec IN ( 'ANAES','PAEDS') AND Consultant IN ( 'MDT','GABHOS'))

would work?
LoosinMaMind
LoosinMaMind
Say Hey Kid
Say Hey Kid (664 reputation)Say Hey Kid (664 reputation)Say Hey Kid (664 reputation)Say Hey Kid (664 reputation)Say Hey Kid (664 reputation)Say Hey Kid (664 reputation)Say Hey Kid (664 reputation)Say Hey Kid (664 reputation)

Group: General Forum Members
Points: 664 Visits: 565
Thanks for your reply.

i was unaware of the "WHERE NOT....." aspect avaiable and will give it a go.

Let you know how i get on.

thanks
kl25
kl25
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1180 Visits: 1875
Yes, you can use reference table to manage the additions of criteria over time. I use reference tables fairly frequently to handle the type of situation you're describing. I haven't addressed all of your criteria but here's how you might approach the query for the first three criteria in your list.

As new exclusions or inclusions are added, you can add the value to the corresponding reference table. If the inclusions or exclusions change over time and it's important to track the history, effective begin and end dates can be used in the reference table and the Where clause as needed.


Create table rt_apptype_ex
(
apptype varchar(6) primary key clustered
)

Insert rt_apptype_ex (apptype)
Values ('ppnew'), ('pprev')


Create table rt_appstatus_in
(
appstatus varchar(4) primary key clustered
)

Insert rt_appstatus_in (appstatus)
Values ('A'),('B'),('DIS'),('DI'),('EXT'),('H'),('L'),('S')

Select ps.*
From
PS_TestForOnline ps
--Excluded: Left outer join with corresponding 'is null' in Where clause
Left outer join
rt_apptype_ex rae
On
ps.apptype = rae.apptype
--Included: Inner join
Inner join
rt_appstatus_in rai
On
ps.appstatus = rai.appstatus
Where
rae.apptype is null and
--Manage date ranges in Where. If there are a lot of date exclusions,
--consider using a calendar table with an exclude column, particularly
--if the dates are structured like appdate_dte with a zero time component.
appdate_dte < '2012-12-01' and
appdate_dte > '2012-12-31'
;



Edit: Update structure of reference table for simplicity. Add additional columns as needed.
LoosinMaMind
LoosinMaMind
Say Hey Kid
Say Hey Kid (664 reputation)Say Hey Kid (664 reputation)Say Hey Kid (664 reputation)Say Hey Kid (664 reputation)Say Hey Kid (664 reputation)Say Hey Kid (664 reputation)Say Hey Kid (664 reputation)Say Hey Kid (664 reputation)

Group: General Forum Members
Points: 664 Visits: 565
many Thanks for the advice on Ref Tables exclusions.

very helpful

thanks
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search