January 12, 2013 at 3:47 pm
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
January 12, 2013 at 4:39 pm
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?
January 12, 2013 at 4:50 pm
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
January 14, 2013 at 10:12 am
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.
January 15, 2013 at 6:23 am
many Thanks for the advice on Ref Tables exclusions.
very helpful
thanks
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply