select distinct tbl.emp_code, b.EmplName, convert(varchar, tbl.punched_date,101) as Punched_date,
(select convert(varchar(8),itbl.PUNCHED_TIME, 114)+', '
from Trn_Capture_Data_New itbl
where itbl.EMP_CODE=tbl.emp_code and itbl.PUNCHED_DATE=tbl.punched_date and itbl.EMP_CODE='4'
and tbl.punched_date >= '03/27/2013' and tbl.punched_date <= '03/28/2013'
for xml path('')) Punched_Time
from Trn_Capture_Data_New tbl, mst_Employee b
where tbl.EMP_CODE = b.Id
and tbl.punched_date >= '03/27/2013' and tbl.punched_date <= '03/28/2013'
and b.Id='4'
group by tbl.emp_code, b.EmplName, tbl.punched_date
order by Punched_date
i have the above query and that gives me result
emp_code |
EmplName |
Punched_date |
Punched_Time |
4 |
XXX |
3/27/2013 |
14:22:00, 09:44:00, 10:38:00, 11:01:00, 16:12:00, 18:25:00, |
4 |
XXX |
3/28/2013 |
13:14:00, 09:49:00, 18:02:00, |
Now i need to Bind it to gridview, the gridview columns should be
Date,IN,OUT,IN,OUT
In punched time the first one should be IN and second OUT. A employee would have punched several time a day. Based on that i should take maximum punches and bring that of columns as IN and OUT in gridvew dynamically. How to achieve this?
Thanks in advance