Is this possible? Select Statements Issue

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

General SQL Server Forums

New to SQL Server Programming

Is this possible? Select Statements Issue

Author

Topic

nt86
Yak Posting Veteran

54 Posts

Posted - 2010-01-07 : 11:20:33

Hey,

Is this possible????
I have a stored procedure that has a number of Select Statements, it works great I'm just wondering can i use one select statement to achieve the same results. This is my current Stored Procedure


ALTER PROCEDURE dbo.SP_TEST_2

AS

/* Create temp 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 Values into Temp Table from View_Savings_Report*/
	
	insert into #CO2temp (pc_profile_id, shutdown_Time, hibernate_Time, sleep_Time, startup_Time, status, subpolicy_name, building_name, floor_name, room_name, pc_kwh_rate, CO2_factor)
	SELECT PC_PROFILE_ID, SHUTDOWN_TIME, HIBERNATE_TIME, SLEEP_TIME, STARTUP_TIME, STATUS, SUB_POLICY_NAME, BUILDING_NAME, FLOOR_NAME, ROOM_NAME, PC_POWER_RATING, CO2_FACTOR
	FROM VIEW_CO2_REPORT 
	
	/** Get total hours off*/
	UPDATE #CO2temp
	SET total_hrs_off = DATEDIFF(HOUR, 
	COALESCE(shutdown_Time,hibernate_Time,sleep_Time,startup_Time),
	COALESCE(startup_Time,sleep_Time,hibernate_Time,shutdown_Time))


	/** Calculate the Savings in CO2 when system is in place, multiple hours off * KWH Rate * CO2 Factor*/
	UPDATE #CO2temp
	SET CO2_savings = (isnull(total_hrs_off, 0) * pc_kwh_rate * CO2_factor)
	
	
	/** 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

You can see i have 4 Select Statements for my savings subpolicy, building, room floor. I've tried just using one select statement as follows:


Select subpolicy_name, building_name, floor_name, room_name, SUM(CO2_savings) As total_savings
	From #CO2temp
	group by subpolicy_name, building_name, floor_name, room_name
	order by subpolicy_name, building_name, floor_name, room_name

This works however i do not get my desired output, its does not SUM the columns, for example if i have a subpolicy called Sales, it may show the sales policy more than once in my results instead of just showing it once with a total, similarly for Building, Floor, Room. You may wonder why I want to do this it is because I am using MS Chart Controls for my reports and they don't seem to be able to process multiple result sets.

Would appreciate any help :)

niall

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-07 : 11:26:45

are you using sql 2005?

nt86
Yak Posting Veteran

54 Posts

Posted - 2010-01-07 : 11:29:22

quote:
Originally posted by visakh16

are you using sql 2005?

Sorry should have mentioned that, ya Im using SQL Server 2005

niall

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-07 : 11:39:49

[code]
ALTER PROCEDURE dbo.SP_TEST_2

AS

/* Create temp 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 Values into Temp Table from View_Savings_Report*/

insert into #CO2temp (pc_profile_id, shutdown_Time, hibernate_Time, sleep_Time, startup_Time, status, subpolicy_name, building_name, floor_name, room_name, pc_kwh_rate, CO2_factor,total_hrs_off,CO2_savings)
SELECT PC_PROFILE_ID, SHUTDOWN_TIME, HIBERNATE_TIME, SLEEP_TIME, STARTUP_TIME, STATUS, SUB_POLICY_NAME, BUILDING_NAME, FLOOR_NAME, ROOM_NAME, PC_POWER_RATING, CO2_FACTOR, DATEDIFF(HOUR,
COALESCE(shutdown_Time,hibernate_Time,sleep_Time,startup_Time),
COALESCE(startup_Time,sleep_Time,hibernate_Time,shutdown_Time)),
(isnull(total_hrs_off, 0) * pc_kwh_rate * CO2_factor)

FROM VIEW_CO2_REPORT

/** Getting Total Savings per Room*/
Select DISTINCT room_name,
SUM(CO2_savings) OVER(PARTITION BY subpolicy_name) As total_savings_by_subpolicy,
SUM(CO2_savings) OVER(PARTITION BY building_name) As total_savings_by_building_name,
SUM(CO2_savings) OVER(PARTITION BY floor_name) As total_savings_by_floor_name
From #CO2temp
RETURN
[/code]

nt86
Yak Posting Veteran

54 Posts

Posted - 2010-01-08 : 06:22:56

Visakh16,

Thanks for the response and the code, the output im getting from that SELECT statement isn't what I exactly need. My problem is im using MS Chart Controls and they cant seem to process multiple result sets so thats why im looking to do this in 1 SELECT Statement, Below is sample Data, If you run this you can see the output which i need but preferably i need to this in 1 SELECT statement, maybe its not possible im not sure.

I also put in the code you gave so you can see the output, i appreciate the help and thank you very much for the advice and help you have given me so far :)

<----------------Create Temp 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 Values in to Temp 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'

<------------------Calculate Savings------------------------------->
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)

<----------------------Group Savings --------------------------->

/** 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

/** Getting Total Savings per Room*/
Select DISTINCT room_name,
SUM(CO2_savings) OVER(PARTITION BY subpolicy_name) As total_savings_by_subpolicy,
SUM(CO2_savings) OVER(PARTITION BY building_name) As total_savings_by_building_name,
SUM(CO2_savings) OVER(PARTITION BY floor_name) As total_savings_by_floor_name
From #CO2temp

RETURN

niall

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-08 : 10:42:40

ok i dont have sqlbox here. will compare and give soln tomorrow.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-01-08 : 11:17:39

Your expected output seems to be confusing...can you show us what the output of the 'single' SELECT should look like (for the sample data posted above).

nt86
Yak Posting Veteran

54 Posts

Posted - 2010-01-08 : 11:32:49

quote:
Originally posted by vijayisonly

Your expected output seems to be confusing...can you show us what the output of the 'single' SELECT should look like (for the sample data posted above).

Thanks for replying guys.'Vijayisonly' i have 4 select statements because i need to group by subpolicy, building, floor, room and have totals for them. The output for a single select statement the first one for example is, it shows the policy name and savings,

subpolicy_name total_savings
Exempt 46.8
Finance 51.48
IT 18.72
Manage 14.04
Sales 9.36
Test 18.72

niall

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-01-08 : 11:38:17

Yeah..I understand that....but when you say..you would want all the results in a single SELECT statement..how would that result look like.

nt86
Yak Posting Veteran

54 Posts

Posted - 2010-01-08 : 15:08:38

quote:
Originally posted by vijayisonly

Yeah..I understand that....but when you say..you would want all the results in a single SELECT statement..how would that result look like.

Basically i need to achieve the same results but without using 4 Select statements just use one instead, The Chart controls im using cant seem to process multiple result sets so i need to just use one select statement, maybe im trying to do the impossible im not sure :(

The result needs to show all my policies etc and there total, so for example:
Exempt 46.8
Finance 51.48

I don't want
Exempt 20.0
Exempt 26.8
Finance 25.0
Finance 26.48

I need totals , similarly for Building, Floor, Room. I've been trying to get it working for a while but I've only been using Stored Procedures for a short while so still only getting used to them, whenever i tried I couldn't manage to get the totals correctly.

niall

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources