iam having table following details
id statusid logtime 100238 1 2011-07-07 03:48:43.000 100238 2 2011-07-07 03:48:46.000 100238 1 2011-07-07 09:07:57.000 100238 2 2011-07-07 16:12:28.000 100238 1 2011-07-08 02:59:57.000 100238 2 2011-07-08 03:00:00.000 100238 1 2011-07-08 09:26:37.000 100238 2 2011-07-08 14:03:05.000 and required output should like
repid clockin clockout 100238 2011-07-07 03:48:43.000 2011-07-07 03:48:46.000 100238 2011-07-07 09:07:57.000 2011-07-07 16:12:28.000 100238 2011-07-08 02:59:57.000 2011-07-08 03:00:00.000 100238 2011-07-08 09:26:37.000 2011-07-08 14:03:05.000 ie.. if statusid 1 logtime has in clockin column , if statusid 2, logtime has in clockout column
i have used query like
select repid, clockin, clockout (select a.eventid, a.repid, a.logtime, case when a.statusid = 1 a.logtime else null end clockin, case when b.statusid = 2 b.logtime else null end clockout tbl_ets_reptimelog left join tbl_ets_reptimelog b on a.repid = b.repid)c and results like
repid clockin clockout 100238 2011-07-07 03:48:43.000 null 100238 2011-07-07 03:48:43.000 2011-07-07 03:48:46.000 100238 2011-07-07 09:07:57.000 null 100238 2011-07-07 09:07:57.000 2011-07-07 16:12:28.000 100238 2011-07-08 02:59:57.000 null 100238 2011-07-08 02:59:57.000 2011-07-08 03:00:00.000 100238 2011-07-08 09:26:37.000 null 100238 2011-07-08 09:26:37.000 2011-07-08 14:03:05.000 how remove row appears null valu ein clockout column
please out of problem......
just add clause in outer query
select repid,clockin,clockout from(select a.eventid, a.repid, a.logtime, case when a.statusid = 1 a.logtime else null end clockin, case when b.statusid = 2 b.logtime else null end clockout tbl_ets_reptimelog left join tbl_ets_reptimelog b on a.repid = b.repid)c clockout not null 
Comments
Post a Comment