none
Merge Date with GetDate()

    Question

  • May i know how to join a date from table merge with GETDATE() to show a result ?

    For the Example,

    SELECT  [FORM_NO]
        ,   [FORM_KIND]
        ,   [EMP_NO]
        ,   [ORG_ID]
        ,   [LEAVE_NO]
        ,   [SHIFT_NO]
        ,   [START_DAY] --// start  date apply leave 
        ,   [start_time]
        ,   [END_DAY]
        ,   [end_time] --// end date apply leave
        ,   [SUMHOURS]
        ,   [SUM_DAYS]
        ,   [ATTORNEY_EMP_NO]
        ,   [APPLY_REASON]
        ,   [NOTE]
        ,   [status]
        ,   [FILL_EMP_NO]
        ,   [FILL_DATE]
    FROM [LEAVETb]

    From this table i have start-date and end-date. Next i want join with EMP_InfoEMP_Shift,EMP_Desc and EMP_HistScan tables.

    SELECT  EMPINFO.EP_EMP_DEPT
        ,   EMPINFO.EP_EMP_SECTION
        ,   EMPINFO.EP_EMP_ID
        ,   EMPINFO.EP_EMP_NAME
        ,   EMPINFO.EP_EMP_SHIFT
        ,   SHIFTCAL.EP_SHIFT
    FROM [EP_EMP_INFO] EMPINFO -- All EMP Details Table
    JOIN [EP_SHIFT_CALENDAR] SHIFTCAL -- ALL SHIFT Type table 
         ON SHIFTCAL.EP_SHIFT_NAME = EMPINFO.EP_EMP_SHIFT 
        AND SHIFTCAL.EP_SHIFT_DATE = CONVERT(VARCHAR(8), GETDATE(), 112)
    LEFT JOIN
    (
        SELECT  CONVERT(VARCHAR(8), STUFF(STUFF(FIRSTSCAN.EP_SCAN_DATE, 12, 0, ':'), 15, 0, ':'), 112) AS SCANDATE
            ,   FIRSTSCAN.EP_EMP_COMPANY
            ,   FIRSTSCAN.EP_EMP_ID
            ,   FIRSTSCAN.EP_EMP_NAME
            ,   FIRSTSCAN.EP_EMP_DEPT
            ,   FIRSTSCAN.EP_EMP_SECTION
            ,   FIRSTSCAN.EP_EMP_SHIFT
            ,   FIRSTSCAN.EP_SHIFT
            ,   FIRSTSCAN.EP_SCAN_DATE
            ,   FIRSTSCAN.LATEIN
        FROM
        (
            SELECT  ROW_NUMBER() OVER (PARTITION BY SCANHIST.EP_EMP_ID, CASE
                        WHEN RIGHT(SCANHIST.EP_SCAN_DATE, 6) < '130000' AND SHIFTCAL.EP_SHIFT = 'N1' THEN CONVERT(VARCHAR(8), DATEADD(DAY, -1, CONVERT(VARCHAR(8), LEFT(SCANHIST.EP_SCAN_DATE, 8), 112)), 112) ELSE LEFT(SCANHIST.EP_SCAN_DATE, 8)
                    END ORDER BY SCANHIST.EP_EMP_ID, SCANHIST.EP_SCAN_DATE) AS RowNum
                ,   SCANHIST.EP_SCAN_DATE
                ,   CASE
                        WHEN RIGHT(SCANHIST.EP_SCAN_DATE, 6) < '130000' AND SHIFTCAL.EP_SHIFT = 'N1' THEN CONVERT(VARCHAR(8), DATEADD(DAY, -1, CONVERT(VARCHAR(8), LEFT(SCANHIST.EP_SCAN_DATE, 8), 112)), 112) ELSE LEFT(SCANHIST.EP_SCAN_DATE, 8)
                    END AS EMP_WORKDATE
                ,   EMPINFO.EP_EMP_COMPANY
                ,   SCANHIST.EP_EMP_ID
                ,   SCANHIST.EP_EMP_NAME
                ,   SCANHIST.EP_EMP_DEPT
                ,   SCANHIST.EP_EMP_SECTION
                ,   SCANHIST.EP_EMP_SHIFT
                ,   SHIFTCAL.EP_SHIFT
                ,   CASE WHEN RIGHT(SCANHIST.EP_SCAN_DATE, 6) > REPLACE(SHIFTDESC.EP_SHIFT_TIMEFR, ':', '') THEN 1 END AS LATEIN 
            FROM [EP_SCAN_HIST] SCANHIST  --//  Employee Scan IN / Out time Table 
            JOIN [EP_EMP_INFO] EMPINFO ON EMPINFO.EP_EMP_ID = SCANHIST.EP_EMP_ID
            JOIN [EP_SHIFT_CALENDAR] SHIFTCAL
                 ON SHIFTCAL.EP_SHIFT_NAME = SCANHIST.EP_EMP_SHIFT
                AND SHIFTCAL.EP_SHIFT_DATE = LEFT(SCANHIST.EP_SCAN_DATE,8)
            JOIN [EP_SHIFT_DESC] SHIFTDESCON SHIFTDESC.EP_SHIFT_NAME = SHIFTCAL.EP_SHIFT
            WHERE 1=1
                AND SCANHIST.EP_SCAN_DATE >= CONVERT(VARCHAR(8),GETDATE(),112) + ' ' + CASE WHEN (SHIFTCAL.EP_SHIFT <> 'R1' AND SHIFTCAL.EP_SHIFT <> 'R2') THEN REPLACE(CONVERT(VARCHAR(8),DATEADD(HOUR,-4,SHIFTDESC.EP_SHIFT_TIMEFR + ':00'),108),':','') ELSE REPLACE(CONVERT(VARCHAR(8),DATEADD(HOUR,-0,SHIFTDESC.EP_SHIFT_TIMEFR + ':00'),108),':','') END
                AND SCANHIST.EP_SCAN_DATE < CASE WHEN (SHIFTCAL.EP_SHIFT = 'N1')  THEN CONVERT(VARCHAR(8),DATEADD(DAY,+1,CONVERT(VARCHAR(8),GETDATE(),112)),112) + ' ' + REPLACE(CONVERT(VARCHAR(8),DATEADD(HOUR,+6,SHIFTDESC.EP_SHIFT_TIMETO + ':00'),108),':','') ELSE CASE WHEN (SHIFTCAL.EP_SHIFT = 'R1' OR SHIFTCAL.EP_SHIFT = 'R2') THEN CONVERT(VARCHAR(8),GETDATE(),112) + ' ' + REPLACE(CONVERT(VARCHAR(8),DATEADD(HOUR,+0,SHIFTDESC.EP_SHIFT_TIMETO + ':00'),108),':','') ELSE CONVERT(VARCHAR(8),GETDATE(),112) + ' ' + REPLACE(CONVERT(VARCHAR(8),DATEADD(HOUR,+6,SHIFTDESC.EP_SHIFT_TIMETO + ':00'),108),':','') END END
                AND SCANHIST.EP_TRANS_LOC = 'IN'
                AND EMPINFO.EP_EMP_LEVEL > '10'
                AND EMPINFO.EP_EMP_LEVEL <> ''
                AND (SHIFTCAL.EP_SHIFT = 'D1'
              OR SHIFTCAL.EP_SHIFT = 'NS')
        ) FIRSTSCAN
        WHERE 1=1 AND FIRSTSCAN.RowNum = 1
    )FIRSTSCAN ON FIRSTSCAN.EP_EMP_ID = EMPINFO.EP_EMP_ID
    WHERE 1=1
        AND EMPINFO.EP_EMP_RESIGN <> 'Y'
        AND EMPINFO.EP_EMP_DOJ <= CONVERT(VARCHAR(8),GETDATE(),112)  
        AND EMPINFO.EP_EMP_LEVEL > '10' 
        AND EMPINFO.EP_EMP_LEVEL <> ''
        AND (SHIFTCAL.EP_SHIFT = 'D1' OR SHIFTCAL.EP_SHIFT = 'NS')
        AND FIRSTSCAN.EP_EMP_ID IS NULL    --// when detect the EMP_ID scan is null , the result will show.
     ORDER BY EMPINFO.EP_EMP_DEPT,EMPINFO.EP_EMP_SECTION,EMPINFO.EP_EMP_TYPE,EMPINFO.EP_EMP_ID

    The Emp ScanIN/OUT time will catch GetDate() in SQL, so now how to merge the start-date & end-date with FIRSTSCAN.EP_EMP_ID when IS NULL(absent or onleave), so that it will show the proper result?

    Monday, September 02, 2013 9:46 AM

All replies

  • Please post a concise and complete example. Include table DDL (only with columns which show your date problem) and sample data..
    Thursday, September 05, 2013 12:07 PM
  • Dear Stefan Hoffman,

    Thank u for reply, 

    As below table show the result when Employee absent . this result  query is based on above SQL Script i posted. 

    so now i want join with table Apply leave  when Employee absent and apply leave on the day, it will show the actual result. kindly advise, thank you

    Friday, September 06, 2013 1:42 AM