Translation Please? - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Translation Please?

Author

Topic

lauramayer
Posting Yak Master

152 Posts

Posted - 2004-04-21 : 10:45:23

Good morning,

I have been working on converting an application from Access to SQL 2K. I've found a statement that I can't decipher:

Expr1: IIf([dbo_tblTrailerRouteCurrentHistory].[Status]=[tblTempYardCheck].[Status],IIf(IsNull([dbo_tblTrailerRouteCurrentHistory].[MinutesSinceLastStatusChange]),0,[dbo_tblTrailerRouteCurrentHistory].[MinutesSinceLastStatusChange]+DateDiff("n",[dbo_tblTrailerRouteCurrentHistory].[TimeStamp],[tblTempYardCheck].[timestamp])),0)

How would I write this in SQL?

Thanks for the help.

Laura

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2004-04-21 : 11:04:59

the SQL equivalent of IFF is CASE.

search here for IFF and CASE....and you'll get some examples.

lauramayer
Posting Yak Master

152 Posts

Posted - 2004-04-21 : 11:28:28

Okay I come up with this:

case when TrailerRoutes.dbo.tblTrailerRouteCurrentHistory.Status = tblTempYardCheck.Status then
case when TrailerRoutes.dbo.tblTrailerRouteCurrentHistory.MinutesSinceLastStatusChange IS NULL THEN '0'
else TrailerRoutes.dbo.tblTrailerRouteCurrentHistory.MinutesSinceLastStatusChange+ DATEDIFF( n , TrailerRoutes.dbo.tblTrailerRouteCurrentHistory.TimeStamp, tblTempYardCheck.timestamp )
end
end

I keep erroring out, says incorrect syntax near case.

Laura

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2004-04-21 : 12:08:19

aliased to make it easier to read
dbo_tblTrailerRouteCurrentHistory = a
tblTempYardCheck = b

case when a.Status = b.Status then
(case when a.MinutesSinceLastStatusChange is not null then
a.MinutesSinceLastStatusChange + DateDiff(n,a.TimeStamp],b.[timestamp])
else 0
end)
else 0 end

mtomeo
Starting Member

30 Posts

Posted - 2004-04-21 : 12:10:34

Try This:

case 
   when TrailerRoutes.dbo.tblTrailerRouteCurrentHistory.MinutesSinceLastStatusChange IS NULL THEN '0' 
   when TrailerRoutes.dbo.tblTrailerRouteCurrentHistory.Status = tblTempYardCheck.Status then
	TrailerRoutes.dbo.tblTrailerRouteCurrentHistory.MinutesSinceLastStatusChange + DATEDIFF( n , TrailerRoutes.dbo.tblTrailerRouteCurrentHistory.TimeStamp, tblTempYardCheck.timestamp )
end

I thought you could nest Case statements, but maybe not. You may want to add an Else. Also, your first Case (...Status = ...Status) looks like a table join...maybe that would go better in your Where clause, but w/out seeing the entire SQL Statement who knows....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources