February 26, 2012 at 3:46 pm
CREATE TABLE [dbo].[TESTGROUNDFORIDEAS](
[AU] [nvarchar](10) NULL,
[GL] [int] NULL,
[GL1] [int] NULL,
[MONTH] [varchar](20) NULL,
[PERIOD] [int] NULL,
[HIGH_OFFICE] [nvarchar](23) NULL,
[DIVISION] [nvarchar](56) NULL,
[LOWER_OFFICE] [nvarchar](12) NULL
) ON [PRIMARY]
GO
INSERT INTO TESTGROUNDFORIDEAS
values ('MU-123',12,5,'JAN','201101','STAND CHART','OHIO','MILFORD')
,('MU-124',3,54,'JAN','201101','US BANK','CALIFORNIA','LA')
,('MU-125',31,5,'JAN','201101','STAND CHART','NEW YORK','BRONX')
,('MU-124',3,56,'FEB','201102','US BANK','VIRGINA','ALEXANDRA')
,('MU-123',49,5,'FEB','201102','STAND CHART','DELAWARE','NUOME')
,('MU-125',3,88,'FEB','201102','US BANK','UTAH','UTICA')
The table above shows a sample of the data I’m currently working with at work. My task is to calculate the total revenue for each [au] and rolling up the revenues to the high office which in this case is the values in the High Office which are (Stand chart, US bank). The revenues are calculated by adding GL1 AND GL2. The current data in the table is at the lower office level. I need to perform this task to generate a report showing revenues by monthly for each HIGH OFFICE, DIVISION and LOWER_OFFICE . Calculating the revenues was easy but rolling up the revenues to the high office and division have been a pain in my *** as after rollup the addition of the GL numbers do not match the revenue numbers. . This has to be done showing all the descriptive columns. Any help will be appreciated.
February 26, 2012 at 4:33 pm
You stand a better chance of getting tested help if you would post your table definition, sample data and required results in a readily consumable format.
Click on the first link in my signature block.
That link leads to an article, which includes T-SQL statements to make the task rather quick and easy.
February 26, 2012 at 5:18 pm
If I correctly understand your requirement, you need to do Pivoting.
Here is the code:
SELECT
PVT.AU,
PVT.DIVISION,
PVT.LOWER_OFFICE,
PVT.[STAND CHART],
PVT.[US BANK]
FROM
(select AU, DIVISION, HIGH_OFFICE, LOWER_OFFICE, GL, GL1, GL + GL1 AS TotalRevenue
from TESTGROUNDFORIDEAS
) SRC
PIVOT
(
SUM(TotalRevenue)
FOR HIGH_OFFICE IN ([STAND CHART], [US BANK])
) AS PVT
February 27, 2012 at 5:39 am
Pivoting may not perform well enough especially for large datasets (I guess your GL is not a small table :-))
Would be great if you could provide exact espected results for a sample you've given. This would describe what you need in the best details!
February 27, 2012 at 5:57 am
Just one of the way of combining subtotals in SQL:
select [month], SumType, high_office, division, LOWER_OFFICE, AU, Revenue from
(
select 1 lo, 1 do, 'Lower_Ofice Total:' SumType,high_office, division, LOWER_OFFICE, [month], AU, SUM(GL+GL1) as Revenue
from TESTGROUNDFORIDEAS
group by high_office, division,LOWER_OFFICE, [month], AU
union all
select 2 lo, 1 do, 'Division Total:' SumType,high_office, division, '', [month], AU, SUM(GL+GL1) as Revenue
from TESTGROUNDFORIDEAS
group by high_office,division, [month], AU
union all
select 2 lo, 2 do, 'HightOffice Total:' SumType,high_office, '','',[month], AU, SUM(GL+GL1) as Revenue
from TESTGROUNDFORIDEAS
group by high_office, [month], AU
) a
order by [month], high_office, do, DIVISION, lo, LOWER_OFFICE
Please specify the required output in more details.
February 27, 2012 at 8:46 pm
Hello Eugene,
The pivot was a great idea but your solution works for the data set I'm working on currently. It's a large data set with over 2 million rows.
Thanks for you response!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply