Last post Jun 08, 2011 03:06 AM by yrb.yogi
Jun 08, 2011 03:03 AM|joeyan|LINK
I developing a roster application (asp.net with VB + sql server) to let user input shift duty record, proposed screen as follows:
for the database design, each staff will have one record per day.
staff_key, shift_date, start_time, end_time, shift_patten, SL, VL, ML, PH, APH, etc.
123, 23-5-2011, 9:00, 17:00, A, N, N, N, N, N
123, 24-5-2011, 12:00, 19:00, B, N, N, N, N, N
123, 25-5-2011, 12:00, 19:00, B, N, N, N, N, N
I know I need to use pivot table and then bind it to gridview
such as (Peter, COII) 23/5 in column 1, (Peter, COII) 24/5 in column 2..... for the row 1
sql server statement:
select troster.staff_key, shift_date, start_time, end_time, shift_type, SL, VL, ML, PH, APH from troster right join hris_leave.dbo.tstaff on hris_leave.dbo.tstaff.staff_key=troster.staff_key where troster.shift_date in ('5/23/2011', '5/24/2011', '5/25/2011',
'5/26/2011', '5/27/2011', '5/28/2011', '5/29/2011')
But i still no idea how to amend/write the above sql to become pivot table and fucntion to bind the gridview. Pls help.
Jun 08, 2011 03:06 AM|yrb.yogi|LINK
Basically you will require to merge the gridview rows/columns as per your datasource
so look this solved thread where lots of example available