StartTimeIn=11:48 am,
EndTimeOut =4:18 pm,
MealTimeIn=11:58 am,
MealTimeOut=12:15 pm,
TeaTimeIn= 1:06 pm,
TeaTimeOut=1:17 pm,
OtherTimeIn=null,
OtherTimeOut=null,
TimeSpent(StartTimeIn to EndTimeOut) is 270 minutes
MealBreakTimeSpent(MealTimeIn to MealTimeOut) is 17 minute
TeaBreakTimeSpent(TeaTimeIn to TeaTimeOut) is 11 minutes
OtherBreakTimeSpent(OtherTimeIn to OtherTimeOut) is null
TotalTimeSpent=242 minutes
so Time like 4:02,
I am sending my query can you please correct it.
What I have tried:
SELECT CONVERT(nvarchar(5), ISNULL( DATEADD(minute, DATEDIFF(minute, StartTimeIn, ISNULL(EndTimeOut,SWITCHOFFSET(SYSDATETIMEOFFSET(), '+05:30'))), 0),0), 114) -
(
CONVERT(nvarchar(5), ISNULL( DATEADD(minute, DATEDIFF(minute, MealTimeIn, ISNULL(MealTimeOut,SWITCHOFFSET(SYSDATETIMEOFFSET(), '+05:30'))), 0),0), 114) +
CONVERT(nvarchar(5), ISNULL( DATEADD(minute, DATEDIFF(minute, TeaTimeIn, ISNULL(TeaTimeOut,SWITCHOFFSET(SYSDATETIMEOFFSET(), '+05:30'))), 0),0), 114) +
CONVERT(nvarchar(5),ISNULL( DATEADD(minute, DATEDIFF(minute, OtherTimeIn, ISNULL(OtherTimeOut,SWITCHOFFSET(SYSDATETIMEOFFSET(), '+05:30'))), 0),0), 114)
) AS [TimeSpent]
from DailyTimeRecord
The data types varchar and varchar are incompatible in the subtract operator.