SQL Server Performance

Select Statement problem

Discussion in 'Getting Started' started by nt86, Jan 14, 2010.

  1. nt86 New Member

    Hey,Im using SQL Server 2005 and im using stored procedures. Is this possible? i need to get a number of results and do it in one SELECT statement or just one result set. Im using MS Chart Controls for reporting and they cant seem to process multiple result sets this is my problem, so basically to run a barchart i need to assign a column to the X axis and a column to the Y axis and it will then display my results.

    My stored procedure below returns a result set with columns subpolicy_name, building_name, floor_name, room_name and total_savings. Im just wondering is it possible to split my total_savings column into 4 columns, for example subpolicy_savings, building_savings, floor_savings and room_savings, i need everything in a separate column. If you run this stored procedure you will see the result set.

    Thanks in advance for any help

    ALTER PROCEDURE dbo.TEST_SAMPLE_DATA

    AS

    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 #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'

    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)

    /**------------------------------------------------------------------*/

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


    RETURN
  2. FrankKalis Moderator

    Not sure if I understand you correctly, but are you looking for something like this?
    Select 'Policy' AS Sector,
    subpolicy_name,
    '' AS building_name,
    '' AS floor_name,
    '' AS room_name,
    SUM(CO2_savings) As subpolicy_savings
    , 0 AS building_savings
    , 0 AS floor_savings
    , 0
    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
    , 0
    , SUM(CO2_savings)
    , 0
    , 0
    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
    , 0
    , 0
    , SUM(CO2_savings)
    , 0
    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
    , 0
    , 0
    , 0
    , SUM(CO2_savings)
    From #CO2temp
    group by room_name

  3. nt86 New Member

    Frank,

    Appreciate the reply, Im not sure if its possible what Im trying to do. Unfortunately Im using MS Chart Controls which cant seem to process multiple result sets. So for example for a bar chart i need to assign a column to the X axis and a column to the Y Axis. I need to group my savings by:

    Subpolicy
    Building
    Floor
    Room

    So for example if i point to the subpolicy_name as the X axis and then the subpolicy_savings as the Y axis, my chart will pull everything in the subpolicy_savings including the 0's. My output i guess needs top be something like this but in one select statement. I've been trying to do this for a few days now, Im not sure if its possible to be honest, thanks again for the help

    /** 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
  4. FrankKalis Moderator

    Probably the most pragmatic workaround is to use a second temp table
    CREATE TABLE #Final
    (
    OrderID int IDENTITY,
    ColumnA varchar(50),
    ColumnB float
    )
    and then INSERT the resultsets of each of the 4 SELECT statement into that table
    /** Getting Total Savings per Policy*/
    INSERT INTO #Final
    Select subpolicy_name, SUM(CO2_savings) As total_savings
    From #CO2temp
    group by subpolicy_name
    Order by subpolicy_name

    /** Getting Total Savings per Building*/
    INSERT INTO #Final
    Select building_name, SUM(CO2_savings) As total_savings
    From #CO2temp
    group by building_name
    Order by building_name

    /** Getting Total Savings per Floor*/
    INSERT INTO #Final
    Select floor_name, SUM(CO2_savings) As total_savings
    From #CO2temp
    group by floor_name
    Order by floor_name

    /** Getting Total Savings per Room*/
    INSERT INTO #Final
    Select room_name, SUM(CO2_savings) As total_savings
    From #CO2temp
    group by room_name
    and then do the final SELECT against this 2nd table
    SELECT * FROM #Final F ORDER BY F.OrderID;

  5. nt86 New Member

    Frank Thanks for the reply, I've been trying to solve this for 4 days now and cant get the result i need. unfortunely im stuck using MS Chart Controls and they cant process multiple result sets. Thats why i need everything in a separate column so when the bar chart runs the X axis will b assigned to one column in my Stored procedure and the Y axis to another, it then returns all the records in these columns.I think im trying to the impossible :( thanks for the help anyway i appreciate it.

Share This Page