Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Performing calculations on subtotals Expand / Collapse
Author
Message
Posted Tuesday, March 5, 2013 12:47 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, March 6, 2013 7:27 AM
Points: 2, Visits: 3
Hello: I'm a new user to SQL Server 2012 Standard. I have a table with information similar to what's found in the spreadsheet attached. I would like to get the total Pay calculated not just by department (e.g. total of CNA is 70.31) but then also have a total of the subtotals of some departments (but not all). For example, I'd like the total of LPN and RN together (both of which are types of nurses, so I want a 'nurse total') but not CNA. Is this possible to do through a SQL query, or another way in SSMS? I've read something about Calculated Members and cubes, is that the direction I have to head in, or can it be handled in SSMS? For the record, I'm using Yellowfin to run reports off of the data in SS 2012 and I can't find a way to create these calculations in YF, so I'm hoping to do it on the server end. I hope this is clear. thank you in advance.

  Post Attachments 
Payroll Record Example.xlsx (4 views, 9.27 KB)
Post #1427001
Posted Tuesday, March 5, 2013 1:18 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:44 AM
Points: 13,093, Visits: 12,574
Hi and welcome to the forums. The best chance you have of people helping is if you first post ddl and sample data in a consumable format. Most people won't even bother with your post if you post an excel doc. I took the risk this time. I have formatted your data into something consumable to demonstrate what I mean.

if object_id('tempdb..#PayData') is not null
drop table #PayData

create table #PayData
(
Dept int,
DeptDesc varchar(5),
PayDate datetime,
Pay numeric(7,2)
)

insert #PayData
select 006051, 'CNA', '2013-01-04', 18.53 union all
select 006051, 'CNA', '2013-01-04', 19.16 union all
select 003000, 'RN', '2013-01-04', 3.86 union all
select 003100, 'LPN', '2013-01-04', 11.54 union all
select 006051, 'CNA', '2013-01-04', 3.76 union all
select 005410, 'RN', '2013-01-04', 16.44 union all
select 003100, 'RN', '2013-01-04', 6.08 union all
select 005410, 'RN', '2013-01-04', 12.81 union all
select 805410, 'RN', '2013-01-04', 12.35 union all
select 006051, 'CNA', '2013-01-04', 15.02 union all
select 003000, 'LPN', '2013-01-04', 2.25 union all
select 003100, 'LPN', '2013-01-04', 10.03 union all
select 006051, 'CNA', '2013-01-04', 10.33 union all
select 006051, 'CNA', '2013-01-04', 3.51 union all
select 003000, 'RN', '2013-01-04', 11.91 union all
select 006050, 'LPN', '2013-01-04', 3.53 union all
select 806050, 'RN', '2013-01-04', 12.81 union all
select 006031, 'R.N.', '2013-01-04', 49.86 union all
select 006020, 'LPN', '2013-01-04', 5.67

Now I think that this query will produce the results you are looking for but from the brief description it is hard to say for sure.

select * from 
(
select case when DeptDesc in ('LPN', 'RN', 'R.N.') then 'Nurse' else DeptDesc end as DeptDesc,
sum(Pay) over(partition by case when DeptDesc in ('LPN', 'RN', 'R.N.') then 'Nurse' else DeptDesc end ) as TotalPay
from #PayData
group by case when DeptDesc in ('LPN', 'RN', 'R.N.') then 'Nurse' else DeptDesc end, Pay
)x
group by DeptDesc, TotalPay



_______________________________________________________________

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1427021
Posted Tuesday, March 5, 2013 3:56 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, March 6, 2013 7:27 AM
Points: 2, Visits: 3
Thank you very much for "taking the risk" ;) My apologies for not posting the data is a different format.
Post #1427070
Posted Wednesday, March 6, 2013 7:26 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:44 AM
Points: 13,093, Visits: 12,574
adam.berman (3/5/2013)
Thank you very much for "taking the risk" ;) My apologies for not posting the data is a different format.


Did that get you the desired results?


_______________________________________________________________

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1427389
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse