October 2, 2013 at 4:32 pm
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
October 2, 2013 at 7:40 pm
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;
October 2, 2013 at 8:20 pm
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.
October 2, 2013 at 9:05 pm
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 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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
October 3, 2013 at 3:41 am
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.
October 3, 2013 at 11:51 am
Sorry, I should have put a link up for the tally table references
October 3, 2013 at 12:13 pm
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 3, 2013 at 4:44 pm
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.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply