locked
Calculate Delay and Duration between login and logout time RRS feed

  • Question

  • User920096554 posted

    I have created a master page on which employee can search their login report of any month when employee search between two dates it capture data from tblAttendancedetails and show it on the grid view my problem is that i want to show two more columns to the grid view named "delay" and "duration" by calculating delay and duration from existing data can anyone help me how to do that?? Here is my c# code...

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Web;
    using System.Web.UI;
    using System.Web.UI.WebControls;
    using System.Data;
    using System.Data.SqlClient;
    using System.Configuration;
    namespace Eas2
    {
        public partial class Report : System.Web.UI.Page
        {
            protected void Page_Load(object sender, EventArgs e)
            {
                if(!IsPostBack)
                {
                    lblid.Text = Session["User"].ToString().Split('^')[1];
                    GridView1.Visible = false;
                }
            }
    
            protected void btnsearch_Click(object sender, EventArgs e)
            {
                using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["REGDataConnectionString"].ConnectionString))
                {
                    using (SqlCommand cmd = new SqlCommand("SELECT EmployeeId,LoginDate,Logintime,Logoutime,Remarks FROM tblAttendanceDetails where EmployeeId=' " + Session["User"].ToString().Split('^')[0] + " ' and LoginDate BETWEEN Convert(DateTime,'" + TextBoxstart.Text + "',101) AND Convert(DateTime,'" + TextBoxend.Text + "',101)"))
                    {
                        using (SqlDataAdapter sda = new SqlDataAdapter())
                        {
                            cmd.Connection = con;
                            sda.SelectCommand = cmd;
                            using (DataTable dt = new DataTable())
                            {
                                sda.Fill(dt);
                                GridView1.DataSource = dt;
                                GridView1.DataBind();
                                GridView1.Visible = true;
                            }
                        }
                    }
                }
            }
        }
    }

    Tuesday, July 11, 2017 5:20 AM

Answers

  • User-707554951 posted

    Hi explosiveskull,

    Working code as below:

    tblAttendanceDetails datatable as below:

    Code as below:

       <asp:TextBox ID="TextBoxstart" runat="server"></asp:TextBox>
            <asp:TextBox ID="TextBoxend" runat="server"></asp:TextBox>        
            <asp:Button ID="btnsearch" runat="server" Text="Search" OnClick="btnsearch_Click" />
            <asp:GridView ID="GridView1" runat="server"  OnRowDataBound="GridView1_RowDataBound"></asp:GridView>

    CodeBehind:

     protected void Page_Load(object sender, EventArgs e)
            {
                if (!IsPostBack)
                {               
                    GridView1.Visible = false;
                }
    
            }
    
            protected void btnsearch_Click(object sender, EventArgs e)
            {
                int id = 1;
                using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["NorthWind"].ConnectionString))
                {
                    using (SqlCommand cmd = new SqlCommand("SELECT EmployeeId,LoginDate,Logintime,Logoutime,Remarks FROM tblAttendanceDetails where EmployeeId=' " + id + " ' and LoginDate BETWEEN Convert(DateTime,'" + TextBoxstart.Text + "',101) AND Convert(DateTime,'" + TextBoxend.Text + "',101)"))
                    {
                        using (SqlDataAdapter sda = new SqlDataAdapter())
                        {
                            cmd.Connection = con;
                            sda.SelectCommand = cmd;
                            using (DataTable dt = new DataTable())
                           {                          
                                sda.Fill(dt);
                                int i = dt.Rows.Count;
                                GridView1.DataSource = dt;
                                GridView1.DataBind();
                                GridView1.Visible = true;
                            }
                        }
                    }
                }
            }
    
            protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
            {
                if (e.Row.RowType == DataControlRowType.Header)
                {
                    TableHeaderCell NewCell = new TableHeaderCell();
                    NewCell.Text = "Delay";
                    e.Row.Cells.Add(NewCell);
                     NewCell = new TableHeaderCell();
                    NewCell.Text = "Duration";
                    e.Row.Cells.Add(NewCell);
                }
    
    
                if (e.Row.RowType == DataControlRowType.DataRow)
                {
    
                    string Logintime = e.Row.Cells[2].Text;
                    string Logoutime= e.Row.Cells[3].Text;
                    DateTime Logintimedt = Convert.ToDateTime(Logintime);
                    DateTime Logoutimedt = Convert.ToDateTime(Logoutime);
                    DateTime today = DateTime.Today.AddHours(9).AddMinutes(20);
                    TimeSpan diff = Logoutimedt - Logintimedt;
                    TimeSpan delay = Logintimedt - today;
                    TableCell NewCell = new TableCell();
                    NewCell.ID = "NewCell1";
                    NewCell.Text = delay.ToString();
                    e.Row.Cells.AddAt(5, NewCell);
    
                    NewCell = new TableCell();
                    NewCell.ID = "NewCell2";                           
                    NewCell.Text = diff.ToString();
                    e.Row.Cells.AddAt(6, NewCell);
    
                }
    
            }

    Output:

    Best regards

    Cathy

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, July 12, 2017 9:12 AM

All replies

  • User1970566204 posted

    sda.Fill(dt); GridView1.DataSource = dt;

    hi,

    may be u requirement like below.. !!???

     sda.Fill(dt);
    //1.add two columns in datatable......
    //dt.columns.add("delay")
    //dt.columns.add("duration")
    //2.using loop
    //foreach(DataRow dr in dt.rows)
    //{
    // dr["delay"]=yourcalucation;
    // dr["duration"]=yourcalucation;
    // }
    GridView1.DataSource = dt;

    Tuesday, July 11, 2017 10:31 AM
  • User475983607 posted

    named "delay" and "duration" by calculating delay and duration from existing data can anyone help me how to do that??

    It is not clear what delay and duration mean in your application..  

    Tuesday, July 11, 2017 10:59 AM
  • User753101303 posted

    Hi,

    Or you could add a computed column using https://docs.microsoft.com/en-us/dotnet/api/system.data.datacolumncollection.add?view=netframework-4.7#System_Data_DataColumnCollection_Add_System_String_System_Type_System_String_

    It seems the expression might be a bit more complex than needed as it seems you have a date  column and two time columns ? (what if the session ends on the next day ?).

    My personal preference would have been likely to just use two date/time columns (using just LogoutDateTime-LoginDateTime for the expression). Also as pointed already, seems you have a session "duration" but I'm not sure what you mean with the "delay".

    Tuesday, July 11, 2017 11:13 AM
  • User920096554 posted

    delay with respect to time 09:20 a.m  and duration is total hours between logout time and login time in a day...

    Tuesday, July 11, 2017 11:59 AM
  • User920096554 posted

    Basically on my page i am showing the report between two dates of the month of a particular employee who is logged in from tblAttendanceDetails  but i don't know how to calculate his total worked hours(duration) per day and delay while logging in with respect to 09:20 per day and show this data along with the existing data in the grid view...

    Tuesday, July 11, 2017 12:19 PM
  • User475983607 posted

    Basically on my page i am showing the report between two dates of the month of a particular employee who is logged in from tblAttendanceDetails  but i don't know how to calculate his total worked hours(duration) per day and delay while logging in with respect to 09:20 per day and show this data along with the existing data in the grid view...

    See TSQL DateTime documentation and Date funcitons.  Here is an example that assume your date fields are DateTimes.

    DECLARE @start DATETIME
    DECLARE @end DATETIME
    DECLARE @delayMark DATETIME
    
    DECLARE @delay INT
    DECLARE @duration INT
    
    --Init vars
    SET @delayMark = DATEFROMPARTS(2017, 7, 11)
    SET @start = '2017-07-11 12:14:22'
    SET @end = '2017-07-11 19:58:06'
    
    --SELECT @delayMark AS Mark, @start AS StartDate, @end AS EndDate
    
    --Caclulate the seconds between the two dates
    SELECT @delay = DATEDIFF(ss, @delayMark, @start)
    SELECT @duration = DATEDIFF(ss, @start, @end)
    
    --Display seconds and hours
    SELECT
    	@delay AS [Delay Sec], 
    	@duration AS [Duration Sec],
    	CAST(@delay AS FLOAT)/(60*60) AS [Delay Hours],
    	CAST(@duration AS FLOAT)/(60*60) AS [Delay Hours]

    Results

    Delay Sec   Duration Sec Delay Hours            Delay Hours
    ----------- ------------ ---------------------- ----------------------
    44062       27824        12.2394444444444       7.72888888888889

    If your dates are not DateTime types then I suggest you rethink the design as suggested by PatriceSc (in this thread) and myself (in one of your other threads).

    Tuesday, July 11, 2017 12:38 PM
  • User1970566204 posted

    hi  explosiveskull,

    as per "mgebhard" , i created sample for your reference,

    1) various way we can reach the solution . like 

             1.a) add some db-column in table ; calculate all required values and stored in table [using function in [C# or SQL-]

             1.b)  here i attached one of way for solution.. based on your table-design and your other related post

    select 
           CAST(LoginDate as DATE) LoginDate,
           cast(LoginTime as Time) LoginTime,
           cast(LogoutTime as Time) LogoutTime,   
           
           --find the login type  !?? present ,expected, delay                   
    	   case when cast(LoginTime as Time)>N'9:30' then 'Late' 
           ELSE
               case when (cast(LoginTime AS Time ))>N'9:20' and (cast(LoginTime AS Time ))<=N'9:30' then 'Expected' 
               ELSE
                   case when (cast(LoginTime AS Time ))<=N'9:20' then 'Present' END
               END
            END LoginType,
            
            --find the duration
           (CAST(DATEDIFF(SECOND,cast(LoginTime as Time),cast(LogoutTime as Time))
                 as float)/(60*60))/1.666666666666667 as 'Duration [hh:mm]',
            
            --find the delay                                 
           case when (cast(LoginTime AS Time ))>N'9:30' then       
                (cast(datediff(second,cast('9:30'as time),cast(LoginTime as time)) as float)/(60*60))/1.666666666666667
                 ELSE 0 END as 'Late Hour  [hh:mm]'
                                       
      from tblAttendanceDetails where EmployeeId='SomeInput' 
      
    
    

    Wednesday, July 12, 2017 7:10 AM
  • User-707554951 posted

    Hi explosiveskull,

    Working code as below:

    tblAttendanceDetails datatable as below:

    Code as below:

       <asp:TextBox ID="TextBoxstart" runat="server"></asp:TextBox>
            <asp:TextBox ID="TextBoxend" runat="server"></asp:TextBox>        
            <asp:Button ID="btnsearch" runat="server" Text="Search" OnClick="btnsearch_Click" />
            <asp:GridView ID="GridView1" runat="server"  OnRowDataBound="GridView1_RowDataBound"></asp:GridView>

    CodeBehind:

     protected void Page_Load(object sender, EventArgs e)
            {
                if (!IsPostBack)
                {               
                    GridView1.Visible = false;
                }
    
            }
    
            protected void btnsearch_Click(object sender, EventArgs e)
            {
                int id = 1;
                using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["NorthWind"].ConnectionString))
                {
                    using (SqlCommand cmd = new SqlCommand("SELECT EmployeeId,LoginDate,Logintime,Logoutime,Remarks FROM tblAttendanceDetails where EmployeeId=' " + id + " ' and LoginDate BETWEEN Convert(DateTime,'" + TextBoxstart.Text + "',101) AND Convert(DateTime,'" + TextBoxend.Text + "',101)"))
                    {
                        using (SqlDataAdapter sda = new SqlDataAdapter())
                        {
                            cmd.Connection = con;
                            sda.SelectCommand = cmd;
                            using (DataTable dt = new DataTable())
                           {                          
                                sda.Fill(dt);
                                int i = dt.Rows.Count;
                                GridView1.DataSource = dt;
                                GridView1.DataBind();
                                GridView1.Visible = true;
                            }
                        }
                    }
                }
            }
    
            protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
            {
                if (e.Row.RowType == DataControlRowType.Header)
                {
                    TableHeaderCell NewCell = new TableHeaderCell();
                    NewCell.Text = "Delay";
                    e.Row.Cells.Add(NewCell);
                     NewCell = new TableHeaderCell();
                    NewCell.Text = "Duration";
                    e.Row.Cells.Add(NewCell);
                }
    
    
                if (e.Row.RowType == DataControlRowType.DataRow)
                {
    
                    string Logintime = e.Row.Cells[2].Text;
                    string Logoutime= e.Row.Cells[3].Text;
                    DateTime Logintimedt = Convert.ToDateTime(Logintime);
                    DateTime Logoutimedt = Convert.ToDateTime(Logoutime);
                    DateTime today = DateTime.Today.AddHours(9).AddMinutes(20);
                    TimeSpan diff = Logoutimedt - Logintimedt;
                    TimeSpan delay = Logintimedt - today;
                    TableCell NewCell = new TableCell();
                    NewCell.ID = "NewCell1";
                    NewCell.Text = delay.ToString();
                    e.Row.Cells.AddAt(5, NewCell);
    
                    NewCell = new TableCell();
                    NewCell.ID = "NewCell2";                           
                    NewCell.Text = diff.ToString();
                    e.Row.Cells.AddAt(6, NewCell);
    
                }
    
            }

    Output:

    Best regards

    Cathy

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, July 12, 2017 9:12 AM
  • User920096554 posted

    Thank you Cathy.... it worked i need one more favor i cannot add remarks into my tblAttendanceDetails can you tell me what is wrong with my code...Actually when user is marking attendance after 09:20 have to put some remarks regarding late attendance....here is my code...

    
    
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Web;
    using System.Web.UI;
    using System.Web.UI.WebControls;
    using System.Data;
    using System.Data.SqlClient;
    using System.Configuration;
    namespace Eas2
    {
        public partial class Attendance : System.Web.UI.Page
        {
            protected void Page_Load(object sender, EventArgs e)
            {
                if (!IsPostBack)
                {
                    lblAttendance.Text = Session["user"].ToString().Split('^')[1];
                    lblDate.Text = DateTime.Now.ToString("dd/MM/yyyy");
                    
                    
                }
            }
     
            protected void btnLogin_Click(object sender, EventArgs e)
            {
                // int id = 0;
                TimeSpan logintime = System.DateTime.Now.TimeOfDay;
                TimeSpan time = TimeSpan.Parse("09:20:00.000");
                DateTime ltime = DateTime.Parse("07/07/2017 09:30:00.000");
     
                if (logintime > time && TextBoxRemarks.Text.ToString() == String.Empty)
                {
                    
                    SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["REGDataConnectionString"].ConnectionString);
                    cn.Open();
                    SqlCommand cd = new SqlCommand("insert into tblAttendanceDetails(Remarks)values(@rem)", cn);
                    cd.Parameters.AddWithValue("@rem", TextBoxRemarks.Text);
                    cd.ExecuteNonQuery();
                }
     
                
                if (btnLogin.Text == "Login(Daily Attendance)")
                {
     
                    SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["REGDataConnectionString"].ConnectionString);
                    con.Open();
                    SqlCommand cmd = new SqlCommand("Track_UserLog", con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@EmployeeId", Session["user"].ToString().Split('^')[0]);
                    
                    cmd.ExecuteNonQuery();
                    con.Close();
                    lblLogin.Text = System.DateTime.Now.ToString("hh:mm");
                    btnLogin.Text = "Logout(Daily Attendance)";
     
                }
                else if (btnLogin.Text == "Logout(Daily Attendance)")
                {
                    SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["REGDataConnectionString"].ConnectionString);
                    con.Open();
                    SqlCommand cmd = new SqlCommand("Track_logout", con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@EmployeeId", Session["user"].ToString().Split('^')[0]);
                    
                    cmd.ExecuteNonQuery();
                    
                    lblLogout.Text = System.DateTime.Now.ToString("hh:mm");
                    btnLogin.Text = "Login(Daily Attendance)";
                }
     
            }
     
        }
    }


     

    Wednesday, July 12, 2017 1:46 PM