October 6, 2009 at 7:30 am
I currently have some data on the number of patients treated per month by certain hospital providers. I would like to produce an output such that it gives me the total cumulative number of patients per provider per month. I have included some sql to illustrate my situation
create table #test(
provider varchar(5),
treated_total int,
month_of_treatment varchar(5)
)
insert into #test(provider,treated_total,month_of_treatment)
SELECT 'RP4',7,'JAN' UNION ALL
SELECT'RP5',10,'JAN' UNION ALL
SELECT'RP6',9,'JAN' UNION ALL
SELECT'RP7',12,'JAN' UNION ALL
SELECT'RP4',4,'FEB' UNION ALL
SELECT'RP5',11,'FEB' UNION ALL
SELECT'RP6',11,'FEB' UNION ALL
SELECT'RP7',3,'FEB' UNION ALL
SELECT'RP4',9,'MAR' UNION ALL
SELECT'RP5',11,'MAR' UNION ALL
SELECT'RP6',12,'MAR' UNION ALL
SELECT'RP7',13,'MAR'
in this case i would like to produce a dataset which will show for example that
proivder RP6 in the month of March had a cumulative total of 32 patients treated.
Any help would be much appreciated
October 6, 2009 at 7:40 am
upppps
October 6, 2009 at 7:42 am
What you're trying to do is known as a running total. Ordinarily, I'd point you to Jeff's article on it, but it's under construction as he is re-writing it. However, there's a link to the article in my signature anyways which has a query demonstrating it, just not the full write-up / explanation of how it works.
Also, here's a long thread about another issue solved byrunning totals.
http://qa.sqlservercentral.com/Forums/Topic789373-8-1.aspx
In addition, 'running total sql' in google will give you a lot of information.
All of that said, this is a very easy query to write(once you know how), but I wanted to give you the opportunity to read about it before I just gave you the answer. Let us know if you have any issues with it or if you would like the code for this one.
October 6, 2009 at 8:38 am
hey,
thanks for the info. I have had a good look on the internet and managed to find out the logic of what i was trying to do and managed to solve it. I changed the month_of_treatment to a datetime and applied the following sql
SELECT A.provider, A.month_of_treatment, SUM(B.treated_total) AS RunningTotal
FROM #test A INNER JOIN #test B
ON A.month_of_treatment >= B.month_of_treatment
and A.provider = b.provider
GROUP BY A.provider, a.month_of_treatment
order by A.provider
Incidentally, with the original data I gave you how would I have done it without out the datetimes?
October 6, 2009 at 9:36 am
eseosaoregie (10/6/2009)
Incidentally, with the original data I gave you how would I have done it without out the datetimes?
This is the method that I prefer. For this running total (which is one of the simplest forms of iterative logic) you have more options including the one you're using. More advanced issues like the one I linked in the thread above can't use a method like yours.
While your method may have a performance benefit on small datasets due to not having to create the temp table etc, IIRC they tend to be slower on large datasets because of the triangular joins.
CREATE TABLE #test2(
provider varchar(5),
treated_total int,
monthnum varchar(5),
PRIMARY KEY CLUSTERED(PROVIDER, MONTHnum),
RTint)
INSERT INTO #test2(provider, treated_total, monthnum)
SELECT PROVIDER, treated_total, MONTH(month_of_treatment + ' 01, 2009')
FROM #test
DECLARE @rt int, @LP varchar(10)
SELECT @rt = 0, @LP = ''
UPDATE #test2
SET @rt = RT = CASE WHEN provider = @LP THEN @rt + treated_total ELSE treated_total END,
@LP = Provider
FROM #test2 OPTION (MAXDOP 1)
SELECT * FROM #Test2
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply