June 29, 2009 at 3:41 pm
Hi Guys
I am working on a patiet table where a patient is prescribed two types of drugs and there are two scenarios for how the prescription occurs:
1) Patient is prescribed drug A in different year and drug B in different year
2) Patient is prescribed Drug A and Drug B in the same year and same month.
I need to find out those patients who were prescribed only Drug A in the given year.
WhatI have done is I have created two temporary tables :
1) For patients who were prescribed Drug A for the a two year period
2) For patients who were prescribed Drug B for the a two year period
I can easily find out the patients who were prescibed both the drugs simultaneously in the same year and month but how do I find the patients who were prescribed just Drug A in any give nyear??
Please help
Cheers
June 29, 2009 at 3:46 pm
Can you provide the DDL for the table (original, not temporary), sample data (in a readily consummable format that can be cut/paste/run in SSMS), expected results based on the sample data, and the code you have currently deveoped.
If you need help with this request, please read and follow the first article I have referenced below in my signature block regarding asking for assistance.
June 29, 2009 at 3:54 pm
Table A
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[DrugA]
AS
SELECT year1, month1, Quarter,[Patient No.]
FROM MainTable AS a
WHERE ([Chemical name] = 'Drug A') AND (year1 > 2006)
Table B
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[DrugA]
AS
SELECT year1, month1, Quarter,[Patient No.]
FROM MainTable AS a
WHERE ([Chemical name] = 'Drug B') AND (year1 > 2006)
Hope this helps
June 29, 2009 at 3:57 pm
Nuts (6/29/2009)
Table ASET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[DrugA]
AS
SELECT year1, month1, Quarter,[Patient No.]
FROM MainTable AS a
WHERE ([Chemical name] = 'Drug A') AND (year1 > 2006)
Table B
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[DrugA]
AS
SELECT year1, month1, Quarter,[Patient No.]
FROM MainTable AS a
WHERE ([Chemical name] = 'Drug B') AND (year1 > 2006)
Hope this helps
Nope. I asked for the DDL for the source table, not the views you above. Also, I will need some sample data, and the expected results. Everything you need done should be able tobe done with out the views.
Please take the time to read the article I referred you to below and follow its instructions.
June 29, 2009 at 10:34 pm
Hi Here is the actual DDL:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Main Table](
[Drug name] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Calendar year] [int] NULL,
[Calendar month] [int] NULL,
[Quarter] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Patient ID] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
June 29, 2009 at 10:46 pm
Okay, so when do I get everything else I asked you for, you know, the sample data, expected results based on said data? If you want help, you have to help. I'm not going to put in any more effort to help you than you are willing to put in getting help.
As I suggested several times already, please read the first article I have referenced below in my signature block regarding asking for assistance. And if you'd like more on why, then read this also: The Flip Side.
June 30, 2009 at 3:03 pm
Here is the sample data
USE [main table]
GO
INSERT INTO [main table]([Drug name],
[calendar year],[calendar month],[quarter],[patient id])
SELECT 'typeA' ,2000,1,'q1','ac1'
UNION ALL
SELECT 'typeA' ,2001,2,'q1','qwe2'
UNION ALL
SELECT 'typeB' ,2000,1,'q1','ac1'
UNION ALL
SELECT 'typeB' ,2005,12,'q4','bgresd'
UNION ALL
SELECT 'typeB',2001,2,'q1','qwe2'
GO
Here is the desired output:
[calendar year] quarter Total_Patients_on_Aand_B
2000 q1 1
2001 q1 1
Hope it helps.
Let me know if I have missed anything
Thanks
June 30, 2009 at 4:42 pm
If you can fix your names now you should remove the spaces. But, here ya go.
SELECT
A.[Calendar year],
A.Quarter,
COUNT(*) AS Total_Patients_on_Aand_B
FROM
(
SELECT *
FROM [Main Table]
WHERE [Drug name] = 'typeA'
) AS A
INNER JOIN
(
SELECT *
FROM [Main Table]
WHERE [Drug name] = 'typeB'
) AS B
ON A.[patient id] = B.[patient id]
AND A.[Calendar year] = B.[Calendar year]
AND A.Quarter = B.Quarter
GROUP BY
A.[Calendar year],
A.Quarter
June 30, 2009 at 4:45 pm
Here is another one for just for people on A and not B for a given year:SELECT
A.[Calendar year],
A.Quarter,
COUNT(*) AS Total_Patients_on_Just_A
FROM
(
SELECT *
FROM [MainTable]
WHERE [Drug name] = 'typeA'
) AS A
LEFT OUTER JOIN
(
SELECT *
FROM [Main Table]
WHERE [Drug name] = 'typeB'
) AS B
ON A.[patient id] = B.[patient id]
AND A.[Calendar year] = B.[Calendar year]
WHERE
B.[Drug name] IS NULL
GROUP BY
A.[Calendar year],
A.Quarter
June 30, 2009 at 9:36 pm
Hi
Thanks for your reply.
I tried your query but there are still few patient IDs appearing in the result who have been on both type of Drugs..
June 30, 2009 at 10:23 pm
Hi Friend,
Why are you not adding [Drug Name] filter in your where clause ?
June 30, 2009 at 10:47 pm
Here is more code for you to take a look at and play with.
CREATE TABLE [dbo].[MainTable](
[DrugName] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CalendarYear] [int] NULL,
[CalendarMonth] [int] NULL,
[CalendarQuarter] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PatientID] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
INSERT INTO [MainTable](
[DrugName], [CalendarYear],[CalendarMonth],[CalendarQuarter],[PatientID])
SELECT 'typeA' ,2000,1,'q1','ac1'
UNION ALL
SELECT 'typeA' ,2001,2,'q1','qwe2'
UNION ALL
SELECT 'typeB' ,2000,1,'q1','ac1'
UNION ALL
SELECT 'typeB' ,2005,12,'q4','bgresd'
UNION ALL
SELECT 'typeB',2001,2,'q1','qwe2'
GO
select -- Show all records in table
*
from
dbo.MainTable;
select -- Show count of distinct patients on both A and B
CalendarYear,
CalendarQuarter,
count(distinct PatientID) as PatientsOnAandB
from
dbo.MainTable mt
where
exists(select 1 from dbo.MainTable dmt1 where DrugName = 'typeA' and dmt1.PatientID = mt.PatientID)
and exists(select 1 from dbo.MainTable dmt2 where DrugName = 'typeB' and dmt2.PatientID = mt.PatientID)
group by
CalendarYear,
CalendarQuarter
;
select -- Show count of distinct patients not on A but on B
CalendarYear,
CalendarQuarter,
count(distinct PatientID) as PatientsOnNotAandB
from
dbo.MainTable mt
where
not exists(select 1 from dbo.MainTable dmt1 where DrugName = 'typeA' and dmt1.PatientID = mt.PatientID)
and exists(select 1 from dbo.MainTable dmt2 where DrugName = 'typeB' and dmt2.PatientID = mt.PatientID)
group by
CalendarYear,
CalendarQuarter
;
select -- Show count of distinct patients on A but not on B
CalendarYear,
CalendarQuarter,
count(distinct PatientID) as PatientsOnAandNotB
from
dbo.MainTable mt
where
exists(select 1 from dbo.MainTable dmt1 where DrugName = 'typeA' and dmt1.PatientID = mt.PatientID)
and not exists(select 1 from dbo.MainTable dmt2 where DrugName = 'typeB' and dmt2.PatientID = mt.PatientID)
group by
CalendarYear,
CalendarQuarter
;
drop table dbo.MainTable;
July 1, 2009 at 4:03 am
Hi from Sydney...
reading the notes above, I am still unsure exactly what it is you are trying to do, but I am guessing you want a stored procedure which is passed a drug, and which returns ALL entries for ALL patients who were given the nominated drug (in the parameter) AND ONLY the nominated drug in that given month. In other words, if they were not prescribed the drug, you don't want them, and if they were prescribed the drug AND WERE ALSO PRESCRIBED ANY OTHER DRUG WHATSOEVER IN THE SAME MONTH then you don't want them.
If so, consider this:
declare@drug varchar(5) = 'typeB' // the parameter
selecta.[drug name], a.[Calendar year], a.[calendar month], a.Quarter, a.[Patient ID]
FROM[Main Table] a
wherea.[drug name] = @drug
and not exists (
select1
from[Main Table] b
whereb.[drug name] @drug
andb.[Calendar year]= a.[Calendar year]
andb.[calendar month]= a.[calendar month]
andb.Quarter= a.Quarter
andb.[Patient ID]= a.[Patient ID]
)
You might want to spruce up 'a' and 'b' if you use this. You can also drop, say, Quarter from the query if the criteria is something like 'not prescribed any other drug in the same quarter etc.
I hope I have correctly divined wht you actually want, however...
Scott
July 1, 2009 at 4:07 am
Hi again from Scott - i realised I made an error in my post, towards the end - I meant to say you can for instance, drop 'month' if you don't want entries posted for "other" drugs in the same quarter, though not neccessarily in the same month...
July 1, 2009 at 6:27 am
Hope this query may help you:-)
select * from [dbo].[Main Table]a
where [drug name]='typeA' and not exists(select * from [dbo].[Main Table]b where a.[calendar year]=b.[calendar year] and b.[drug name]='typeB')
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply