Roll up issue and subquery - 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)

Roll up issue and subquery

Author

Topic

oasis1
Starting Member

35 Posts

Posted - 2010-07-11 : 02:00:31

I Have the following rollup. I am trying to Divide NS/TA and put it in the final results. How would I write this? I tried a subquery but it won't work. If I try to calculate in the below code it calculates wrong. Mahalo for your help

Select
case
When Grouping(Location) = 1 then 'All Location'
Else Location
End as MyLocations,
case
When Grouping(Department) = 1 then 'All Department'
Else Department
End as MyDepartment,
sum(NoShows) as NS, sum(TotalAppts) as TA
From dbo.IVR
Group by Location, Department with Rollup

Ala Moana Lab 6 34
Ala Moana Peds 11 97
Ala Moana All Department 17 131
Hon Cliinic ED 9 90
Hon Cliinic ER 7 90
Hon Cliinic All Department 16 180
All Location All Department 33 311

oasis1
Starting Member

35 Posts

Posted - 2010-07-11 : 02:07:13

Got it !!!!

Select SQ.*, (SQ.NS/SQ.TA) as Percentage From (Select
case
When Grouping(Location) = 1 then 'All Location'
Else Location
End as MyLocations,
case
When Grouping(Department) = 1 then 'All Department'
Else Department
End as MyDepartment,
sum(NoShows) as NS, sum(TotalAppts) as TA
From dbo.IVR
Group by Location, Department with Rollup) SQ

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources