Progressive Aggregate - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Progressive Aggregate

Author

Topic

GregDDDD
Posting Yak Master

120 Posts

Posted - 2011-09-20 : 19:06:33

I have the query below which will show me the number of rows created for a specific type for each month. The output looks like this

Month PatCount
----- -----------
01 2
02 1
03 6
05 7
06 2
07 45
08 57
09 17


SELECT CONVERT(VARCHAR(2), [date_created], 101) AS [Month],
COUNT([patient_id]) AS PatCount
FROM [AICU].[dbo].[tbl_encounters]
where [record_active] = 1 and [encounter_type_id] = 4
GROUP BY CONVERT(VARCHAR(2), [date_created], 101)
ORDER BY CONVERT(VARCHAR(2), [date_created], 101)

What I would like it to do is give me a running total, like what is below. Is that possible or am I spinning my wheels trying to find a solution.

Month PatCount
----- -----------
01 2
02 3
03 9
05 16
06 18
07 63
08 102
09 119

Thanks in advance.

GregDDDD
Posting Yak Master

120 Posts

Posted - 2011-09-20 : 19:15:31

Hey! I got it on my own!

SELECT CONVERT(VARCHAR(2), e1.[date_created], 101) AS [Month],
COUNT(e1.[patient_id]) AS PatCount,
(SELECT COUNT([patient_id]) from [tbl_encounters] e2 
WHERE [record_active] = 1 AND [encounter_type_id] = 4 
AND CONVERT(VARCHAR(2), e2.[date_created], 101)  <= CONVERT(VARCHAR(2), e1.[date_created], 101) )
FROM [AICU].[dbo].[tbl_encounters] e1
where [record_active] = 1 and [encounter_type_id] = 4
GROUP BY CONVERT(VARCHAR(2), [date_created], 101)
ORDER BY CONVERT(VARCHAR(2), [date_created], 101)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources