将数据添加到列 [英] Adding data to column

问题描述

我写了这个查询来显示某个员工在特定日期(如果他来的话)的总工作时间和加班时间.我想如果某个特定日期的人的 INtime 和 OutTime 是空的，那么在他的 intime、outtime、totalworkingtime、加班时间中输入 00:00，例如现在是星期天，所以显然没有 InTime 和 OutTime，那么它应该在时间列中显示 00:00.注意:只有在提到人 InTIme 时才输入日期，否则没有 DateVisited.

i have written this query to show total working time and overtime of an employee on particular date (if he has came). i want if for a particular date person's INtime and OutTime are empty then put 00:00 in his intime, outtime, totalworkingtime, overtime e.g. it's sunday so obviously no InTime and OutTime then it should show 00:00 in time columns. Note: dates are only entered if person InTIme is mentioned otherwise no DateVisited.

例如

EmplID  EmplName ShiftID intime Outtime totalworking overtime  dateVisited
0000001 John     S001    00:00  00:00   00:00:       00:00     2013-12-01

查询:

with times as (

SELECT    t1.EmplID

        , t3.EmplName

        , min(t1.RecTime) AS InTime

        , max(t2.RecTime) AS [TimeOut]

        , t4.ShiftId as ShiftID

        , t4.StAtdTime as ShStartTime

        , t4.EndAtdTime as ShEndTime

        , cast(min(t1.RecTime) as datetime) AS InTimeSub

        , cast(max(t2.RecTime) as datetime) AS TimeOutSub

        , t1.RecDate AS [DateVisited]

FROM  AtdRecord t1 

INNER JOIN 

      AtdRecord t2 

ON    t1.EmplID = t2.EmplID 

AND   t1.RecDate = t2.RecDate

AND   t1.RecTime < t2.RecTime

inner join 

      HrEmployee t3 

ON    t3.EmplID = t1.EmplID 

inner join AtdShiftSect t4

ON t3.ShiftId = t4.ShiftId

group by 

          t1.EmplID

        , t3.EmplName

        , t1.RecDate

        , t4.ShiftId 

        , t4.StAtdTime 

        , t4.EndAtdTime

)
SELECT 

 EmplID

,EmplName
,ShiftId As ShiftID

,InTime
,[TimeOut]
,convert(char(5),cast([TimeOutSub] - InTimeSub as time), 108) TotalWorkingTime

,[DateVisited]
,CASE WHEN [InTime] IS NOT NULL AND [TimeOut] IS NOT NULL THEN

     CONVERT(char(5),CASE WHEN  CAST([TimeOutSub] AS DATETIME) >= ShEndTime And ShiftID = 'S002' Then  LEFT(CONVERT(varchar(12), DATEADD(ms, DATEDIFF(ms, CAST(ShEndTime AS DATETIME),CAST([TimeOutSub] AS DATETIME)),0), 108),5) 

                          WHEN  CAST([TimeOutSub] AS DATETIME) >= ShEndTime And ShiftID = 'S001' Then  LEFT(CONVERT(varchar(12), DATEADD(ms, DATEDIFF(ms, CAST(ShEndTime AS DATETIME),  CAST([TimeOutSub] AS DATETIME)),0), 108),5) 

      ELSE '00:00' END, 108) 

 ELSE 'ABSENT' END AS OverTime

FROM times  order by EmplID, ShiftID, DateVisited

推荐答案

如果您的问题仅依赖于知道日期是工作日/假期/周末，我建议您使用 DataWarehouse 解决方案.创建一个包含所有假期/周末的表并进行检查.

If your problem solely relies on knowing when a date is a workday/holiday/weekend I would recommend using a DataWarehouse solution. Create A table that contains all the holidays/weekends and check against it.

根据您使用的日历和其他日期，有许多脚本可以为您创建，但最简单的方法是在接下来的 10 年左右计算您所有假期的日期.例如圣诞节总是在 12 月 23 日，感恩节总是在 11 月的第 3 周我会创建一个这样的表

Depending on what Calendar and what other dates you are using there are many scripts out there to create it for you, but the simplest way would be for the next 10 years or so calculate when all your holidays land on. for example Christmas is always on December 23 and thanksgiving is always 3 week in November I would create a table like this

CREATE TABLE Holiday
(
HolidayID INT NOT NULL, --surrogate key, but you can just as easily make HolidayDate the Natural key
HolidayDate  DATE NOT NULL
HolidayName NVARCHAR(30),--encase you need non English holiday names saved
...
...
..


INSERT INTO Holiday (HolidayDate,HolidayName) VALUES (2014-12-23, 'Christmas')
INSERT INTO Holiday (HolidayDate,HolidayName) VALUES (2015-12-23, 'Christmas')
INSERT INTO Holiday (HolidayDate,HolidayName) VALUES (2016-12-23, 'Christmas')
INSERT INTO Holiday (HolidayDate,HolidayName) VALUES (2017-12-23, 'Christmas')
.....
.....
....

或者，如果您想获得更深入的了解，您可以在表(日期维度表)中绘制未来 10 年的每一天.然后将这些日子标记为假期、取消工作、销售季度等等.(谷歌日期维度表)

Or if you want to get even more indepth you can map out ever day for the next 10 years in a table (Date Dimension table). Then mark those days as holidays, work cancelled, sales quarters and what not. (google Date Dimension Table)

这篇关于将数据添加到列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！