Hello,I have column in table like TeaTimeIn,TeaTimeOut.
very first time
TeaTimeIn = 12:10 pm,
TeaTimeOut= 12:20 pm,
So TeaBreakTimeSpent is 10 minutes.
second time we take new tea break that time our earlyear TeaBreakTimeSpent is removed and take new value.but I want like this If we take number of teabreak the first time teabreatime duration will be store using query if we take another tea break so that time earlyear timeduration and new time duration will be added and display sum of this time duration.
like..
first time
TeaTimeIn = 12:10 pm,
TeaTimeOut= 12:20 pm,
So TeaBreakTimeSpent is 10 minutes.
Second time
TeaTimeIn = 3:30 pm,
TeaTimeOut= 3:35 pm,
So TeaBreakTimeSpent is 5 minutes.
third time
TeaTimeIn = 6:10 pm,
TeaTimeOut= 6:20 pm,
So TeaBreakTimeSpent is 10 minutes.
so all teabreaktimespent display like 25 minutes.
If it possible to store first time of TeaBreakTimeSpent dynamically in query and second time of TeaBreakTimeSpent dynamically in query and third time of TeaBreakTimeSpent dynamically in query and after sum all timeDuration of Teabreak and display it.
What I have tried:
SELECT CONVERT(VARCHAR, CreateDateTime,105) AS [LoginDate],
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 [MTime],
CONVERT(varchar(15),CAST(MealTimeOut AS TIME),100) AS [MOUT],
CONVERT(varchar(15),CAST(TeaTimeIn AS TIME),100) AS [TTime],
CONVERT(varchar(15),CAST(TeaTimeOut AS TIME),100) AS [TOUT],
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, TeaTimeIn, ISNULL(TeaTimeOut,SWITCHOFFSET(SYSDATETIMEOFFSET(), '+05:30'))), 0), 114) AS [TeaTimeSpent] ,
DATEDIFF(minute, TeaTimeIn, ISNULL(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, TeaTimeIn, ISNULL(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, TeaTimeIn, ISNULL(TeaTimeOut,SWITCHOFFSET(SYSDATETIMEOFFSET(), '+05:30'))),0)+
ISNULL(DATEDIFF(minute, OtherTimeIn, ISNULL(OtherTimeOut,SWITCHOFFSET(SYSDATETIMEOFFSET(), '+05:30'))),0) ) AS [TimeSpentMinutesWork]
FROM
DailyTimeRecord