Risky Wahyu N Risky Wahyu N - 4 months ago 12
SQL Question

SQL select with if statement

I want select with when or if statement

select
ID, name,Departmen, date, clock_in, clock_out,
DATEDIFF(MINUTE, on_duty, clock_in) as late,
DATEDIFF(MINUTE, off_duty, clock_out) as Early,
DATEDIFF(MINUTE, on_duty, clock_in) - DATEDIFF(MINUTE, off_duty, clock_out) as total_telat when late <=30 from kkpsurabaya


I want make
total_telat
only count 1-30min

Answer

use CASE . . WHEN . . END

select  ID, 
    name,
    Departmen, date, clock_in, clock_out,
    DATEDIFF(MINUTE, on_duty, clock_in ) as late  ,
    DATEDIFF(MINUTE, off_duty, clock_out ) as Early,
    CASE 
    WHEN DATEDIFF(MINUTE, on_duty, clock_in ) < 30
    THEN DATEDIFF(MINUTE,on_duty, clock_in ) - DATEDIFF(MINUTE ,off_duty, clock_out ) 
    END as total_telat
from    kkpsurabaya