January 12, 2010 at 3:27 am
Hey
Is this possible??
I have 4 select statements in this stored procedure, im using MS Chart Controls which don't seem to be able to process multiple result sets 🙁 so just wondering is it possible to get the same output that i currently have by using one select statement? Ive tried to do this a number of times but i cant seem to get the correct output, i need the savings summed as the current output displays. Thanks in advance
--===== Create the test table
CREATE TABLE #CO2temp
(
pc_profile_id int,
shutdown_Time datetime NULL,
hibernate_Time datetime NULL,
sleep_Time datetime NULL,
startup_Time datetime NULL,
status varchar(50),
subpolicy_name varchar(50),
building_name varchar(50),
floor_name varchar(50),
room_name varchar(50),
total_hrs_off int,
pc_kwh_rate float,
CO2_factor float,
CO2_savings float
)
--===== Insert the test data into the test table
insert #CO2temp (pc_profile_id, shutdown_Time, startup_Time, subpolicy_name, building_name, floor_name, room_name, pc_kwh_rate, CO2_factor)
SELECT '2', '04/09/2009 11:02:08' , '04/09/2009 16:03:03', 'Exempt', 'Kane', 'floor1', 'Room1.1', '1.2','3.9' UNION ALL
SELECT '3', '04/09/2009 11:00:17','04/09/2009 16:03:00', 'Exempt','Kane', 'floor2', 'Room1.2', '1.2', '3.9' UNION ALL
SELECT '4', '04/09/2009 14:52:17','04/09/2009 16:02:57', 'Sales', 'Kane', 'floor4', 'Room2.0', '1.2', '3.9' UNION ALL
SELECT '5', '04/09/2009 12:12:10','04/09/2009 16:12:50', 'Test', 'Building B', 'floor2', 'Room2.0', '1.2', '3.9' UNION ALL
SELECT '6', '04/09/2009 12:12:10', '04/09/2009 18:12:50', 'Finance', 'Building C', 'floor3', 'Room4.1', '1.2','3.9' UNION ALL
SELECT '7', '04/09/2009 14:12:10', '04/09/2009 18:12:50', 'IT', 'Building d', 'floor7', 'Room4.6', '1.2','3.9' UNION ALL
SELECT '8', '04/09/2009 15:12:10', '04/09/2009 18:12:50', 'Manage', 'Building e', 'floor4', 'Room4.7', '1.2','3.9' UNION ALL
SELECT '9', '04/09/2009 13:12:10', '04/09/2009 18:12:50', 'Finance', 'Building C', 'floor6', 'Room4.1', '1.2','3.9'
--===== get hours_off
UPDATE #CO2temp
SET total_hrs_off = DATEDIFF(HOUR,
COALESCE(shutdown_Time,startup_Time),
COALESCE(startup_Time,shutdown_Time))
UPDATE #CO2temp
SET CO2_savings = (isnull(total_hrs_off, 0) * pc_kwh_rate * CO2_factor)
--===== get savings per group
/** Getting Total Savings per Policy*/
Select subpolicy_name, SUM(CO2_savings) As total_savings
From #CO2temp
group by subpolicy_name
Order by subpolicy_name
/** Getting Total Savings per Building*/
Select building_name, SUM(CO2_savings) As total_savings
From #CO2temp
group by building_name
Order by building_name
/** Getting Total Savings per Floor*/
Select floor_name, SUM(CO2_savings) As total_savings
From #CO2temp
group by floor_name
Order by floor_name
/** Getting Total Savings per Room*/
Select room_name, SUM(CO2_savings) As total_savings
From #CO2temp
group by room_name
Order by room_name
return
January 12, 2010 at 3:59 am
Hi,
Try to make all the four separate select statement in to one statement by using UNION operation. Like
Select 'Savings per Policy' as TYPE , subpolicy_name, SUM(CO2_savings) As total_savings
From #CO2temp
group by subpolicy_name
union all
Select 'Savings per Building',building_name, SUM(CO2_savings)
From #CO2temp
group by building_name
union all
Select 'Savings per Floor',floor_name, SUM(CO2_savings)
From #CO2temp
group by floor_name
union all
Select 'Savings per Room',room_name, SUM(CO2_savings)
From #CO2temp
group by room_name
January 12, 2010 at 4:52 am
Thanks for the post appreciate it, unfortunately that doesn't solve my problem, Im stuck using MS chart Controls which don't seem to be able to process multiple sets. I tried your code and chart controls cant find 'building_name' same problem i've had for a few weeks.
Ive tried this code and it works fine with the chart controls but my only problem is Im not sure is it possible to SUM my totals,
Select
subpolicy_name, SUM(CO2_savings) As subpolicy_savings,
building_name, SUM(CO2_savings) As building_savings,
floor_name, SUM(CO2_savings) As floor_savings,
room_name, SUM(CO2_savings) As room_savings
From #CO2temp
group by subpolicy_name, building_name, floor_name, room_name
Order by subpolicy_name, building_name, floor_name, room_name
What i mean by this is that for example on the subpolicy_name my results return
Exempt 23.4
Exempt 23.4
Finance 28.08
Finance 23.4
i need a total here, i need to have my results as
Exempt 46.8
Finance 51.48
Thanks again for the response
January 12, 2010 at 8:07 am
Something like this?
/** Getting Total Savings per Policy*/
Select 'Policy' AS Sector,
subpolicy_name,
'' AS building_name,
'' AS floor_name,
'' AS room_name,
SUM(CO2_savings) As total_savings
From #CO2temp
group by subpolicy_name
UNION ALL
/** Getting Total Savings per Building*/
Select 'Building' AS Sector,
'' AS subpolicy_name,
building_name,
'' AS floor_name,
'' AS room_name,
SUM(CO2_savings) As total_savings
From #CO2temp
group by building_name
UNION ALL
/** Getting Total Savings per Floor*/
Select 'Floor' AS Sector,
'' AS subpolicy_name,
'' AS building_name,
floor_name,
'' AS room_name,
SUM(CO2_savings) As total_savings
From #CO2temp
group by floor_name
UNION ALL
/** Getting Total Savings per Room*/
Select 'Room' AS Sector,
'' AS subpolicy_name,
'' AS building_name,
'' AS floor_name,
room_name,
SUM(CO2_savings) As total_savings
From #CO2temp
group by room_name
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 12, 2010 at 8:58 am
Thanks for the reply, this very nearly works thanks very much appreciate it, the problem with the reports i have to use unfortunately is that they wont process multiple results sets. They are mainly bar graphs so you have to assign a column to the X and Y axis. The code you gave me works very well so for example for the 1st result if i assign subpolicy_name to the X axis and the total_savings column to the Y axis i get all the totals from the total_savings column displaying in the chart when in fact i only need the first 5 for the subpolicy_name.
Maybe Im trying to do the impossible Im not sure 🙁
January 12, 2010 at 9:03 am
nialltuohy (1/12/2010)
Thanks for the reply, this very nearly works thanks very much appreciate it, the problem with the reports i have to use unfortunately is that they wont process multiple results sets. They are mainly bar graphs so you have to assign a column to the X and Y axis. The code you gave me works very well so for example for the 1st result if i assign subpolicy_name to the X axis and the total_savings column to the Y axis i get all the totals from the total_savings column displaying in the chart when in fact i only need the first 5 for the subpolicy_name.Maybe Im trying to do the impossible Im not sure 🙁
Niall, the code I posted generates 1 result set...
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 12, 2010 at 9:22 am
Chris,
Thanks, i know it does but i have one small problem, i also need the savings to be split into 4 columns, for example a policy savings column, building_savings, floor_savings, and room_savings, Similar to the way there is 4 separate columns for subpolicy_name, building_name, floor_name and room_name. I have to do it this way because when i assign the total_savings column to the Y axis in my chart it shows all the results in that column when i only need the results for what ever category im showing, i.e. policy or building etc
Apologies for all the questions but this is a bit beyond my SQl knowledge 🙁 Really appreciate the help
January 12, 2010 at 9:49 am
Edit: error in my post.
January 12, 2010 at 9:54 am
nialltuohy (1/12/2010)
Chris,Thanks, i know it does but i have one small problem, i also need the savings to be split into 4 columns, for example a policy savings column, building_savings, floor_savings, and room_savings, Similar to the way there is 4 separate columns for subpolicy_name, building_name, floor_name and room_name. I have to do it this way because when i assign the total_savings column to the Y axis in my chart it shows all the results in that column when i only need the results for what ever category im showing, i.e. policy or building etc
Apologies for all the questions but this is a bit beyond my SQl knowledge 🙁 Really appreciate the help
That's why aI created an extra column, sector.
May I suggest that you show how you would like your data to look?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 12, 2010 at 10:30 am
Well the only solution i can think of is having all separate columns for everything. When the chart runs it needs to grab data from a column, so lets say for its X axis we assign subpolicy_name and for its Y axis we assign total_savings as it currently is, it will then display every value in the total_savings column. If i can use separate columns i think this should work, for example by assigning X axis as subpolicy_name and Y axis as a separate column policy_savings then it should work, i only want to display the 5 or 6 records which would be the policy savings.
So basically similar to the way there are separate columns for policy, building, floor, room instead of having the savings in one total savings column if i could break down into 4 columns one for each the
subpolicy into a policy_savings column
building into a building_savings column
floor into a floor_savings column
room into a room_savings column
Sorry i couldn't post my desired results but wouldn't display properly.
Hope that makes sense maybe im trying to do the impossible i don't know 🙁 Thanks again
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply