samedi 17 octobre 2015

Sqlite nested case query

He I am trying to build sql query with multiple nested case querys.

My table has this columns: ID,UserId, EventInTime, InTime, EventOutTime, OutTime.

Here is pseudo code for column that troubles me:

(If EventInTime ==0 then IT = InTime else IT = EventInTime;
If EventOutTime ==0 then IT = OutTime else OT = EventOutTime;
If IT or OT ==0 then 0 else OT-IT) as Suma

Heres my code:

SELECT 
UserId, 
StatId, 
case when 
    case when EventInTime =0 
    then InTime 
    else EventInTime end=0 
    or 
        case when EventOutTime =0 
        then OutTime 
        else EventOutTime end 
        then 0
 else 
    case when EventOutTime =0 
    then OutTime 
    else EventOutTime end -
        case when EventInTime =0 
        then InTime 
        else EventInTime end 
        as suma
from Worktimes

What am I doing wrong?

Aucun commentaire:

Enregistrer un commentaire