In MealIn MealOut MealBreak TeaIn TeaOut TeaBreak OutTime TimeSpent
12:52PM 12:55PM 1:05PM 00:10 1:13PM 1:45PM 00:32 4:21PM 02:47
12:52PM 12:55PM 1:05PM 00:10 2:11PM 2:23PM 00:12 4:21PM 03:07
12:52PM 12:55PM 1:05PM 00:10 3:11PM 3:15PM 00:04 4:21PM 03:15
Sub Total 09:09
Grand Total 09:09
This is my output.I am login at (26/10.2016) 12:52PM
first we take meal break for 10 minutes.then I was take tea break 32 minutes so timespent is 2:47.now I am taking another tea break at same date for 12 minutes time and third time I was take tea break for 4 minutes so total break is 48 minutes
My start time is 12:52PM and EndTime is 4:21PM so between start time and endtime duration -total break time like 3:29-48=2:41 so I spent total time 2:41.but in my case i can able to get it.
I want to output like this..
<pre lang="C#">
In MealIn MealOut MealBreak TeaIn TeaOut TeaBreak TeaIn TeaOut TeaBreak TeaIn
12:52PM 12:55PM 1:05PM 00:10 1:13PM 1:45PM 00:32 2:11PM 2:23PM 00:12 3:11PM
TeaOut TeaBreak OutTime TimeSpent
3:15PM 00:04 4:21PM 02:41
Sub Total 02:41
Grand Total 02:41</pre>
I want to output like this in gridview in asp.net .can you please help me.I will send my gridview code and procedure.
What I have tried:
gridview code.
<asp:GridView ID="grdView" runat="server" AutoGenerateColumns="false" CssClass="table table-bordered" TabIndex="1" Width="100%"
GridLines="Vertical" OnRowCreated="grdView_RowCreated1" OnRowDataBound="grdView_RowDataBound1">
<Columns>
<asp:BoundField DataField="LoginDate" HeaderText="LoginDate" SortExpression="LoginDate" />
<asp:BoundField DataField="FullName" HeaderText="EmployeeName" SortExpression="FullName" />
<asp:BoundField DataField="STime" HeaderText="In Time" SortExpression="STime" />
<asp:BoundField DataField="MTimeIn" HeaderText="MealIn" SortExpression="MTimeIn" />
<asp:BoundField DataField="MTimeOut" HeaderText="MealOut" SortExpression="MTimeOut" />
<asp:BoundField DataField="MealTimeSpent" HeaderText="MealBreak" SortExpression="MealTimeSpent" />
<asp:BoundField DataField="TTimeIn" HeaderText="TeaIn" SortExpression="TTimeIn" />
<asp:BoundField DataField="TTimeOut" HeaderText="TeaOut" SortExpression="TTimeOut" />
<asp:BoundField DataField="TeaTimeSpent" HeaderText="TeaBreak" SortExpression="TeaTimeSpent" />
<asp:BoundField DataField="TeaTimeSpentMinutes" HeaderText="TimeSpentMinutes" SortExpression="TeaTimeSpentMinutes" Visible="false" />
<asp:BoundField DataField="OTimeIn" HeaderText="Oth.In" SortExpression="OTimeIn" />
<asp:BoundField DataField="OTimeOut" HeaderText="Oth.Out" SortExpression="OTimeOut" />
<asp:BoundField DataField="OtherTimeSpent" HeaderText="OtherBreak" SortExpression="OtherTimeSpent" />
<asp:BoundField DataField="OtherTimeSpentMinutes" HeaderText="TimeSpentMinutes" SortExpression="OtherTimeSpentMinutes" Visible="false" />
<asp:BoundField DataField="ETime" HeaderText="OutTime" SortExpression="ETime" />
<asp:BoundField DataField="TimeSpentWork" HeaderText="TimeSpent" SortExpression="TimeSpentWork" />
<asp:BoundField DataField="EmployeeId" HeaderText="EmployeeId" SortExpression="EmployeeId" Visible="false"></asp:BoundField>
<asp:BoundField DataField="TimeSpentMinutes" HeaderText="TimeSpentMinutes" SortExpression="TimeSpentMinutes" Visible="false" />
</Columns>
</asp:GridView>
procedure.
SELECT CONVERT(VARCHAR, T.CreateDateTime,105) AS [LoginDate],
E.FullName,
T.StartTimeIn,
T.EndTimeOut,
CONVERT(varchar(15),CAST(StartTimeIn AS TIME),100) AS [STime],
CONVERT(varchar(15),CAST(EndTimeOut AS TIME),100) AS [ETime],
CONVERT(varchar(15),CAST(MealTimeIn AS TIME),100) AS [MTimeIn],
CONVERT(varchar(15),CAST(MealTimeOut AS TIME),100) AS [MTimeOut],
CONVERT(varchar(15),CAST(D.TeaTimeIn AS TIME),100) AS [TTimeIn],
CONVERT(varchar(15),CAST(D.TeaTimeOut AS TIME),100) AS [TTimeOut],
CONVERT(varchar(15),CAST(OtherTimeIn AS TIME),100) AS [OTimeIn],
CONVERT(varchar(15),CAST(OtherTimeOut AS TIME),100) AS [OTimeOut],
CONVERT(varchar(5), DATEADD(minute, DATEDIFF(minute, StartTimeIn, ISNULL(EndTimeOut,SWITCHOFFSET(SYSDATETIMEOFFSET(), ''+05:30''))), 0), 114) AS [TimeSpent] ,
DATEDIFF(minute, StartTimeIn, ISNULL(EndTimeOut,SWITCHOFFSET(SYSDATETIMEOFFSET(), ''+05:30''))) AS [TimeSpentMinutes],
CONVERT(varchar(5), DATEADD(minute, DATEDIFF(minute, MealTimeIn, ISNULL(MealTimeOut,SWITCHOFFSET(SYSDATETIMEOFFSET(), ''+05:30''))), 0), 114) AS [MealTimeSpent] ,
DATEDIFF(minute, MealTimeIn, ISNULL(MealTimeOut,SWITCHOFFSET(SYSDATETIMEOFFSET(), ''+05:30''))) AS [MealTimeSpentMinutes],
CONVERT(varchar(5), DATEADD(minute, DATEDIFF(minute, D.TeaTimeIn, ISNULL(D.TeaTimeOut,SWITCHOFFSET(SYSDATETIMEOFFSET(), ''+05:30''))), 0), 114) AS [TeaTimeSpent] ,
DATEDIFF(minute, D.TeaTimeIn, ISNULL(D.TeaTimeOut,SWITCHOFFSET(SYSDATETIMEOFFSET(), ''+05:30''))) AS [TeaTimeSpentMinutes],
CONVERT(varchar(5), DATEADD(minute, DATEDIFF(minute, OtherTimeIn, ISNULL(OtherTimeOut,SWITCHOFFSET(SYSDATETIMEOFFSET(), ''+05:30''))), 0), 114) AS [OtherTimeSpent] ,
DATEDIFF(minute, OtherTimeIn, ISNULL(OtherTimeOut,SWITCHOFFSET(SYSDATETIMEOFFSET(), ''+05:30''))) AS [OtherTimeSpentMinutes],
CONVERT(varchar(5),ISNULL(DATEADD(minute, DATEDIFF(minute, StartTimeIn, ISNULL(EndTimeOut,SWITCHOFFSET(SYSDATETIMEOFFSET(), ''+05:30''))), 0),0)-
(ISNULL(DATEADD(minute, DATEDIFF(minute, MealTimeIn, ISNULL(MealTimeOut,SWITCHOFFSET(SYSDATETIMEOFFSET(), ''+05:30''))), 0),0) +
ISNULL(DATEADD(minute, DATEDIFF(minute, D.TeaTimeIn, ISNULL(D.TeaTimeOut,SWITCHOFFSET(SYSDATETIMEOFFSET(), ''+05:30''))), 0),0) +
ISNULL(DATEADD(minute, DATEDIFF(minute, OtherTimeIn, ISNULL(OtherTimeOut,SWITCHOFFSET(SYSDATETIMEOFFSET(), ''+05:30''))), 0),0)),114) AS [TimeSpentWork],
DATEDIFF(minute, StartTimeIn, ISNULL(EndTimeOut,SWITCHOFFSET(SYSDATETIMEOFFSET(), ''+05:30'')))-
(ISNULL(DATEDIFF(minute, MealTimeIn, ISNULL(MealTimeOut,SWITCHOFFSET(SYSDATETIMEOFFSET(), ''+05:30''))),0)+
ISNULL(DATEDIFF(minute, D.TeaTimeIn, ISNULL(D.TeaTimeOut,SWITCHOFFSET(SYSDATETIMEOFFSET(), ''+05:30''))),0)+
ISNULL(DATEDIFF(minute, OtherTimeIn, ISNULL(OtherTimeOut,SWITCHOFFSET(SYSDATETIMEOFFSET(), ''+05:30''))),0) ) AS [TimeSpentMinutesWork],
E.EmployeeId
FROM
DailyTimeRecord T
INNER JOIN Employees E ON E.EmployeeId = T.EmployeeId
INNER JOIN TeaBreak D ON T.DailyTimeRecordId = D.DailyTimeRecordId