我们如何使用SQL查询从总时间中减去3个中断时间 [英] How can we subtract 3 break time from total time using SQL query

问题描述

在我的项目中



StartTimeIn = 11:48 am

EndTimeOut = 4:18 pm

MealTimeIn = 11:58 am

MealTimeOut = 12:15 pm

TeaTimeIn = 1:06 pm

TeaTimeOut = 1:17 pm



所以TotalSpentTime = 270分钟

MealBreakTime = 17分钟

TeaBReakTime = 11分钟

现在我想从TotalSpentTime中减去（MealBreakTime + TeaBReakTime）

如270-（17 + 11）并将结果存储在列中。

我将发送我的查询我希望任何人都可以帮助我。



如果teabreakTime为null，那么整个值将为null。我想如果任何中断值为null，那么它将取0并进一步计算。



我的尝试：

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:

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

推荐答案

我真的不知道这个有什么这么复杂的< br $> b $ b



你有这些数字：

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


You have the numbers:

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

如果有的话大量数据可能效率低下（但你需要注意大量数据）



如果你真的担心效率低下那么你可以使用CTE（普通表）表达式）减少冗余重复计算：

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:

--[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.

这篇关于我们如何使用SQL查询从总时间中减去3个中断时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！