Member 12097108 Ответов: 1

Как мы можем вычесть 3 времени перерыва из общего времени с помощью SQL запроса

В моем проекте

StartTimeIn=11: 48 утра
EndTimeOut=4: 18 вечера
MealTimeIn=11: 58 утра
MealTimeOut=12: 15 вечера
TeaTimeIn=1: 06 вечера
Чаепитие=1: 17 вечера

таким образом, TotalSpentTime=270 минут
Время перерыва на обед=17 минут
TeaBReakTime=11 минут
теперь я хочу вычесть (MealBreakTime+TeaBReakTime) из TotalSpentTime
например, 270-(17+11)и сохранить результат в столбце.
Я пошлю свой запрос, надеюсь, кто-нибудь сможет мне помочь.

Если teabreakTime равно null, то все значение будет равно null.Я хочу, чтобы если какое-либо значение разрыва равно нулю, то оно будет приниматься как 0 и идти на дальнейший расчет.

Что я уже пробовал:

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

1 Ответов

Рейтинг:
4

Andy Lanng

Я действительно не понимаю, что в этом такого сложного


У вас есть номера:

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 (Common Table Expressions) для уменьшения избыточных дублированных вычислений:


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


И это все? У меня такое чувство, что я упускаю эту проблему.

Member 12097108

Большое вам спасибо, сэр.

Member 12097108

Здравствуйте, сэр, могу я задать еще один вопрос?..

Andy Lanng

Вы можете опубликовать его как новый вопрос.
Если вы хотите привлечь мое внимание к этому, то просто свяжите новый пост здесь

Andy Lanng

ISNULL(5,0) = 5
ISNULL(null, 0) = 0