October 3, 2010 at 6:20 pm
I have to write a sql query to find out all the active Treatments for last year....
below are the rules that should follow to get the last year active treatments
1. If the Treatment Start Date is greater then 01/01/2009
2. If the Treatment Start Date is less then 01/01/2009 and TreatmentEnd date is Null or Treatment End Date is greater then 12/12/2009
below is the table structure and have the sample data....
Create Table Treatment
(TreatmentID int,
TreatStartdate datetime,
TreatEndDate datetime)
Insert into Treatment Values(1,'2009-01-01',Null)
Insert into Treatment Values(2,'2007-01-01','2009-12-12')
Insert into Treatment Values(3,'2009-03-23','2009-11-12')
Insert into Treatment values(4,'2006-02-02',Null)
Insert into Treatment values(5,'2010-01-01',Null)
Insert into Treatment values(6,'2007-08-09','2008-12-04')
I should get the following records
Treatment ID - 1,2,3,4
I should not get the following records
Treatment ID - 5 and 6
Reasons
Treatment ID 1 started at 01/01/2009 and Treatment End Dt is null
so it is active last year..
Treatement ID 2 even it is started in 01/01/2007 it is consider as active because it has treatment end date 11/12/2009...so it is active last year...
Treatment ID 5 started 01/01/2010 so it is consider as inactive....
Treatment ID 6 started 08/09/2007 and Ended in 12/04/2008...so it is consider as inactive....
Please help me with the query....
October 3, 2010 at 6:27 pm
We can help, but we don't really like to write these for you.
What you can do is use your rules as part of a WHERE clause in a query. You can write two queries and then use a UNION between them, or you can use an OR clause in your WHERE, and parenthesis around each of your two rules. Hint, the second rule with have AND clauses inside the parens.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply