try  
       {  
           ConnectionString connection = new ConnectionString();  
  
           SqlConnection conn = new SqlConnection(connection.Conn);  
           conn.Open();  
           // string qryString = ";  
           SqlCommand sqlCmd = new SqlCommand("SP_SAVINGS_REPORT", conn);  
           sqlCmd.CommandType = System.Data.CommandType.StoredProcedure;  
  
            
             
           SqlDataReader dr;  
  
            
           dr = sqlCmd.ExecuteReader();  
  
           while (dr.Read())  
           {   
             
             
             
             
  
                    
                   lblTotalSavings.Text = sqlCmd.Parameters["@Savings_To_Date"].Value.ToString();  
  
           }  
           dr.Close();  
               conn.Close();  
       }  
            
      
  
       catch (Exception x)  
       {  
  
           string error = x.Message;  
  
       }  

ALTER PROCEDURE dbo.SP_SAVINGS_REPORT
	
	@Savings_To_Date float OUTPUT
	
	/**@startup_time datetime,
	@hibernate_time datetime,
	@sleep_time datetime,
	@shutdown_time datetime,
	@total_hrs_off int
    @status varchar(50)
    @pc_profile_id int,
    @day_rate int,
	@night_rate int,
	@pc_power_rating int*/

AS	
	/* Create temp table */
	CREATE TABLE #savingstemp
	(
	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),
	hours_off_day int,
	day_hour_rate float,
	hours_off_night int,
	night_hour_rate float,
	pc_kwh_rate float,
	savings float	
	)
		
	/** Insert Values into Temp Table from View, including Day/Night Rates*/
	
	insert into #savingstemp (pc_profile_id, shutdown_Time, hibernate_Time, sleep_Time, startup_Time, status, subpolicy_name, building_name, floor_name, room_name, day_hour_rate, night_hour_rate, pc_kwh_rate)
	SELECT PC_PROFILE_ID, SHUTDOWN_TIME, HIBERNATE_TIME, SLEEP_TIME, STARTUP_TIME, STATUS, SUB_POLICY_NAME, BUILDING_NAME, FLOOR_NAME, ROOM_NAME, DAY_RATE, NIGHT_RATE, PC_POWER_RATING
	FROM VIEW_SAVINGS_REPORT 
	
	
	
		/** Get hours off between Shutdown/hibernate/sleep and Startup and insert them into Hours Off Day or Hours Off Night fields*/
	UPDATE #savingstemp 
SET hours_off_day = case when DATEPART(hh, startup_Time) >= 7 AND DATEPART(hh, shutdown_Time) <= 23 
					then DATEDIFF(HOUR, shutdown_Time, startup_Time) else hours_off_day end,
	hours_off_night = case when DATEPART(hh, startup_Time) <= 7 AND DATEPART(hh, shutdown_Time) >= 23
						then DATEDIFF(HOUR, shutdown_Time, startup_Time) else hours_off_night end
WHERE STATUS = 'CLOSED';


UPDATE #savingstemp 
SET hours_off_day = case when DATEPART(hh, hibernate_Time) >= 7 AND DATEPART(hh, startup_Time) <= 23 
					then DATEDIFF(HOUR, hibernate_Time, startup_Time) else hours_off_day end,
	hours_off_night = case when DATEPART(hh, hibernate_Time) <= 7 AND DATEPART(hh, startup_Time) >= 23
						then DATEDIFF(HOUR, hibernate_Time, startup_Time) else hours_off_night end
WHERE STATUS = 'CLOSED';

UPDATE #savingstemp 
SET hours_off_day = case when DATEPART(hh, sleep_Time) >= 7 AND DATEPART(hh, startup_Time) <= 23 
					then DATEDIFF(HOUR, sleep_Time, startup_Time) else hours_off_day end,
	hours_off_night = case when DATEPART(hh, sleep_Time) <= 7 AND DATEPART(hh, startup_Time) >= 23
						then DATEDIFF(HOUR, sleep_Time, startup_Time) else hours_off_night end
WHERE STATUS = 'CLOSED';

	
	/** Calculate the Total Savings, multiple hours * KWH Rate * Rate for both Day/Night Hours off*/
	
	UPDATE #savingstemp
	SET savings = (isnull(hours_off_day, 0) * pc_kwh_rate * day_hour_rate) + (isnull(hours_off_night, 0) * pc_kwh_rate * night_hour_rate)
	

	/** Getting Total Savings per Policy*/
	Select subpolicy_name, SUM(savings) as total_savings
	From #savingstemp
	group by subpolicy_name
	
	/**Getting total Savings to Date*/
	Select SUM(savings) as savings_total
	From #savingstemp
	where savings_total = @Savings_To_Date
	
	RETURN

quote:

---

Originally posted by saralstalin

I had a look into your sproc, where are you assigning value to @Savings_To_Date?

The below section looks incorrect


/**Getting total Savings to Date*/
Select SUM(savings) as savings_total
From #savingstemp
where savings_total = @Savings_To_Date


If my understanding is correct, it should be


/**Getting total Savings to Date*/
Select @Savings_To_Date = SUM(savings)
From #savingstemp

RETURN @Savings_To_Date







Saral S Stalin

---

quote:

---

Originally posted by saralstalin

Sleepy me.... :( That code is incorrect..

try
{
ConnectionString connection = new ConnectionString();
SqlConnection conn = new SqlConnection(connection.Conn);

// string qryString = ";
SqlCommand sqlCmd = new SqlCommand();
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.Connection = conn;
sqlCmd.CommandText = "SP_SAVINGS_REPORT";

SqlParameter outputParam = new SqlParameter("Savings_To_Date", SqlDbType.Float);
outputParam.Direction = ParameterDirection.Output;
sqlCmd.Parameters.Add(outputParam);

SqlDataReader dr;

conn.Open();
dr = sqlCmd.ExecuteReader();

while (dr.Read())
{

lblTotalSavings.Text = sqlCmd.Parameters["Savings_To_Date"].Value.ToString();

}
dr.Close();
conn.Close();
}
catch (Exception x)
{
string error = x.Message;
}



Saral S Stalin

---