Time and Attendance records

Arunkumar Emm

15y4.9k.NET

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?...

9 Replies

Know the answer? Post it — somebody with the same question will find it here.

Satyapriya Nayak15y ago
Accepted answer
Hi Arun,

Here is your solution,Run 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.empCode,a.date,a.[time] AS intime,b.outtime FROM   employee A LEFT JOIN ( SELECT empCode,date,[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
0
Arunkumar Emm15y ago
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 < '13:30:00')) AS b LEFT OUTER JOIN
                          (SELECT emp_code, dates, times AS break_in
                            FROM punch_details
                            WHERE (times > '13:30:00') and (times < '14:30:00')) AS c
                            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 < '13:00:00')

Myself little confused with my query. lol. How do i proceed?..
0
Arunkumar Emm15y ago
Hi satya ,

I got it. Thanks for helping me.
0
Arunkumar Emm15y ago
Hi Satya ,

Wow. That really worked. Thanks alot.
0
Arunkumar Emm15y ago
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
0
Arunkumar Emm15y ago
Hi Dorababu ,

I would like to show it in the webpage.. Using GridView control..
0
Satyapriya Nayak15y ago
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
0
Dorababu Meka15y ago
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
0
Dorababu Meka15y ago
Will you show it in sql or on web page on some controls
0