vcalendarlate isAllApproved

/****** 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