IF Statement - SQL Server Forums

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

IF Statement

Author

Topic

nt86
Yak Posting Veteran

54 Posts

Posted - 2009-12-02 : 10:31:15

I have a temp table which is being populated from a VIEW. Im trying to write an IF statement where it will get 2 fields from the temp table shutdown, startup and calculate the difference between them and subsequently update 2 fields in the temp table, the hours_off_day field or hours_off_night field. Im just having problems with the IF statement at the moment, would greatly appreciate any help, Im getting an error at the moment in my code, the error is "incorrect syntax near keyword ELSE and SELECT"

ALTER PROCEDURE dbo.SP_SAVINGS_REPORT

/**@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),
total_hrs_off int,
pc_kwh_rate int,
hours_off_day int,
day_hour_rate float,
hours_off_night int,
night_hour_rate float,
total_savings int
)

insert into #savingstemp (pc_profile_id, shutdown_Time, hibernate_Time, sleep_Time, startup_Time, status, day_hour_rate, night_hour_rate)
SELECT PC_PROFILE_ID, SHUTDOWN_TIME, HIBERNATE_TIME, SLEEP_TIME, STARTUP_TIME, STATUS, DAY_RATE, NIGHT_RATE
FROM VIEW_TEMP

IF select startup_Time, shutdown_Time from #savingstemp where DATEPART(hh, startup_Time) >= 7 AND DATEPART(hh, shutdown_Time) <= 23
Begin
UPDATE #savingstemp
SET hours_off_day = DATEDIFF(HOUR, shutdown_Time, startup_Time)
WHERE STATUS = 'CLOSED';
End --IF

ELSE IF select startup_Time, shutdown_Time from #savingstemp where DATEPART(hh, startup_Time) <= 7 AND DATEPART(hh, shutdown_Time) >= 23
Begin
UPDATE #savingstemp
SET hours_off_night = DATEDIFF(HOUR, shutdown_Time, startup_Time)
WHERE STATUS = 'CLOSED';
End --IF

/** Return data from Temp Table*/
SELECT * FROM #savingstemp

RETURN

niall

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-12-02 : 11:43:06

Can you removethe IF completely and try this once...

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

nt86
Yak Posting Veteran

54 Posts

Posted - 2009-12-03 : 10:13:32

Thanks for the reply, thats works well but it only updates the hours_off_day field

quote:

Originally posted by vijayisonly

Can you removethe IF completely and try this once...

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

niall

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-12-03 : 10:42:36

Are you sure you have cases with this condition

quote:
WHERE DATEPART(hh, startup_Time) <= 7 AND DATEPART(hh, shutdown_Time) >= 23 AND STATUS = 'CLOSED'

If so, then I dont see why it shouldn't update.

nt86
Yak Posting Veteran

54 Posts

Posted - 2009-12-03 : 10:53:21

Im not sure I understand, Currently Im just using the code you gave me, im using 'Case' instead of 'If'as you mentioned, its is executing fine but only updating the hours_off_day field. Am i missing something? Thanks

quote:
Originally posted by vijayisonly

Are you sure you have cases with this condition
quote:
WHERE DATEPART(hh, startup_Time) <= 7 AND DATEPART(hh, shutdown_Time) >= 23 AND STATUS = 'CLOSED'

If so, then I dont see why it shouldn't update.

niall

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-12-03 : 10:59:25

According to your "IF" condition, hours_off_night will be updated only when DATEPART(hh, startup_Time) <= 7 AND DATEPART(hh, shutdown_Time) >= 23 for cases WHERE STATUS = 'CLOSED'

Is that correct?

run this against ur table..

SELECT * FROM #savingstemp
WHERE DATEPART(hh, startup_Time) <= 7 AND DATEPART(hh, shutdown_Time) >= 23 AND STATUS = 'CLOSED'

If you dont get any rows returned, then the field hours_off_night won't get updated.

nt86
Yak Posting Veteran

54 Posts

Posted - 2009-12-03 : 11:13:25

It works now, Your code was right all along, I just made a stupid mistake, working with stored procedures isn't much fun :P thanks for your help appreciate it :)

quote:
Originally posted by vijayisonly

According to your "IF" condition, hours_off_night will be updated only when DATEPART(hh, startup_Time) <= 7 AND DATEPART(hh, shutdown_Time) >= 23 for cases WHERE STATUS = 'CLOSED'

Is that correct?

run this against ur table..

SELECT * FROM #savingstemp
WHERE DATEPART(hh, startup_Time) <= 7 AND DATEPART(hh, shutdown_Time) >= 23 AND STATUS = 'CLOSED'

If you dont get any rows returned, then the field hours_off_night won't get updated.

niall

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-12-03 : 12:00:55

np..ur welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources