CODEWITHSUNDEEP
sqlsql-server-2008
sd_dracula
sd_dracula

Reputation: 3896

sql IF in stored procedure

I have a stored procedure that pulls data from 2 different on 2 different servers as follows:

SELECT 
        LocalDB.Record_ID,  
        LocalDB.Total/1440 as TotalTime,
        ((REMOTE_ACTIVE.Time_Adjusted) - (REMOTE_ACTIVETimes.CALCTimeSec)) as TimeLeft,
        LocalDB.isManualEntry
        FROM LocalDatabase.dbo.Records LocalDB left join
                   [RemoteServer].[Reporting].[dbo].[ActiveWO] REMOTE_ACTIVE ON
                   LocalDB.WO_ID = REMOTE_ACTIVE.[PO_ID]
                   left join [RemoteServer].[Reporting].[dbo].[ActiveWOTimes] REMOTE_ACTIVETimes ON
            LocalDB.WO_ID = REMOTE_ACTIVETimes.[PO_ID]

What can happen is that sometimes the "TimeLeft" value can be 0. When that happens I want to replace that value with something like 

IF(TimeLeft is 0 or null)
(getdate() - LocalDB.CreatedDate) as TimeElapsed

The thing is, I'm not sure how to implement such an IF statement or if it is even possible.

Upvotes: 0

Views: 231

Answers (2)

Mudassir Hasan
Mudassir Hasan

Reputation: 28771

You can use a Subquery

Select RecordID,TotalTime,TimeLeft,IsManualEntry,
       CASE WHEN TimeLeft=0 OR TimeLeft is null THEN DATEDIFF(day, GETDATE(), CreationDate)
            ELSE  CreationDate END AS TimeElapsed,

FROM (
        SELECT  LocalDB.CreatedDate As CreationDate
        LocalDB.Record_ID as RecordID,  
        LocalDB.Total/1440 as TotalTime,
        ((REMOTE_ACTIVE.Time_Adjusted) - (REMOTE_ACTIVETimes.CALCTimeSec)) as TimeLeft,
        LocalDB.isManualEntry as IsManualEntry
        FROM LocalDatabase.dbo.Records LocalDB left join
                   [RemoteServer].[Reporting].[dbo].[ActiveWO] REMOTE_ACTIVE ON
                   LocalDB.WO_ID = REMOTE_ACTIVE.[PO_ID]
                   left join [RemoteServer].[Reporting].[dbo].[ActiveWOTimes]  
                   REMOTE_ACTIVETimes ON
                   LocalDB.WO_ID = REMOTE_ACTIVETimes.[PO_ID]
      ) Z

More about DATEDIFF

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271003

Just add a case statement:

SELECT LocalDB.Record_ID,  
       LocalDB.Total/1440 as TotalTime,
       (case when REMOTE_ACTIVE.Time_Adjusted - REMOTE_ACTIVETimes.CALCTimeSec <> 0
             then REMOTE_ACTIVE.Time_Adjusted - REMOTE_ACTIVETimes.CALCTimeSec
             else getdate() - LocalDB.CreatedDate
        end) as TimeLeft,
       LocalDB.isManualEntry
FROM LocalDatabase.dbo.Records LocalDB left join
     [RemoteServer].[Reporting].[dbo].[ActiveWO] REMOTE_ACTIVE
     ON LocalDB.WO_ID = REMOTE_ACTIVE.[PO_ID] left join
     [RemoteServer].[Reporting].[dbo].[ActiveWOTimes] REMOTE_ACTIVETimes
     ON LocalDB.WO_ID = REMOTE_ACTIVETimes.[PO_ID];

I assume in the question that TimeElapsed is the replacement for TimeLeft.

Upvotes: 2

Related Questions