Category: ASP.NET

04 Oct 2016 02:51

Joined:	04/10/2016
Level:	Bronze
Points:	0

Points:	5

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">

<asp:Button ID="Button1" runat="server" Text="Button" />
 
<asp:TextBox ID="TextBox1" runat="server" OnTextChanged="TextBox1_TextChanged"></asp:TextBox>




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

#768010

04 Oct 2016 11:04

Joined:	13/06/2007
Level:	Gold
Points:	21327

Points:	4

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

04 Oct 2016 11:25

Joined:	18/12/2008
Level:	Gold
Points:	2921

Points:	3

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

04 Oct 2016 20:35

Joined:	04/10/2016
Level:	Bronze
Points:	0

Points:	1

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

10 Oct 2016 10:04

Joined:	28/09/2016
Level:	Bronze
Points:	24

Points:	0

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

Total hours calculation

Email subscription

About dotnetspider.com

Quick Links

General

Services