Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Sum prescription amounts for an individual by given a start date and number of days supplied Expand / Collapse
Author
Message
Posted Wednesday, October 2, 2013 4:32 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 11:46 AM
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

Post #1500951
Posted Wednesday, October 2, 2013 7:40 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 5:20 AM
Points: 950, Visits: 2,876
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;

Post #1500963
Posted Wednesday, October 2, 2013 8:20 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 11:46 AM
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.
Post #1500969
Posted Wednesday, October 2, 2013 9:05 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 6:01 PM
Points: 3,609, Visits: 5,222
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!
Post #1500970
Posted Thursday, October 3, 2013 3:41 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 11:46 AM
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.
Post #1501086
Posted Thursday, October 3, 2013 11:51 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 5:20 AM
Points: 950, Visits: 2,876
Sorry, I should have put a link up for the tally table references
Post #1501304
Posted Thursday, October 3, 2013 12:13 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:49 PM
Points: 13,069, Visits: 11,908
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 Moden's 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)
Post #1501314
Posted Thursday, October 3, 2013 4:44 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 11:46 AM
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.
Post #1501378
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse