CODEWITHSUNDEEP
sql-server
user734781
user734781

Reputation: 273

I want to select specific values from a SQL Server table

I want to select specific values from a table called Prescriptions. I want to select a patient that is utilizing the following medicines at a certain period in time:

This is the select that I have been using, but it returns patients that are utilizing just one or two of the medicines. I want to be able to bring just the patient that is utilizing both of the medicines:

SELECT MEDICINE, CLIENTNUMBER
FROM PRESCRIPTIONS  
where  
(MEDICINE like 'simvastatin %' OR
MEDICINE like 'amlodipine%')  
AND
DATE between '11/23/2010' and '11/23/2010'
Group by CLIENTNUMBER, MEDICINE
Order by CLIENTNUMBER

Can anyone help? Thank you very much!

Upvotes: 2

Views: 15825

Answers (3)

Andomar
Andomar

Reputation: 238126

You could use two exists clauses to ensure the client has subscriptions for both medicines:

select  p.MEDICINE
,       p.CLIENTNUMBER
from    PRESCRIPTIONS as p
where   p.DATE between '11/23/2010' and '11/23/2011'
        and exists
        (
        select  *
        from    PRESCRIPTIONS as p2
        where   p2.DATE between '11/23/2010' and '11/23/2011'
                and p2.CLIENTNUMBER = p.CLIENTNUMBER
                and p2.MEDICINE like 'simvastatin %'
        )
        and exists
        (
        select  *
        from    PRESCRIPTIONS as p3
        where   p3.DATE between '11/23/2010' and '11/23/2011'
                and p3.CLIENTNUMBER = p.CLIENTNUMBER
                and p3.MEDICINE like 'amlodipine %'
        )

Upvotes: 2

user421125
user421125

Reputation:

you must create a table for your medician an then get division of prescription on it.

Upvotes: 0

Marco
Marco

Reputation: 57583

Try this:

SELECT p1.Medicine m1, p2.Medicine m2, p1.ClientNumber cn 
FROM Prescriptions p1 INNER JOIN Prescriptions p2 
ON p1.ClientNumber = p2.ClientNumber
WHERE (
    ((m1 LIKE 'simvastatin %') AND (m2 LIKE 'amlodipine%')) OR
    ((m2 LIKE 'simvastatin %') AND (m1 LIKE 'amlodipine%'))
    AND Date BETWEEN '11/23/2010' AND '11/23/2010')
GROUP BY cn, m1
ORDER BY cn

Upvotes: 1

Related Questions