これが現時点での私のSQLクエリです:
select Employee_Number, Cast([DateTime] as Date) as 'DateTime', MIN([DateTime]) as 'MIN', MAX([DateTime]) as 'MAX' , [Hours Worked] =
CAST((DATEDIFF(HOUR ,min([DateTime]), max([DateTime])) / 24) AS VARCHAR) +
' Days ' +
CAST((DATEDIFF(HOUR , min([DateTime]), max([DateTime])) % 24) AS VARCHAR) +
' Hours ' +
CAST((DATEDIFF(MINUTE, min([DateTime]), max([DateTime])) % 60) AS VARCHAR) +
' Minutes ' +
CAST((DATEDIFF(SECOND, min([DateTime]), max([DateTime])) % 60) AS VARCHAR) +
' Seconds ' , [Over Time] = CAST((DATEDIFF(HOUR, '17:30:00:000', MAX([DateTime])) % 24) AS VARCHAR) + ' Hours ' +
CAST((DATEDIFF(MINUTE, '17:30:00:000', max([DateTime])) % 60) AS VARCHAR) + ' Minutes', [LATE] =
CAST((DATEDIFF(HOUR, '08:30:00:000',Min([DATETIME])) % 24) AS VARCHAR) + ' Hours ' + CAST((DATEDIFF(MINUTE, '08:30:00:000', MIN([DateTime])) % 60) AS VARCHAR) + ' Minutes'
from tblExtract group by Employee_Number, Cast([DateTime] as Date)
そして、これはこのクエリが示すものです:
問題は、従業員が8:30より前に早く入社し、17:30より前にも早く出て行った場合に発生しました。
どうすればこれを修正できますか?従業員が早く出発または到着した場合、LATEまたはOvertime列の値が「0時間0分」になるケースを取得するにはどうすればよいですか?
'0 Hours 0 Minutes'
人が早く到着/出発したかどうかを表示したい場合はCASE
、そのために使用できます。
select Employee_Number, Cast([DateTime] as Date) as 'DateTime', MIN([DateTime]) as 'MIN', MAX([DateTime]) as 'MAX' , [Hours Worked] =
CAST((DATEDIFF(HOUR ,min([DateTime]), max([DateTime])) / 24) AS VARCHAR) +
' Days ' +
CAST((DATEDIFF(HOUR , min([DateTime]), max([DateTime])) % 24) AS VARCHAR) +
' Hours ' +
CAST((DATEDIFF(MINUTE, min([DateTime]), max([DateTime])) % 60) AS VARCHAR) +
' Minutes ' +
CAST((DATEDIFF(SECOND, min([DateTime]), max([DateTime])) % 60) AS VARCHAR) +
' Seconds ' , [Over Time] = CASE WHEN DATEDIFF(MINUTE, '17:30:00:000', CONVERT(TIME, MAX([DateTime]))) < 0
THEN '0 Hours 0 Minutes'
ELSE
CAST((DATEDIFF(HOUR, '17:30:00:000', MAX([DateTime])) % 24) AS VARCHAR) + ' Hours ' +
CAST((DATEDIFF(MINUTE, '17:30:00:000', max([DateTime])) % 60) AS VARCHAR) + ' Minutes'
END,
[LATE] = CASE WHEN DATEDIFF(MINUTE, '08:30:00:000', CONVERT(TIME, MIN([DateTime]))) < 0
THEN '0 Hours 0 Minutes'
ELSE
CAST((DATEDIFF(HOUR, '08:30:00:000',Min([DATETIME])) % 24) AS VARCHAR) +
' Hours ' + CAST((DATEDIFF(MINUTE, '08:30:00:000', MIN([DateTime])) % 60) AS VARCHAR) + ' Minutes'
END
from Extraction
group by Employee_Number, Cast([DateTime] as Date)
この記事はインターネットから収集されたものであり、転載の際にはソースを示してください。
侵害の場合は、連絡してください[email protected]
コメントを追加