DECLARE @now VARCHAR(8) 
SET @now = dbo.fn_formatdatetime(GETDATE(), 'yyyymmdd')
DECLARE @maskerja AS TABLE (mskerja INT)
INSERT INTO @maskerja VALUES (5),(10),(15),(20),(25),(30),(35),(40),(45),(50),(55),(60)	

DECLARE @empHiring AS TABLE
    (
      EmployeeID VARCHAR(MAX) ,
      HiringDate VARCHAR(MAX),
	  [Year] VARCHAR(MAX),
	  [Month] VARCHAR(MAX)
	  --[day] VARCHAR(MAX)
    )
INSERT  INTO @empHiring
       SELECT EmployeeID,
	    HiringDate,
        DATEDIFF(year, HiringDate, GETDATE()) as tahun,
        DATEDIFF(month, HiringDate, GETDATE()) % 12 as bulan
        --DATEDIFF(day, HiringDate, GETDATE()) % 365 % 30 as hari
    FROM PHRPA0015

Declare @FilterHiring  AS TABLE
    (
	 StartDate VARCHAR(MAX) ,
      EndDate VARCHAR(MAX),
      EmployeeID VARCHAR(MAX) ,
      HiringDate VARCHAR(MAX),
	  [Year] VARCHAR(MAX),
	  [Month] VARCHAR(MAX)
    )
INSERT  INTO @FilterHiring
SELECT CONVERT(VARCHAR,YEAR(GETDATE()))+CONVERT(VARCHAR,SUBSTRING(@now,5,4)) StartDate ,
		CONVERT(VARCHAR,YEAR(GETDATE()) +5)+CONVERT(VARCHAR,SUBSTRING(@now,5,2))+ SUBSTRING(CONVERT(VARCHAR(MAX), CONVERT(DATE, DATEADD(DAY, -1, @now))),9,2) AS EndDate , * from @empHiring where  Month = '0' and SUBSTRING(HiringDate, 7, 2) = SUBSTRING(@now, 7, 2) and Year in (select * from @maskerja )

INSERT  INTO PHRPA0017
SELECT DISTINCT  StartDate AS StartDate ,
        EndDate AS EndDate ,
        EmployeeID ,
        '1111' AS AbsenceQuotaType ,
        '1' AS [Sequence] ,
        StartDate AS StartDeduction ,
        EndDate AS EndDeduction ,
        '5' AS OriginalQuota ,
        '5' AS RemainQuota ,
        '0' AS PreviousQuota ,
        EndDate AS ExpiredQuota ,
        'Generate Leave Quota Cuti Masa Kerja ' AS Notes,
		'' AS Createby,
		'' AS CreateDate,
		'' AS ChangeBy,
		'' AS ChangeDate
FROM    @FilterHiring


