Total hours calculation
i would to search person_num and creation_datei 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>