case clause in query
How can use the case clause in the following query ?
data in the table like this.
userid...vdate....video1..........video2......vide o3.............video4.....to.....video14
----------------------------------------------------------------------------------------
Martin..5/2/2005..Office Safety..Road Safety.....................
Martin..8/2/2005..--------....................Electrical Safety.................
John....2/2/2005.........------...............Electrical Safety...............
John....4/2/2005..Office Safety..Road Safety...............
Peter...5/2/2005..Office Safety.............---------------------Back Safety.....
Peter...6/2/2005.......---------..............Eelectrical Safety......
I use query like this.
select userid, vdate,
video1 = case when video1 is null then 'not completed' else 'completed' end,
video2 = case when video2 is null then 'not completed' else 'completed' end,
video3 = case when video3 is null then 'not completed' else 'completed' end,
video4 = case when video4 is null then 'not completed' else 'completed' end
from
(select userid, vdate = max(vdate), video1=max(video1), video2=max(video2), video3=max(video3),video4=max(video4)
from pvideo
group by userid
) t
Query result like this
userid...vdate....video1..........video2......vide o3.......video4......to..video14
--------------------------------------------------------------------------------
Martin..5/2/2005..completed.....completed...completed.....comp leted....completed...
John....2/2/2005..completed.....completed...completed.....comp leted....completed...
Peter...5/2/2005..completed.....completed---completed.....completed....completed....
I want that if user not view the video it should display not completed and if user view the video it diplay completed in
the respective fields how ?
regards
Mateen
|
