Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- /****** Object: View [dbo].[vcalendarlate] Script Date: 28/11/2023 14:54:23 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- ALTER VIEW [dbo].[vcalendarlate]
- AS
- select employeeid,sum(late)Late,sum(EarlyOut)EarlyOut,sum(NoCheckIn)NoCheckIn,sum(NoCheckOut) NoCheckOut from (
- select a.EmployeeID,day(a.tgl) hari,a.tgl,a.Late,case when NoCheckOut = 0 then
- (case when datediff(MINUTE,a.timeout,b.jadwalkeluar) > 0 then 1 else 0 end)
- else 0 end EarlyOut,NoCheckIn,NoCheckOut from (
- SELECT distinct ao.EmployeeID, 'H' AS tipe, convert(datetime,Date,105) AS tgl, isnull(timeIn,Date)TimeIn, ISNULL(TimeOut, Date) AS timeout,
- 'H In:' + ISNULL(left(CONVERT(varchar, TimeIn, 108),5), '-') + ' Out:' + ISNULL(left(CONVERT(varchar, TimeOut, 108),5), '-')
- + ' Late:' + ISNULL(left(CONVERT(varchar, LateTimeDouble, 108),5), '00:00:00') AS note, 'limegreen' AS color,
- case when LateTimeDouble <> 0 then 1 else 0 end Late,
- case when timeIn is null then 1 else 0 end NoCheckIn,case when timeout is null then 1 else 0 end NoCheckOut
- FROM dbo.AttendanceOverview ao left join attendance a on ao.attendanceoverviewid = a.attendanceoverviewid where a.isAllApproved = 1
- ) a inner join
- (
- SELECT distinct EmployeeID, 'A' AS tipe, tgl, convert(datetime,convert(varchar,tgl,105)+ ' ' + convert(varchar(8),masuk),105) jadwalmasuk,
- convert(datetime,convert(varchar,case when masuk > keluar then dateadd(day,1,tgl) else tgl end,105)+ ' ' + convert(varchar(8),keluar),105) jadwalkeluar,
- 'A In:' + ISNULL(left(CONVERT(varchar, masuk, 108),5), '-') + ' Out:' + ISNULL(left(CONVERT(varchar, keluar, 108),5), '-') note,
- 'slategrey' AS color, 0 Late FROM dbo.vwp where tipe = 'reguler' and tgl is not null) b on
- a.EmployeeID = b.employeeid and a.tgl = b.tgl
- ) c group by EmployeeID
- GO
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement