Time and Attendance records

Question

Hi  I'm currently doing a time & attendance project. its like when i click the download button the code and dlls communicates with the attendance reader and fetches the datas in the following format(sql)  empCode in/outMode date time 5001 0 12/09/2011 09:05:34 5002 0 12/09/2011 09:33:13 5001 1 12/09/2011 18:05:09 5002 1 12/09/2011 17:44:34 where  in the in/out mode  o is the intime and 1 is the outtime. i want to show these datas in a single record for each employees. that is  i want to show somethng like the following  (assume that i created a new table as below)  empCode date intime outtime 5001 12/09/2011 09:05:34 18:05:09 5002 12/09/2011 09:33:13 17:44:34 How do i do that?.. can anyone pls guide me?...

Satyapriya Nayak · Answer

Hi Arun Here is your solutionRun the attachments. using System; using System.Configuration; using System.Data; using System.Linq; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.HtmlControls; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Xml.Linq; using System.Data.SqlClient; public partial class _Default : System.Web.UI.Page { string strConnString = ConfigurationManager.ConnectionStrings[ConnectionString].ConnectionString; string str; SqlCommand com; protected void Page_Load(object sender EventArgs e) { SqlConnection con = new SqlConnection(strConnString); con.Open(); str = SELECT a.empCodea.datea.[time] AS intimeb.outtime FROM employee A LEFT JOIN ( SELECT empCodedate[time] AS outtime FROM employee WHERE employee.inoutMode = 1 ) b ON a.empCode = b.empCode AND a.date = b.date WHERE a.inoutMode = 0; com = new SqlCommand(str con); SqlDataReader reader = com.ExecuteReader(); GridView1.DataSource = reader; GridView1.DataBind(); con.Close(); } } Thanks If this post helps you mark it as answer

Satyapriya Nayak · Answer

Hi ArunKumar Here is your Query SELECT A.empCode A.date A.time AS intime b.outtime FROM employee AS A LEFT OUTER JOIN (SELECT empCode date time AS outtime FROM employee WHERE (inoutMode = 1)) AS b ON A.empCode = b.empCode AND A.date = b.date WHERE (A.inoutMode = 0) Thanks If this post helps you mark it as answer

Arunkumar Emm · Answer

Hi  i tried with the above query and it worked fine with intime and outtime but now i want to do it with extra two fields i.e break_out and break_in. lets say i have  empCode date time 5001 12/09/2011 09:05:34 5002 12/09/2011 09:33:13 5001 12/09/2011 13:05:53 5002 12/09/2011 13:22:24 5001 12/09/2011 14:05:22 5002 12/09/2011 14:33:53 5001 12/09/2011 18:05:09 5002 12/09/2011 17:44:34 i want to show the above records as follows  (the intime  break_out  break_in and outtime are based on 'time') empCode date intime break_out break_in outtime 5001 12/09/2011 09:05:34 13:05:53 14:05:22 18:05:09 5002 12/09/2011 09:33:13 13:22:24 14:33:53 17:44:34 so i tried the following query but it didnt work SELECT a.emp_Code a.dates a.times AS intime b.break_out  c.break_in  d.outtime FROM punch_details AS a LEFT OUTER JOIN (((SELECT emp_code dates times AS break_out FROM punch_details WHERE (times > '13:00:00') and (times (SELECT emp_code dates times AS break_in FROM punch_details WHERE (times > '13:30:00') and (times on b.emp_code=c.emp_code and b.dates = a.dates) LEFT OUTER JOIN (SELECT emp_code dates times AS outtime FROM punch_details WHERE (times > '17:00:00')) AS d on c.emp_code=d.emp_code and c.dates = d.dates) ON A.emp_code = b.emp_code AND A.dates = b.dates WHERE (A.times > '09:00:00') and (A.times Myself little confused with my query. lol. How do i proceed?..

Arunkumar Emm · Answer

Hi satya  I got it. Thanks for helping me.

Arunkumar Emm · Answer

Hi Satya  Wow. That really worked. Thanks alot.

Arunkumar Emm · Answer

Hi dorababu  SELECT empcode  MAX ([ date ]) as [ date ] MAX ( intime ) as [ intime ] MAX ( outtime ) as [ outtime ] FROM MyTable GROUP BY empcode I dont have fields called intime and outtime in the first table. So your query not gonna work out. I think i confused u. I'll make it clear. I jus wana show the records of first table in the second table in the following format. Table2 empCode date intime outtime 5001 12/09/2011 09:05:34 18:05:09 5002 12/09/2011 09:33:13 17:44:34

Arunkumar Emm · Answer

Hi Dorababu  I would like to show it in the webpage.. Using GridView control..

Dorababu Meka · Answer

I just tried using group by and max SELECT empcode  MAX ([ date ]) as [ date ] MAX ( intime ) as [ intime ] MAX ( outtime ) as [ outtime ] FROM MyTable GROUP BY empcode

Dorababu Meka · Answer

Will you show it in sql or on web page on some controls