Rollup/Cube SQL 2005 - 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)

Rollup/Cube SQL 2005

Author

Topic

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2010-11-15 : 13:14:34

Is it possible to get anything like GROUPING SETS in SQL 2005?

I have a data set with 7 or 8 groups, my biggest problem is the size of the data it returns, I really only need the summary lines on 3 groups and the rest I don't need summarized.

Any hints on this problem?

Thanks.

Select
year(Date) as YYYY
, month (Date) as MM
, Case When Grouping(Program) = 1 then 'ALL' else Program End as Program
, Case When Grouping(Right(RC, 2)) = 1 then 'ALL' else Right(RC, 2) End as RC          
, Case When Grouping(Case when Network like 'Something%' then 'Something' else 'Other' End) = 1 then 'ALL' else 
	(Case when Network like 'Something%' then 'Something' else 'Other' End) End as Network   
, Case When Grouping(GenericBrand) = 1 then 'ALL' else Program End as Program
, pdg.DrugGroupName
, Sum(IngredientAmount) as IngredientCost
, Sum(AWPUnitPrice * DispensedQuantity) as AWPCost 
, Sum(Case When IsCapitated = 'Y' then ContractPaidAmount Else PaidAmount End) as 'PaidAmount'
, sum(Events) as script
, Count(ClaimID) as ScriptCount

Where ...

Group by 
year(Date) 
, month (Date) 
, Program
, Right(RC, 2)   
, Case when Network like 'Something%' then 'Something' else 'Other' End
, GenericBrand
, pdg.DrugGroupName
With Rollup

Edit: I really only want to Roll up the values on Program / RC / Network, I dont care about montly and yearly rollups, the lines just add clutter, but I need the months and years in my result set.

Having the rollup be on everything jumps the number of lines to an amazing amount, which I need to trim.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources