March 5, 2013 at 12:47 pm
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.
March 5, 2013 at 1:18 pm
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 5, 2013 at 3:56 pm
Thank you very much for "taking the risk" 😉 My apologies for not posting the data is a different format.
March 6, 2013 at 7:26 am
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply