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


Sum prescription amounts for an individual by given a start date and number of days supplied


Sum prescription amounts for an individual by given a start date and number of days supplied

Author
Message
ken_gardiner
ken_gardiner
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 44
I am trying to develop a query to determine the amount of a drug that an individual has had for every day during a quarter. On some days, there are no drugs prescribed, for others, there may be overlap and I need a total amount (meaning, strength for each summed for a day). The number of drugs, strengths, daysupply etc. can vary. Here's some data:

   create table #MemberInfo 
(ProgramName varchar(255),
DateFilled datetime,
DaySupply integer,
MemberID varchar(255),
Strength integer,
Tradename varchar(255));

insert into #MemberInfo
Values ('InsureCo', '20130612', 30, 'MEM001', 10, 'Sedative')
, ('InsureCo', '20130429', 30, 'MEM001', 20, 'Sedative')
, ('InsureCo', '20130401', 30, 'MEM001', 20, 'Sedative')
, ('InsureCo', '20130529', 30, 'MEM001', 30, 'Sedative')



I really have no idea what the best approach might be to add up the amount of drugs taken on a given day during a quarter. I'd like to avoid using cursors if I can. I was thinking about creating a temp table with all the days for a quarter and then somehow joining those dates to every day a drug is taken (i.e., DateFilled + every subsequent day up to DaySupply). Once I get to the point where I have the dates and amounts for every drug in a quarter, I could group by day and get a sum of strength for each day. I also need to be able to get the average amount taken over a quarter.

Additional Requirements:

1. I have a start date and a number of days. I'd like to create a row
for each member for every day they have a prescription (and do the
same for all of their prescriptions). I would then sum the strength
of all the drugs for each day. If it helps any, all of the drugs
will be of the same class, and strength is going to be equivalent
doses, meaning that I can sum them up.
2. For reporting, I need to be able to count consecutive days that the
amount is greater than some cutoff (let's say 100). That's why I'm
trying to get amount per day.

Desired output

MemberID Date SumStrength
MEM001 2013-04-29 40
MEM001 2013-04-30 40
MEM001 2013-05-01 20
ETC FOR EVERY DAY FOR THIS MEMBER

MEM002 2013-04-01 60
MEM002 2013-04-02 40
ETC FOR EVERY DAY FOR THIS MEMBER


mickyT
mickyT
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1612 Visits: 3317
Hi

I think this is what you are asking for. I've made use of a Tally(Numbers) table to fill out a strength for each day, then summed strength for each member and day. The consecutive days are done using a row_number. I used CTEs to try and make the query a bit clearer and put in script counter for my testing
WITH fillDays AS (
SELECT MemberID, Strength, DATEADD(day, t.N, DateFilled) myDate
FROM #MemberInfo m
CROSS APPLY (SELECT TOP(DaySupply) N - 1 N FROM TALLY ORDER BY N) t
),
sumStrengths AS (
SELECT MemberID, myDate, SUM(Strength) SumStrength, COUNT(*) numScripts
FROM fillDays
GROUP BY MemberID, myDate
)
SELECT MemberID, myDate, SumStrength, numScripts,
CASE WHEN SumStrength >= 100 THEN -- only display when over
ROW_NUMBER() OVER (
PARTITION BY MEMBERID, CASE WHEN SumStrength >= 100 THEN 1 ELSE 0 END
ORDER BY myDate)
END consecutiveDaysOver
FROM sumStrengths
ORDER BY myDATE;


ken_gardiner
ken_gardiner
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 44
This looks fantastic. I do get an "invalid object name TALLY" when I try to run it. Am I missing a few lines? I've never used a tally table before. Thanks.
dwain.c
dwain.c
SSCertifiable
SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)

Group: General Forum Members
Points: 7595 Visits: 6431
ken_gardiner (10/2/2013)
This looks fantastic. I do get an "invalid object name TALLY" when I try to run it. Am I missing a few lines? I've never used a tally table before. Thanks.


MickyT must have a permanent TABLE in his sandbox with the name TALLY. You can substitute that for an in line Tally table (assuming you have access to the sys tables) as follows:


WITH TALLY (N) AS (
SELECT TOP (SELECT MAX(DaySupply) FROM #MemberInfo)
ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM sys.all_columns a CROSS JOIN sys.all_columns b
),
fillDays AS (
SELECT MemberID, Strength, DATEADD(day, t.N, DateFilled) myDate
FROM #MemberInfo m
CROSS APPLY (SELECT TOP(DaySupply) N - 1 N FROM TALLY) t
),
sumStrengths AS (
SELECT MemberID, myDate, SUM(Strength) SumStrength, COUNT(*) numScripts
FROM fillDays
GROUP BY MemberID, myDate
)
SELECT MemberID, myDate, SumStrength, numScripts,
CASE WHEN SumStrength >= 100 THEN -- only display when over
ROW_NUMBER() OVER (
PARTITION BY MEMBERID, CASE WHEN SumStrength >= 100 THEN 1 ELSE 0 END
ORDER BY myDate)
END consecutiveDaysOver
FROM sumStrengths
ORDER BY myDATE;




You can Google "Tally table" for many links, but basically it is just a table with one column that's a number from 1 (or 0) up to some large N (like 10,000,000). I've included a TOP clause on the in line Tally to limit to just the MAX number of Days supply in your table to keep the performance good.

I also dropped the ORDER BY in MickyT's CROSS APPLY to TALLY as that's not needed.

Here's one link you can look at for info on Tally tables: http://www.sqlservercentral.com/articles/Tally+Table/72993/


My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
ken_gardiner
ken_gardiner
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 44
Works perfectly. Elegant and fast solution. The only change I made was to the condition for the CASE statement:

WHEN SumStrength < 100 THEN 0 ELSE




It wasn't filling 0s otherwise.

Thank you both.
mickyT
mickyT
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1612 Visits: 3317
Sorry, I should have put a link up for the tally table references
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26858 Visits: 17557
dwain.c (10/2/2013)
ken_gardiner (10/2/2013)
This looks fantastic. I do get an "invalid object name TALLY" when I try to run it. Am I missing a few lines? I've never used a tally table before. Thanks.


MickyT must have a permanent TABLE in his sandbox with the name TALLY. You can substitute that for an in line Tally table (assuming you have access to the sys tables) as follows:


WITH TALLY (N) AS (
SELECT TOP (SELECT MAX(DaySupply) FROM #MemberInfo)
ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM sys.all_columns a CROSS JOIN sys.all_columns b
),
fillDays AS (
SELECT MemberID, Strength, DATEADD(day, t.N, DateFilled) myDate
FROM #MemberInfo m
CROSS APPLY (SELECT TOP(DaySupply) N - 1 N FROM TALLY) t
),
sumStrengths AS (
SELECT MemberID, myDate, SUM(Strength) SumStrength, COUNT(*) numScripts
FROM fillDays
GROUP BY MemberID, myDate
)
SELECT MemberID, myDate, SumStrength, numScripts,
CASE WHEN SumStrength >= 100 THEN -- only display when over
ROW_NUMBER() OVER (
PARTITION BY MEMBERID, CASE WHEN SumStrength >= 100 THEN 1 ELSE 0 END
ORDER BY myDate)
END consecutiveDaysOver
FROM sumStrengths
ORDER BY myDATE;




You can Google "Tally table" for many links, but basically it is just a table with one column that's a number from 1 (or 0) up to some large N (like 10,000,000). I've included a TOP clause on the in line Tally to limit to just the MAX number of Days supply in your table to keep the performance good.

I also dropped the ORDER BY in MickyT's CROSS APPLY to TALLY as that's not needed.

Here's one link you can look at for info on Tally tables: http://www.sqlservercentral.com/articles/Tally+Table/72993/


You can create an on the fly tally without touching any actual tables too. This results in 0 read execution plan because no actual tables are touched.


WITH
E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
Tally(N) AS
(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
)
select N from Tally



_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
ken_gardiner
ken_gardiner
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 44
I'm looking forward to trying this completely CTE based tally table. My impression is that this script is going to be very fast for what it is. I'll let you know how it performs with some big data sets when I start implementing in a week or two. Thank you.
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