SQL, Incorrect syntax on CASE statement near the keyword 'FROM'

Question

I am trying to get the below code to work, individually the two pieces of code (in the WHEN part and the ELSE part) work but when used in this CASE statement I get an error

"Incorrect syntax near 'CAST', expected 'AS'." error.

Basically if the WHEN statements code is equals to or greater than 24 then use the THEN statement if its is under 24 then use the ELSE statement.

I cannot seem to get this to work after trying for several hours any indication as to where I am going wrong would be greatly appreciated.

SELECT CASE 
        WHEN 
            (convert(float,datediff(mi, start_work, end_work))/60) >= '24'
        THEN
            (convert(float,datediff(mi, start_work, end_work))/60)
        ELSE
            (CAST(convert(varchar(2), dateadd(minute, datediff(minute, start_time, end_time), 0), 114) 
            * 60 + RIGHT (convert(varchar(5), dateadd(minute, datediff(minute, start_time, end_time), 0), 114),
            CASE WHEN CHARINDEX(':',convert(varchar(5), dateadd(minute, datediff(minute, start_time, end_time), 0), 114)) > 0
            THEN LEN(convert(varchar(5), dateadd(minute, datediff(minute, start_time, end_time), 0), 114))-3
            ELSE LEN(convert(varchar(5), dateadd(minute, datediff(minute, start_time, end_time), 0), 114))
            END) AS decimal) / 60
FROM  NDB.dbo.statusa 
INNER JOIN NDB.dbo.details ON statusa.vkey = details.vkey
INNER JOIN NDB.dbo.chegu ON statusa.ckey = NDB.dbo.chegu.gkey
WHERE start_time!= end_time AND string1 = Visit_Id and NDB.dbo.chegu.name = 'loft'
     AS [Working]

Solution 1

you need to close your case statement

 case when ... then ... else ... end

Solution 2

There should be an END before the FROM clause and you should also remove( before CAST.

Solution 3

Unclosed parentheses and a case statement needed an END.

SELECT CASE 
            WHEN (convert(FLOAT, datediff(mi, start_work, end_work)) / 60) >= '24'
                THEN (convert(FLOAT, datediff(mi, start_work, end_work)) / 60)
            ELSE CAST(convert(VARCHAR(2), dateadd(minute, datediff(minute, start_time, end_time), 0), 114) * 60 
                 + RIGHT(
                        convert(VARCHAR(5), dateadd(minute, datediff(minute, start_time, end_time), 0), 114), 
                        CASE 
                            WHEN CHARINDEX(':', convert(VARCHAR(5), dateadd(minute, datediff(minute, start_time, end_time), 0), 114)) > 0
                                THEN LEN(convert(VARCHAR(5), dateadd(minute, datediff(minute, start_time, end_time), 0), 114)) - 3
                            ELSE LEN(convert(VARCHAR(5), dateadd(minute, datediff(minute, start_time, end_time), 0), 114))
                        END
                        ) AS DECIMAL) / 60
        END

FROM NDB.dbo.statusa
INNER JOIN NDB.dbo.details
    ON statusa.vkey = details.vkey
INNER JOIN NDB.dbo.chegu
    ON statusa.ckey = NDB.dbo.chegu.gkey
WHERE start_time != end_time
    AND string1 = Visit_Id
    AND NDB.dbo.chegu.NAME = 'loft' AS [Working]

Solution 4

There arent closing parenthesis.

SELECT CASE 
    WHEN 
        (convert(float,datediff(mi, start_work, end_work))/60) >= '24'
    THEN
        (convert(float,datediff(mi, start_work, end_work))/60)
    ELSE
        (CAST(convert(varchar(2), dateadd(minute, datediff(minute, start_time, end_time), 0), 114) 
        * 60 + RIGHT (convert(varchar(5), dateadd(minute, datediff(minute, start_time, end_time), 0), 114),
        CASE WHEN CHARINDEX(':',convert(varchar(5), dateadd(minute, datediff(minute, start_time, end_time), 0), 114)) > 0
        THEN LEN(convert(varchar(5), dateadd(minute, datediff(minute, start_time, end_time), 0), 114))-3
        ELSE LEN(convert(varchar(5), dateadd(minute, datediff(minute, start_time, end_time), 0), 114))
        END) AS decimal) / 60)

Solution 5

I realized this answer is too late for the reward. But your script is WAY too long. This will do exactly the same as you are trying to achieve, fixing the error in your code, is just patching bad code:

SELECT 
  datediff(mi, 0, end_time - start_time)/60.0%24
FROM  
 ....

View more solutions