PERTALIFE

SELECT 

CONVERT(INT,'')as[ROW],

--INI DB BARU

CONVERT(int,'')as[id],

CONVERT(nvarchar(max),'')as[badgenumber],

CONVERT(nvarchar(max),'')as[userid],

CONVERT(datetime,'')as[checktime],

CONVERT(nvarchar(max),'')as[checktype],

CONVERT(nvarchar(max),'')as[verifycode],

CONVERT(nvarchar(max),'')as[sn]

INTO #temp

--CREATE ROWS IN NEW ESS TABLE TEMPO 

DELETE #temp

DECLARE @ROWSNUMBER  INT = (SELECT TOP(1) CONVERT(INT, ROW_NUMBER() OVER(ORDER BY id ASC)) AS Num FROM wdms_db.dbo.checkinout ORDER BY Num DESC)

DECLARE @i int = 1

WHILE @i <= @ROWSNUMBER

BEGIN  

   INSERT INTO #temp VALUES ( @i ,'','','','','','','')

     SET @i = @i + 1

END

--INSERT DATA ROWS IN NEW ESS TABLE TEMPO 

UPDATE #temp SET

id = a.id,

badgenumber = a.badgenumber,

userid = a.userid,

checktime = a.checktime,

checktype = a.checktype,

verifycode = a.verifycode,

sn = a.sn

  FROM(

SELECT  ROW_NUMBER() OVER(ORDER BY id ASC) AS Num, * FROM wdms_db.dbo.checkinout)a WHERE a.Num=#temp.[ROW]

--INSERT INTO NEW TABLE ESS

 --INSERT INTO MinovaES_AJTM_Pertalife_Development.dbo.[PHRPA0019] 

 select

 temp.userid

,CONVERT(varchar(8), temp.checktime, 112) as CheckTime

,startdate.checktime as StartDate

,enddate.checktime as EndDate

into #temp2

 from #temp temp

 left join #temp startdate on temp.id=startdate.id and startdate.checktype='0'

 left join #temp enddate on temp.id=enddate.id and enddate.checktype='1'

 where temp.checktype in ('0','1')

 and CONVERT(date, temp.checktime) = CONVERT(date, GETDATE())

 --select * from #temp2

 --select * from #temp where userid='10340122'

 select 

min(CONVERT(varchar(8), StartDate, 112)) as StartDate

,max(CONVERT(varchar(8), EndDate, 112)) as EndDate

,userid as EmployeeID

,min(FORMAT(StartDate, 'HHmm')) as StartTime

,max(FORMAT(EndDate, 'HHmm')) as EndTime

,'1000' as AttendanceType

,'Migrasi From WDMS' as Notes

,userid as CreateBy

,FORMAT(GETDATE(), 'yyyyMMddHHmmss') as CreateDate

,userid as ChangeBy

,FORMAT(GETDATE(), 'yyyyMMddHHmmss') as ChangeDate

,NULL as AuditorFlag

into #temp3

 from #temp2

 group by userid, CheckTime

 order by userid,CheckTime

--INSERT INTO NEW TABLE ESS

MERGE INTO MinovaES_AJTM_Pertalife_Development.dbo.[PHRPA0019] AS Target

USING (

    SELECT 

        CASE WHEN StartDate IS NULL THEN EndDate ELSE StartDate END AS StartDate,

        CASE WHEN StartDate IS NULL THEN EndDate ELSE StartDate END AS EndDate,

        EmployeeID,

        StartTime,

        EndTime,

        AttendanceType,

        Notes,

        CreateBy,

        CreateDate,

        ChangeBy,

        ChangeDate,

        AuditorFlag

    FROM #temp3

) AS Source ON Target.StartDate = Source.StartDate COLLATE DATABASE_DEFAULT 

             AND Target.EndDate = Source.EndDate COLLATE DATABASE_DEFAULT

             AND Target.EmployeeID = Source.EmployeeID COLLATE DATABASE_DEFAULT

WHEN NOT MATCHED THEN

    INSERT (StartDate, EndDate, EmployeeID, StartTime, EndTime, AttendanceType, Notes, CreateBy, CreateDate, ChangeBy, ChangeDate, AuditorFlag)

    VALUES (Source.StartDate, Source.EndDate, Source.EmployeeID, Source.StartTime, Source.EndTime, Source.AttendanceType, Source.Notes, Source.CreateBy, Source.CreateDate, Source.ChangeBy, Source.ChangeDate, Source.AuditorFlag);

DROP TABLE #temp

drop table #temp2

drop table #temp3

SELECT * FROM MinovaES_AJTM_Pertalife_Development.[dbo].[PHRPA0019] where Notes='Migrasi From WDMS' order by EmployeeID,StartDate