How can we subtract 3 break time from total time using SQL query

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

In my project

StartTimeIn=11:48 am
EndTimeOut=4:18 pm
MealTimeIn=11:58 am
MealTimeOut=12:15 pm
TeaTimeIn=1:06 pm
TeaTimeOut=1:17 pm

so TotalSpentTime=270 minutes
MealBreakTime=17 minutes
TeaBReakTime=11 minutes
now I want to subtract (MealBreakTime+TeaBReakTime) from TotalSpentTime
like 270-(17+11) and store result in column.
I will send my query I hope anybody can help me.

If teabreakTime is null then whole value will be null.I want if any break value is null then it will take as 0 and go for further calculation.

What I have tried:

SQL

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

DATEDIFF(minute,MealTimeIn,MealTimeOut) AS [MealBreak],
DATEDIFF(minute,TeaTimeIn,TeaTimeOut) AS [TeaBreak],
DATEDIFF(minute,StartTimeIn,EndTimeOut) AS [TimeSpent]
FROM  
DailyTimeRecord

Posted 9-Oct-16 23:30pm

Member 12097108

Updated 11-Oct-16 20:39pm

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Andy Lanng · Accepted Answer · 2016-10-10T00:02:00

I don't really see what's so complex about this

You have the numbers:

SQL

DATEDIFF(minute,MealTimeIn,MealTimeOut) AS [MealBreak],
DATEDIFF(minute,TeaTimeIn,TeaTimeOut) AS [TeaBreak],
DATEDIFF(minute,StartTimeIn,EndTimeOut) AS [TimeSpent]
--you could just add the maths again:
DATEDIFF(minute,MealTimeIn,MealTimeOut) - (DATEDIFF(minute,TeaTimeIn,TeaTimeOut) + DATEDIFF(minute,StartTimeIn,EndTimeOut)) as TotalSpentTime

If you have masses of data this can be inefficient (but you would need MASSES of data to notice)

If you're really worried about inefficiency then you could use CTE's (Common Table Expressions) to reduce the redundant duplicated calculations:

SQL

--[test data] is just for the working example.  Exclude this and use your original table in [calc_data] 
with test_data as (
	select 
		'2016-10-10 11:48:00' as StartTimeIn,
		'2016-10-10 16:18:00' as EndTimeOut,
		'2016-10-10 11:58:00' as MealTimeIn,
		'2016-10-10 12:15:00' as MealTimeOut,
		'2016-10-10 13:06:00' as TeaTimeIn,
		'2016-10-10 13:17:00' as TeaTimeOut
	), calc_data as ( 
	select 
		datediff(minute, StartTimeIn, EndTimeOut) as TimeSpent,
		datediff(minute, TeaTimeIn,TeaTimeOut) as TeaBreak,
		datediff(minute, MealTimeIn, MealTimeOut) as MealBreak
from test_data
	), calc_total_data as (
		select 
			TimeSpent,
			TeaBreak,
			MealBreak,
			TimeSpent - (TeaBreak + MealBreak) as  TotalSpentTime
		from calc_data
	)
select * from calc_total_data

Is that all? I get the feeling that I'm missing the issue.