How to do calculation inside the CASE statement

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

How to do calculation inside the CASE statement

Author

Topic

winodjadhav
Starting Member

1 Post

Posted - 2012-06-14 : 05:02:51

How to do calculation inside the CASE statement in sql Query, I have the following query kindly tell me how to do?

Finally I want the value should assign the column is truncate the string
@Hours,@Minute,@FinalSecound

select
job.jobid,
SubmittedDate,
CompletedDate,
CASE
WHEN Qu.ElapseTime IS NOT NULL
THEN
BEGIN
DECLARE @Secound BIGINT,@Minute VARCHAR(250),@Hours VARCHAR(250),@FinalSecound INT,@LastSecound AS VARCHAR(250)
SET @Secound = CAST (DATEDIFF (ss,CompletedDate,SubmittedDate) AS BIGINT);
SET @FinalSecound = CAST(@Secound - TotalSecound) AS INT
SET @Hours = @FinalSecound / 3600
SET @Minute =(@FinalSecound - (@Hours * 3600)) / 60
SET @LastSecound = (@FinalSecound - (@Hours * 3600) - (@Minute * 60))
END
--CONVERT(CHAR(8),(CompletedDate - SubmittedDate)-(isnull(convert (datetime,Qu.ElapseTime),0)) ,108)
ELSE
CONVERT(CHAR(8),(CompletedDate - SubmittedDate),108)
END AS JobElapsedTime,
Qu.ElapseTime as QueryElaspsedTime,
CompletedDate
from dbo.tbl_AllJobs as job
and JobId = 34467

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-06-14 : 05:15:01

You can't declare variables in a case statement - has to be a single expression
Not sure what you are trying to do - but it's a lot more complicated thanb it needs to be.
Try it with a simpler expression and build up.
It's usually easier to work with datetimes and convert at the end if you need to - usually a convert style will do.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-14 : 12:17:20

i really didnt understand why you want intermediate values to be stored in variables as i cant see you using them thereafter. Even in expression you wrote you're directly using field elapsedtime rather than its extracted parts. may be you can explain what exactly you're trying to achieve using sample data

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources