You must Sign In to post a response.
  • Category: ASP.NET

    Total hours calculation

    i would to search person_num and creation_date
    i would like to convert to locatime wherever creation_Date is available
    <form id="form1" runat="server">
    <p>
    <asp:Button ID="Button1" runat="server" Text="Button" />
    <br />
    <asp:TextBox ID="TextBox1" runat="server" OnTextChanged="TextBox1_TextChanged"></asp:TextBox>
    </p>
    <p>
     </p>
    <p>
    <asp:GridView ID="GridView1" runat="server" DataSourceID="SqlDataSource1" AllowPaging="True" AutoGenerateColumns="False">
    <Columns>
    <asp:TemplateField HeaderText="creation_date" SortExpression="creation_date">

    <ItemTemplate>
    <asp:Label ID="Label1" runat="server" Text='<%# Bind("creation_date") %>'></asp:Label>
    </ItemTemplate>
    </asp:TemplateField>
    <asp:BoundField DataField="person_num" HeaderText="person_num" SortExpression="person_num" />
    <asp:BoundField DataField="full_name" HeaderText="full_name" SortExpression="full_name" />
    <asp:BoundField DataField="CLOCK_IN1" HeaderText="CLOCK_IN1" SortExpression="CLOCK_IN1" ReadOnly="True" />
    <asp:BoundField DataField="CLOCK_OUT1" HeaderText="CLOCK_OUT1" ReadOnly="True" SortExpression="CLOCK_OUT1" />
    <asp:BoundField DataField="CLOCK_IN2" HeaderText="CLOCK_IN2" ReadOnly="True" SortExpression="CLOCK_IN2" />
    <asp:BoundField DataField="CLOCK_OUT2" HeaderText="CLOCK_OUT2" ReadOnly="True" SortExpression="CLOCK_OUT2" />
    <asp:BoundField DataField="HoursWorked" HeaderText="HoursWorked" ReadOnly="True" SortExpression="HoursWorked" />
    </Columns>
    </asp:GridView>
    <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:acoreConnectionString %>" SelectCommand="
    WITH Prep AS
    (
    SELECT
    creation_date = CAST(creation_date AS date),
    person_num,
    full_name,
    CLOCK_IN1 =
    MIN(CASE event_name WHEN 'CLOCK_IN' THEN CAST(creation_date AS TIME) END),
    CLOCK_OUT1 =
    MIN(CASE event_name WHEN 'CLOCK_OUT' THEN CAST(creation_date AS TIME) END),
    CLOCK_IN2 = NULLIF
    (
    MAX(CASE event_name WHEN 'CLOCK_IN' THEN CAST(creation_date AS TIME) END),
    MIN(CASE event_name WHEN 'CLOCK_IN' THEN CAST(creation_date AS TIME) END)
    ),
    CLOCK_OUT2 = NULLIF
    (
    MAX(CASE event_name WHEN 'CLOCK_OUT' THEN CAST(creation_date AS TIME) END),
    MIN(CASE event_name WHEN 'CLOCK_OUT' THEN CAST(creation_date AS TIME) END)
    )
    FROM dbo.test
    GROUP BY ALL
    CAST(creation_date AS date),
    person_num,
    full_name
    )
    SELECT *, HoursWorked =
    DATEDIFF(MINUTE,CLOCK_IN1,CLOCK_OUT1)/60 +
    ISNULL(DATEDIFF(MINUTE,CLOCK_IN2,CLOCK_OUT2)/60,0)
    FROM prep
    ">
    </asp:SqlDataSource>
    </p>
  • #768010
    you wan to get number of hours from two dates right ? just use DATEDIFF method with hh as out as parameters
    see below SQL snippet

    declare @date1 datetime
    declare @date2 datetime

    select @date1 = GETDATE();
    select @date2 = '2013-02-02 14:05'

    select DATEDIFF(hh, @date2, @date1)
    //or you can try below snippet also
    Declare @Date1 dateTime
    Declare @Date2 dateTime
    Set @Date1 = '22:30:00'
    Set @Date2 = '00:00:00'
    Select Cast((@Date1 - @Date2) as Float) * 24.0

    hope it helps

    Thanks
    Koolprasd2003
    Editor, DotNetSpider MVM
    Microsoft MVP 2014 [ASP.NET/IIS]

  • #768012
    Hi,

    Before doing this, Kindly check whether your server was located in same place or different time zone. This problem usually comes when you take current date or time stamp from the server. If your server is US and you are working in India. When you try to get the Current Time from the Database you will get the US Current time and when you combine that with the india standard time you wont get the result as you desire. So we need to cross check all the criteria before start implement the coding.

    Thanks,
    Mani

  • #768023
    Mani/Prasad,

    My MS SQL VIEW look like this,

    Action_Id person_num creation_date event_name
    1 1004 2016-09-04 05:34:07.000 CLOCK_IN
    3 1004 2016-09-04 07:34:07.000 CLOCK_OUT
    4 1003 2016-09-04 07:17:33.000 CLOCK_IN
    5 1003 2016-09-04 18:17:33.000 CLOCK_OUT
    6 1004 2016-09-04 09:34:07.000 CLOCK_IN
    7 1004 2016-09-04 16:34:07.000 CLOCK_OUT
    8 1006 2016-09-04 07:34:07.000 CLOCK_IN
    9 1006 2016-09-04 16:34:07.000 CLOCK_OUT

    And expected output is,
    DATE PERSON_NUM CLOCK_IN 1 CLOCK_OUT 1 CLOCK_IN 2 CLOCK_OUT 2 TOTA WORKED HOURS example
    2016-09-04 1004 05:34 07:34 09:34 16:34 7
    2016-09-04 1003 07:17 18:17 11
    2016-09-04 1006 07:13 16:34 9
    challenge here is with above code I am able to achieve the same but not able to search the data by date as well unable to convert the timezone.

  • #768074
    hi
    I can send you website name you can refers this website your question easily clear.in this website 2 or 3 different code will be their so you can choose you best code in this website please check it.

    hope this help you
    name: www.codeproject.com › Articles › Database

    thank you


Sign In to post your comments