Re: SQL Server 2012 - forming relationship between tables - Database Discussion Boards

caulsonchua3-Jul-13 22:13

3-Jul-13 22:13

i had plot data from Entry Pass Scan in / out system into Database and write calculation in SQL script to generate Report in Aspx web page.

i had different type of shift pattern following by

EP_SHIFT_NAME EP_SHIFT_DESC EP_SHIFT_TIMEFR EP_SHIFT_TIMETO R1 Off Day 09:00 23:59 W1 W1 08:00 17:00 R2 Rest Day 09:00 23:59 D1 Day Shift 07:00 16:00 NS Normal Shift 08:00 17:30 E1 Day Shift 08:00 17:00 E2 Night Shift 20:00 05:00 N1 Night Shift 19:00 04:00

Currently i had facing a problem which is

a)Total actual working minute(as Work MIn) is 720min per day[12 hours per day] (if more than 720 minutes working hours is consider under category Extra OT hour).

b) Cut off the scan in and out time from 0700 ~ 1900 (DAY) and 1900 ~ 0700(NIGHT) calculation.

However, if the person late in or early out the calculation of the time is base from the last and the first scan.

Anyone can help me or amend the calculation in SQL Script ?

For the Work Hour calculation, i have a idea which is catch the (last scan time - EP_SHIFT_TIMEFROM) for every shift type

Kindly advise , thank you

SELECT CONVERT(VARCHAR(8),STUFF(STUFF(FIRSTSCAN.EP_SCAN_DATE,12,0,':'),15,0,':'),112) AS SCANDATE
           ,FIRSTSCAN.EP_EMP_COMPANY
           ,FIRSTSCAN.EP_EMP_DEPT
            ,FIRSTSCAN.EP_EMP_ID
           ,FIRSTSCAN.EP_EMP_NAME
            ,FIRSTSCAN.EP_EMP_SECTION
            ,FIRSTSCAN.EP_EMP_SHIFT
            ,FIRSTSCAN.EP_SHIFT
            ,right(FIRSTSCAN.EP_SCAN_DATE,6) AS FIRSTSCAN
           ,right(LASTSCAN.EP_SCAN_DATE,6) AS LASTSCAN
            ,SCANTIMECAL.INFAB AS INFAB_MIN
            ,SCANTIMECAL.OUTFAB AS OUTFAB_MIN
           ,DATEDIFF(MI,CAST(STUFF(STUFF(FIRSTSCAN.EP_SCAN_DATE,12,0,':'),15,0,':') AS DATETIME)
           ,CAST(STUFF(STUFF(LASTSCAN.EP_SCAN_DATE,12,0,':'),15,0,':') AS DATETIME)) AS WORK_MIN
           ,DATEDIFF(HOUR,CAST(STUFF(STUFF(FIRSTSCAN.EP_SCAN_DATE,12,0,':'),15,0,':') AS DATETIME)
           ,CAST(STUFF(STUFF(LASTSCAN.EP_SCAN_DATE,12,0,':'),15,0,':') AS DATETIME)) AS WORK_HOUR

            ,CASE WHEN FIRSTSCAN.EP_SHIFT <> 'R1' AND FIRSTSCAN.EP_SHIFT <> 'R2' 
              THEN ROUND(CAST((DATEDIFF(MINUTE,CAST(STUFF(STUFF((CASE WHEN SHIFTCAL.EP_SHIFT = 'N1' 
              THEN CONVERT(VARCHAR(8),DATEADD(DAY,+1,LEFT(FIRSTSCAN.EP_SCAN_DATE ,8)),112) + ' ' + 
              REPLACE(CONVERT(VARCHAR(8),DATEADD(HOUR,+0,SHIFTDESC.EP_SHIFT_TIMETO + ':00'),108),':','') 
               ELSE LEFT(FIRSTSCAN.EP_SCAN_DATE ,8) + ' ' + 
               REPLACE(CONVERT(VARCHAR(8),DATEADD(HOUR,+0,SHIFTDESC.EP_SHIFT_TIMETO + ':00'),108),':','') END),12,0,':'),15,0,':') AS DATETIME)
                ,CAST(STUFF(STUFF(LASTSCAN.EP_SCAN_DATE,12,0,':'),15,0,':') AS DATETIME)) / 60.0) AS FLOAT),2) 
                ELSE ROUND(CAST((DATEDIFF(MINUTE,CAST(STUFF(STUFF(FIRSTSCAN.EP_SCAN_DATE,12,0,':'),15,0,':') AS DATETIME)
                ,CAST(STUFF(STUFF(LASTSCAN.EP_SCAN_DATE,12,0,':'),15,0,':') AS DATETIME)) / 60.0) AS FLOAT),2) END AS OTWORK_HOUR

            ,CASE WHEN (FIRSTSCAN.EP_SHIFT = 'N1' OR FIRSTSCAN.EP_SHIFT = 'D1') 
            AND ROUND(CAST((DATEDIFF(MINUTE,CAST(STUFF(STUFF((CASE WHEN SHIFTCAL.EP_SHIFT = 'N1' 
             THEN CONVERT(VARCHAR(8),DATEADD(DAY,+1,LEFT(FIRSTSCAN.EP_SCAN_DATE ,8)),112) + ' ' + 
             REPLACE(CONVERT(VARCHAR(8),DATEADD(HOUR,+0,SHIFTDESC.EP_SHIFT_TIMETO + ':00'),108),':','') 
               ELSE LEFT(FIRSTSCAN.EP_SCAN_DATE ,8) + ' ' + 
               REPLACE(CONVERT(VARCHAR(8),DATEADD(HOUR,+0,SHIFTDESC.EP_SHIFT_TIMETO + ':00'),108),':','') END),12,0,':'),15,0,':') AS DATETIME)
                ,CAST(STUFF(STUFF(LASTSCAN.EP_SCAN_DATE,12,0,':'),15,0,':') AS DATETIME)) / 60.0) AS FLOAT),2) >= 3 THEN 3 - 0.25 END OTHOUR_FIX


            ,CASE WHEN (FIRSTSCAN.EP_SHIFT = 'N1' OR FIRSTSCAN.EP_SHIFT = 'D1') AND ROUND(CAST((DATEDIFF(MINUTE,CAST(STUFF(STUFF((CASE WHEN SHIFTCAL.EP_SHIFT = 'N1' 
            THEN CONVERT(VARCHAR(8),DATEADD(DAY,+1,LEFT(FIRSTSCAN.EP_SCAN_DATE ,8)),112) + ' ' + 
              REPLACE(CONVERT(VARCHAR(8),DATEADD(HOUR,+0,SHIFTDESC.EP_SHIFT_TIMETO + ':00'),108),':','') 
               ELSE LEFT(FIRSTSCAN.EP_SCAN_DATE ,8) + ' ' + REPLACE(CONVERT(VARCHAR(8),DATEADD(HOUR,+0,SHIFTDESC.EP_SHIFT_TIMETO + ':00'),108),':','') END),12,0,':'),15,0,':') AS DATETIME)
               ,CAST(STUFF(STUFF(LASTSCAN.EP_SCAN_DATE,12,0,':'),15,0,':') AS DATETIME)) / 60.0) AS FLOAT),2) >= 3 
                THEN ROUND(CAST((DATEDIFF(MINUTE,CAST(STUFF(STUFF((CASE WHEN SHIFTCAL.EP_SHIFT = 'N1' 
                THEN CONVERT(VARCHAR(8),DATEADD(DAY,+1,LEFT(FIRSTSCAN.EP_SCAN_DATE ,8)),112) + ' ' + 
               REPLACE(CONVERT(VARCHAR(8),DATEADD(HOUR,+0,SHIFTDESC.EP_SHIFT_TIMETO + ':00'),108),':','') 
                ELSE LEFT(FIRSTSCAN.EP_SCAN_DATE ,8) + ' ' + 
                REPLACE(CONVERT(VARCHAR(8),DATEADD(HOUR,+0,SHIFTDESC.EP_SHIFT_TIMETO + ':00'),108),':','') END),12,0,':'),15,0,':') AS DATETIME),
                 CAST(STUFF(STUFF(LASTSCAN.EP_SCAN_DATE,12,0,':'),15,0,':') AS DATETIME)) / 60.0) AS FLOAT) - 3,2) ELSE 
                 CASE WHEN (FIRSTSCAN.EP_SHIFT = 'R1' OR FIRSTSCAN.EP_SHIFT = 'R2') THEN ROUND(CAST((DATEDIFF(MINUTE,CAST(STUFF(STUFF(FIRSTSCAN.EP_SCAN_DATE,12,0,':'),15,0,':') AS DATETIME)
                 ,Cast(STUFF(Stuff(LASTSCAN.EP_SCAN_DATE,12,0,':'),15,0,':') AS DATETIME)) / 60.0) AS FLOAT),2)END END OTHOUR_EXTRA



            ,CASE WHEN FIRSTSCAN.EP_SHIFT <> 'R1' AND FIRSTSCAN.EP_SHIFT <> 'R2' THEN FIRSTSCAN.LATEIN END LATEIN
            ,CASE WHEN FIRSTSCAN.EP_SHIFT <> 'R1' AND FIRSTSCAN.EP_SHIFT <> 'R2' THEN CASE WHEN SCANTIMECAL.OUTFAB >= SHIFTDESC.EP_SHIFT_OGRACE THEN 1 END END AS BREAK_ABNORMAL
           ,CASE WHEN FIRSTSCAN.EP_SHIFT <> 'R1' AND FIRSTSCAN.EP_SHIFT <> 'R2' THEN CASE WHEN RIGHT(LASTSCAN.EP_SCAN_DATE,6) < REPLACE(CONVERT(VARCHAR(8),
             CONVERT(VARCHAR(8),SHIFTDESC.EP_SHIFT_TIMETO,108),108),':','') THEN 1 END END AS EARLYOUT_NORMAL
           ,(CASE WHEN FIRSTSCAN.EP_SHIFT<> 'NS' AND FIRSTSCAN.EP_SHIFT <> 'R1' AND FIRSTSCAN.EP_SHIFT <> 'R2' 
           THEN (CASE WHEN RIGHT(LASTSCAN.EP_SCAN_DATE,6) < REPLACE(CONVERT(VARCHAR(8),DATEADD(HOUR,+3,CONVERT(VARCHAR(8),SHIFTDESC.EP_SHIFT_TIMETO,108)),108),':','') THEN 1 END) END)AS EARLYOUT_SHIFT

            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) 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
            ,SCANHIST.EP_SCAN_ID
            ,SCANHIST.EP_TRANS_LOC
            ,CASE WHEN RIGHT(SCANHIST.EP_SCAN_DATE,6) > REPLACE(SHIFTDESC.EP_SHIFT_TIMEFR,':','') THEN 1 END AS LATEIN
            FROM [AcmkIMS].[dbo].[EP_SCAN_HIST] SCANHIST

            JOIN [AcmkIMS].[dbo].[EP_EMP_INFO] EMPINFO
            ON EMPINFO.EP_EMP_ID = SCANHIST.EP_EMP_ID


            JOIN [AcmkIMS].[dbo].[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 [AcmkIMS].[dbo].[EP_SHIFT_DESC] SHIFTDESC
            ON SHIFTDESC.EP_SHIFT_NAME = SHIFTCAL.EP_SHIFT

            WHERE 1=1
            AND SCANHIST.EP_SCAN_DATE >= '20130524' + ' ' + 
            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,'20130526'),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 '20130526' + ' ' + REPLACE(CONVERT(VARCHAR(8),DATEADD(HOUR,+0,SHIFTDESC.EP_SHIFT_TIMETO + ':00'),108),':','') 
            ELSE '20130526' + ' ' + 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 SCANHIST.EP_EMP_DEPT = ''
           --AND SCANHIST.EP_EMP_SECTION = ''
           --AND SCANHIST.EP_EMP_SHIFT = ''
           --AND SCANHIST.EP_EMP_ID = ''
           AND SCANHIST.EP_EMP_SHIFT ='A'
            )FIRSTSCAN



            OUTER APPLY
            (
            SELECT TOP 1
            SCANHIST.EP_SCAN_DATE
            ,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
            ,SCANHIST.EP_SCAN_ID
            ,SCANHIST.EP_TRANS_LOC
            FROM [AcmkIMS].[dbo].[EP_SCAN_HIST] SCANHIST

             JOIN [AcmkIMS].[dbo].[EP_EMP_INFO] EMPINFO
            ON EMPINFO.EP_EMP_ID = SCANHIST.EP_EMP_ID


          JOIN [AcmkIMS].[dbo].[EP_SHIFT_CALENDAR] SHIFTCAL
         ON SHIFTCAL.EP_SHIFT_NAME = SCANHIST.EP_EMP_SHIFT
            AND SHIFTCAL.EP_SHIFT_DATE = LEFT(FIRSTSCAN.EP_SCAN_DATE,8)

           JOIN [AcmkIMS].[dbo].[EP_SHIFT_DESC] SHIFTDESC
            ON SHIFTDESC.EP_SHIFT_NAME = SHIFTCAL.EP_SHIFT

        WHERE 1=1

           AND SCANHIST.EP_SCAN_DATE > FIRSTSCAN.EP_SCAN_DATE
           AND SCANHIST.EP_SCAN_DATE < CASE WHEN (FIRSTSCAN.EP_SHIFT = 'N1') 
           THEN CONVERT(VARCHAR(8),DATEADD(DAY,+1,LEFT(FIRSTSCAN.EP_SCAN_DATE,8)),112) + ' ' + 
           REPLACE(CONVERT(VARCHAR(8),DATEADD(HOUR,+6,SHIFTDESC.EP_SHIFT_TIMETO + ':00'),108),':','') 
           else CASE WHEN (FIRSTSCAN.EP_SHIFT = 'R1' OR FIRSTSCAN.EP_SHIFT = 'R2') 
           THEN 
           left(FIRSTSCAN.EP_SCAN_DATE,8) + ' ' + 
           REPLACE(CONVERT(VARCHAR(8),DATEADD(HOUR,+0,SHIFTDESC.EP_SHIFT_TIMETO + ':00'),108),':','') ELSE 
           left(FIRSTSCAN.EP_SCAN_DATE,8) + ' ' + 
           REPLACE(CONVERT(VARCHAR(8),DATEADD(HOUR,+6,SHIFTDESC.EP_SHIFT_TIMETO + ':00'),108),':','') END END
           AND SCANHIST.EP_TRANS_LOC = 'OUT'
           AND EMPINFO.EP_EMP_LEVEL > '10'
           AND EMPINFO.EP_EMP_LEVEL <> ''
           AND SCANHIST.EP_EMP_ID = FIRSTSCAN.EP_EMP_ID

         ORDER BY SCANHIST.EP_SCAN_DATE  )LASTSCAN  




         OUTER APPLY
          (
            SELECT 
             GROUP_SCANTIMECAL.EP_EMP_ID
        ,SUM(CAST(GROUP_SCANTIMECAL.INFAB_MIN AS FLOAT)) AS INFAB
            ,SUM(CAST(GROUP_SCANTIMECAL.OUTFAB_MIN AS FLOAT)) AS OUTFAB
            FROM
            (
          SELECT SCANHIST.EP_SCAN_DATE
            ,SCANHIST.EP_EMP_ID
            ,SCANHIST.EP_EMP_NAME
            ,SCANHIST.EP_EMP_DEPT
            ,SCANHIST.EP_EMP_SECTION
            ,SCANHIST.EP_EMP_SHIFT
           ,SCANHIST.EP_TRANS_LOC
            ,DATEDIFF(MI,CAST(STUFF(STUFF(SCANHIST.EP_SCAN_DATE,12,0,':'),15,0,':') AS DATETIME),
            CAST(STUFF(STUFF(NEXTSCAN.EP_SCAN_DATE,12,0,':'),15,0,':') AS DATETIME)) AS INFAB_MIN
            ,DATEDIFF(MI,CAST(STUFF(STUFF(NEXTSCAN.EP_SCAN_DATE,12,0,':'),15,0,':') AS DATETIME),
            CAST(STUFF(STUFF(PREVSCAN.EP_SCAN_DATE,12,0,':'),15,0,':') AS DATETIME)) AS OUTFAB_MIN
            FROM [AcmkIMS].[dbo].[EP_SCAN_HIST] SCANHIST

            OUTER APPLY
            (
            SELECT TOP 1
             NEXTSCAN.EP_SCAN_DATE
            ,NEXTSCAN.EP_EMP_ID
            ,NEXTSCAN.EP_EMP_NAME
            ,NEXTSCAN.EP_EMP_DEPT
            ,NEXTSCAN.EP_EMP_SECTION
            ,NEXTSCAN.EP_EMP_SHIFT
            ,NEXTSCAN.EP_SCAN_ID
            ,NEXTSCAN.EP_TRANS_DESC
            ,NEXTSCAN.EP_TRANS_LOC
            FROM [AcmkIMS].[dbo].[EP_SCAN_HIST] NEXTSCAN

            JOIN [AcmkIMS].[dbo].[EP_EMP_INFO] EMPINFO
            ON EMPINFO.EP_EMP_ID = NEXTSCAN.EP_EMP_ID


            JOIN [AcmkIMS].[dbo].[EP_SHIFT_CALENDAR] SHIFTCAL
            ON SHIFTCAL.EP_SHIFT_NAME = NEXTSCAN.EP_EMP_SHIFT
            AND SHIFTCAL.EP_SHIFT_DATE = LEFT(FIRSTSCAN.EP_SCAN_DATE,8)

           JOIN [AcmkIMS].[dbo].[EP_SHIFT_DESC] SHIFTDESC
           ON SHIFTDESC.EP_SHIFT_NAME = SHIFTCAL.EP_SHIFT

           WHERE 1=1
          AND SCANHIST.EP_SCAN_ID = NEXTSCAN.EP_SCAN_ID
           AND NEXTSCAN.EP_SCAN_DATE > SCANHIST.EP_SCAN_DATE
          AND NEXTSCAN.EP_SCAN_DATE < CASE WHEN (FIRSTSCAN.EP_SHIFT = 'N1')
           THEN CONVERT(VARCHAR(8),DATEADD(DAY,+1,LEFT(NEXTSCAN.EP_SCAN_DATE,8)),112) + ' ' + 
           REPLACE(CONVERT(VARCHAR(8),DATEADD(HOUR,+6,SHIFTDESC.EP_SHIFT_TIMETO + ':00'),108),':','') 
           ELSE CASE WHEN (FIRSTSCAN.EP_SHIFT = 'R1' OR FIRSTSCAN.EP_SHIFT = 'R2') 
           THEN 
           LEFT(NEXTSCAN.EP_SCAN_DATE,8) + ' ' + 
           REPLACE(CONVERT(VARCHAR(8),DATEADD(HOUR,+0,SHIFTDESC.EP_SHIFT_TIMETO + ':00'),108),':','') 
           ELSE 
           LEFT(NEXTSCAN.EP_SCAN_DATE,8) + ' ' + 
           REPLACE(CONVERT(VARCHAR(8),DATEADD(HOUR,+6,SHIFTDESC.EP_SHIFT_TIMETO + ':00'),108),':','') END END
          AND NEXTSCAN.EP_TRANS_LOC = 'OUT'
          ORDER BY NEXTSCAN.EP_SCAN_DATE
            )NEXTSCAN

            OUTER APPLY
          (
            SELECT TOP 1
           PREVSCAN.EP_SCAN_DATE
            ,PREVSCAN.EP_EMP_ID
            ,PREVSCAN.EP_EMP_NAME
            ,PREVSCAN.EP_EMP_DEPT
            ,PREVSCAN.EP_EMP_SECTION
            ,PREVSCAN.EP_EMP_SHIFT
            ,PREVSCAN.EP_SCAN_ID
            ,PREVSCAN.EP_TRANS_DESC
            ,PREVSCAN.EP_TRANS_LOC
            FROM [AcmkIMS].[dbo].[EP_SCAN_HIST] PREVSCAN

            JOIN [AcmkIMS].[dbo].[EP_EMP_INFO] EMPINFO
             ON EMPINFO.EP_EMP_ID = PREVSCAN.EP_EMP_ID

            JOIN [AcmkIMS].[dbo].[EP_SHIFT_CALENDAR] SHIFTCAL
            ON SHIFTCAL.EP_SHIFT_NAME = PREVSCAN.EP_EMP_SHIFT
            AND SHIFTCAL.EP_SHIFT_DATE = LEFT(FIRSTSCAN.EP_SCAN_DATE,8)

            JOIN [AcmkIMS].[dbo].[EP_SHIFT_DESC] SHIFTDESC
            ON SHIFTDESC.EP_SHIFT_NAME = SHIFTCAL.EP_SHIFT

           WHERE 1=1
           AND SCANHIST.EP_SCAN_ID = PREVSCAN.EP_SCAN_ID
            AND PREVSCAN.EP_SCAN_DATE > SCANHIST.EP_SCAN_DATE
           AND PREVSCAN.EP_SCAN_DATE < CASE WHEN (FIRSTSCAN.EP_SHIFT = 'N1') 
           THEN CONVERT(VARCHAR(8),DATEADD(DAY,+1,LEFT(SCANHIST.EP_SCAN_DATE,8)),112) + ' ' + 
           REPLACE(CONVERT(VARCHAR(8),DATEADD(HOUR,+6,SHIFTDESC.EP_SHIFT_TIMETO + ':00'),108),':','') 
           ELSE CASE WHEN (FIRSTSCAN.EP_SHIFT = 'R1' OR FIRSTSCAN.EP_SHIFT = 'R2') 
            THEN 
            LEFT(SCANHIST.EP_SCAN_DATE,8) + ' ' + 
            REPLACE(CONVERT(VARCHAR(8),DATEADD(HOUR,+0,SHIFTDESC.EP_SHIFT_TIMETO + ':00'),108),':','') 
            ELSE 
            LEFT(SCANHIST.EP_SCAN_DATE,8) + ' ' + 
            REPLACE(CONVERT(VARCHAR(8),DATEADD(HOUR,+6,SHIFTDESC.EP_SHIFT_TIMETO + ':00'),108),':','') END END
           AND PREVSCAN.EP_TRANS_LOC = 'IN'
            ORDER BY PREVSCAN.EP_SCAN_DATE
            )PREVSCAN

             JOIN [AcmkIMS].[dbo].[EP_EMP_INFO] EMPINFO
             ON EMPINFO.EP_EMP_ID = SCANHIST.EP_EMP_ID


            JOIN [AcmkIMS].[dbo].[EP_SHIFT_CALENDAR] SHIFTCAL
            ON SHIFTCAL.EP_SHIFT_NAME = SCANHIST.EP_EMP_SHIFT
           AND SHIFTCAL.EP_SHIFT_DATE = LEFT(FIRSTSCAN.EP_SCAN_DATE,8)

            JOIN [AcmkIMS].[dbo].[EP_SHIFT_DESC] SHIFTDESC
           ON SHIFTDESC.EP_SHIFT_NAME = SHIFTCAL.EP_SHIFT

         WHERE 1=1
            AND SCANHIST.EP_SCAN_DATE >= FIRSTSCAN.EP_SCAN_DATE
           AND SCANHIST.EP_SCAN_DATE < CASE WHEN (FIRSTSCAN.EP_SHIFT = 'N1') 
             THEN CONVERT(VARCHAR(8),DATEADD(DAY,+1,LEFT(FIRSTSCAN.EP_SCAN_DATE,8)),112) + ' ' +
               REPLACE(CONVERT(VARCHAR(8),DATEADD(HOUR,+6,SHIFTDESC.EP_SHIFT_TIMETO + ':00'),108),':','') ELSE
             CASE WHEN (FIRSTSCAN.EP_SHIFT = 'R1' OR FIRSTSCAN.EP_SHIFT = 'R2')
              THEN 
              LEFT(FIRSTSCAN.EP_SCAN_DATE,8) + ' ' + 
             REPLACE(CONVERT(VARCHAR(8),DATEADD(HOUR,+0,SHIFTDESC.EP_SHIFT_TIMETO + ':00'),108),':','')
              ELSE 
              LEFT(FIRSTSCAN.EP_SCAN_DATE,8) + ' ' +
                REPLACE(CONVERT(VARCHAR(8),DATEADD(HOUR,+6,SHIFTDESC.EP_SHIFT_TIMETO + ':00'),108),':','') END END
            AND SCANHIST.EP_EMP_ID = FIRSTSCAN.EP_EMP_ID
            AND SCANHIST.EP_TRANS_LOC = 'IN'
            )GROUP_SCANTIMECAL

           GROUP BY GROUP_SCANTIMECAL.EP_EMP_ID


              )SCANTIMECAL

         JOIN [AcmkIMS].[dbo].[EP_SHIFT_CALENDAR] SHIFTCAL
           ON SHIFTCAL.EP_SHIFT_NAME = FIRSTSCAN.EP_EMP_SHIFT
            AND SHIFTCAL.EP_SHIFT_DATE = LEFT(FIRSTSCAN.EP_SCAN_DATE,8)

      JOIN [AcmkIMS].[dbo].[EP_SHIFT_DESC] SHIFTDESC
            ON SHIFTDESC.EP_SHIFT_NAME = SHIFTCAL.EP_SHIFT

             WHERE 1=1
           AND FIRSTSCAN.RowNum = 1
           AND CONVERT(VARCHAR(8),STUFF(STUFF(FIRSTSCAN.EP_SCAN_DATE,12,0,':'),15,0,':'),112) BETWEEN '20130524' AND '20130526'

           ORDER BY 
           FIRSTSCAN.EP_EMP_ID

Re: Calculation For Time Attendance System

Mycroft Holmes4-Jul-13 23:26

Mycroft Holmes

4-Jul-13 23:26

You are seriously hoping someone will go through that heap of... good luck with that. Try breaking it down to smaller bits that you can work over in isolation and then put the bits together to solve the issue or at least isolate the problem.

Loading in a dump like that is not going to get you a positive response.

Never underestimate the power of human stupidity
RAH

Re: Calculation For Time Attendance System

Amol_B7-Jul-13 20:58

Amol_B

7-Jul-13 20:58

What you have tried so far?

Re: Calculation For Time Attendance System

caulsonchua7-Jul-13 21:13

caulsonchua

7-Jul-13 21:13

I had try out is IN FAB time is out time - In time and Out FAB time is In time - Out time. the problem is now dont know how to apply the logic in my SQL script. kindly advise, thank you

sqlserver 2005 connection problem

hamadam212-Jul-13 23:13

hamadam21

2-Jul-13 23:13

I have a sqlserver 2005 on a private LAN...the server has a private IP...the network is connected to the internet throw a (TMG) firewall which has a real IP.....I have a computer outside my LAN and has a real IP...this computer has a C# program and need to connect to the SQLserver to read data from it...how can i make this computer to connect to the SQLserver since it has a real IP but the SQLserver has a private IP on another network?

Re: sqlserver 2005 connection problem

Eddy Vluggen3-Jul-13 0:30

Eddy Vluggen

3-Jul-13 0:30

Enable the TCP/IP protocol (see configuration).
Open port 1433 on your firewall.
Setup port forwarding on your router.

..and start praying; a SQL Server directly connected to the internet is a bad idea. Expect quite some people poking the server to check its security.

The "best" way to expose your database would be by using webservices Smile | :)

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

SQL Server 2012 - forming relationship between tables

RickBStewart2-Jul-13 10:53

RickBStewart

2-Jul-13 10:53

This is my first database project and I am having difficulties forming the correct relationship between two tables. The database is storing inventory records for approximately 20 buildings, and the room numbers in many of the building are numbered the same.

One table has columns like Asset Number, PO Number, Equipment Type, Manufacturer, Model Number, Serial Number. The PK is the Asset Number.

The second table has Building Name, Room Number, Room Type, Staff Name. The key for this table is a combination of Building Name and Room Number. I need both to uniquely identify the location.

My question is, how do I go about creating the relationship between the tables. The relationship would be described as each Asset having a unique Building/Room Number, but each Building/Room Number can have many Assets.

When I try to create the one-to-many relationship it tells me "Both sides of a relationship must have the same number of columns"

Re: SQL Server 2012 - forming relationship between tables

Richard Deeming2-Jul-13 11:06

Richard Deeming

2-Jul-13 11:06

All of the primary key columns from the table on the "one" side need to be present in the table on the "many" side. In this case, you would add both building name and room number to your assets table, and specify both columns in the FOREIGN KEY relationship.

I'd be inclined to move the buildings out to a separate table with a surrogate key, since the value is likely to be repeated across many rows in the rooms table. Similarly, room type and staff name might be better in their own lookup tables, depending on your data.

Buildings
---------
BuildingId (PK)
BuildingName

Rooms
-----
BuildingId (PK, FK->Buildings)
RoomNumber (PK)
...

Assets
------
AssetNumber (PK)
BuildingId (FK->Rooms)
RoomNumber (FK->Rooms)
...

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

Re: SQL Server 2012 - forming relationship between tables

RickBStewart2-Jul-13 11:47

RickBStewart

2-Jul-13 11:47

Thank You Richard, I will take your suggestion and do a bit of experimentation. It's clearly the case that a few hours of video at an online training site has not fully educated me. LOL... But it may be enough to get me through this project. I will try and find ( and study ) some documentation on database theory when I get a chance. But until then... Thanks

Re: SQL Server 2012 - forming relationship between tables

Mycroft Holmes2-Jul-13 13:01

Mycroft Holmes

2-Jul-13 13:01

As a STRONG suggestion you should devote a couple of days to a basic database design book. The consequences of getting your data structure wrong will cost you many multiples of that investment down the track.

Depending on the criticality of the project I would even consider getting a professional to do the basic DB design. There is also a site with sample schemas (I have lost the link) that will give you some excellent pointers.

Never underestimate the power of human stupidity
RAH

Re: SQL Server 2012 - forming relationship between tables

Ralph D. Wilson II10-Jul-13 6:50

Ralph D. Wilson II

10-Jul-13 6:50

To somewhat piggy-back on the previous suggestions, I would recommend that you consider using an Identity column for your Primary Key and then using the PK to accomplish the links beween tables.

For Example:

Table: Buildings
BuildingID [int] Primary Key Identity (1-1)
BuildingName [VarChar] (25)
...

Table: BuildingRooms
BuildingRoomID [int] Primary Key Identity (1-1)
BuildingID INT
RoomName [Varchar] (10)
...

Table: Assets
AssetID [int] Primary Key Identity (1-1)
AssetNumber [varchar] (25)
AssetName [VarChar] (50)
BuildingRoomID [int]
...

By using using the Identity columns for the PK's of the tables, you can make your joins much more easily. You may also want to add some Unique Indexes on, for instance, the BuildingName, RoomName, and AssetNumber columns just to make sure someone doesn't accidentally add those items multiple times. Wink | ;-)

Check out the following link regarding what is termed "normalization" . . . which is what we have suggested with regard to your tables. Wink | ;-)

http://www.dbnormalization.com/rules-of-normalization-i

Re: SQL Server 2012 - forming relationship between tables

RickBStewart10-Jul-13 9:29

RickBStewart

10-Jul-13 9:29

I've spent some time looking into normalization and currently my tables should be in 3rd Normal Form. I also have my application talking to the remote SQL server via the Entity Framework, so I'm getting there. Thanks all for your suggestions, they were most helpful.

Rick...

Default Button is not working using different (skinid)

rubonkumar2-Jul-13 0:02

rubonkumar

2-Jul-13 0:02

I using three textbox(txtName,txtmobile,txtremark)and two skinid for this textboxs(snTextBox,snTextBoxNumeric)If cursor focused in txtname or txtremark mean defaultbutton is working,while its in txtmobile mean defaultbutton is not working because skinid for that textbox is different.

Re: Default Button is not working using different (skinid)

Eddy Vluggen2-Jul-13 0:31

Eddy Vluggen

2-Jul-13 0:31

This is the database-forum. What does your question have to do with databases?

FWIW, you'd also need to explain which type of GUI you're building; ASP.NET, WinForms, WPF, Gtk# - otherwise you'll be getting answers that assume the wrong technology.

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

Re: Default Button is not working using different (skinid)

Mycroft Holmes2-Jul-13 1:23

Mycroft Holmes

2-Jul-13 1:23

Eddy Vluggen wrote:
What does your question have to do with databases?

Access

I'm betting it is a miss post but some people call Access a database, much to my disgust!

Never underestimate the power of human stupidity
RAH

Re: Default Button is not working using different (skinid)

Shameel8-Jul-13 19:29

Shameel

8-Jul-13 19:29

If you define a database as something that stores data, then by all means Access is indeed a database. It even stores data in a relational format and therefore qualifies to be called a Relational database as well.

Re: Default Button is not working using different (skinid)

Mycroft Holmes8-Jul-13 21:26

Mycroft Holmes

8-Jul-13 21:26

I don't consider "stores data" as a definition of a database! Word, excel, xml, CSV, bin with screwed up paper in it all store data!

Never underestimate the power of human stupidity
RAH

Date difference with total years, months and days

maxRazar1-Jul-13 18:37

maxRazar

1-Jul-13 18:37

How can I obtain total number of years, months and days between 2 dates in a single sql query.

eg :

date1 = 2012-07-02
date2 = 2013-09-04

difference between date1 and date2 should show as "1 year, 2 months and 2 days"

Datediff function returns difference only by years or months or days etc...how can i do that

Re: Date difference with total years, months and days

Eddy Vluggen2-Jul-13 0:32

Eddy Vluggen

2-Jul-13 0:32

maxRazar wrote:
difference between date1 and date2 should show as "1 year, 2 months and 2 days"

That's usually not done at the SQL-level, but in code. The SQL-statement would return the difference (just subtract one from the other). It's a bad idea to format data in Sql.

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

Re: Date difference with total years, months and days

Mycroft Holmes2-Jul-13 1:21

Mycroft Holmes

2-Jul-13 1:21

I'm with Eddy on this one do your formatting in the UI.

However did it occur to you to do the datediff 3 times getting the Y/M/D separately and concatenate them as you want.

Never underestimate the power of human stupidity
RAH

Re: Date difference with total years, months and days

Richard Deeming2-Jul-13 1:35

Richard Deeming

2-Jul-13 1:35

Mycroft Holmes wrote:
do the datediff 3 times getting the Y/M/D separately and concatenate them as you want.

That won't work. Consider:

SQL

declare @start date = '20121231';
declare @end date = '20130101';
select
   cast(datediff(year, @start, @end) as varchar(5)) + ' year, '
   + cast(datediff(month, @start, @end) as varchar(5)) + ' month, '
   + cast(datediff(day, @start, @end) as varchar(5)) + ' day'
;

The output will be 1 year, 1 month, 1 day, which is obviously wrong.

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

Re: Date difference with total years, months and days

Tim Carmichael2-Jul-13 1:57

Tim Carmichael

2-Jul-13 1:57

As stated, the query you showed will provide the wrong results, however, that doesn't mean datediff 3 times can't be used. There is just more to it.

Tim

Re: Date difference with total years, months and days

Mycroft Holmes2-Jul-13 2:20

Mycroft Holmes

2-Jul-13 2:20

You are right, a simple datediff 3 times will not do it, however with the application of a little imagination and some work with SSMS I know you can do this, I have seen it done in an example somewhere but dammed if I am willing to chase it down.

Never underestimate the power of human stupidity
RAH

Re: Date difference with total years, months and days

Tim Carmichael2-Jul-13 1:59

Tim Carmichael

2-Jul-13 1:59

As other's have said, formatting shouldn't be done in SQL. However, if that is the requirment of the application, other replies do provide some insight.

Post what you have and then it can be reviewed and commented on.

Tim

Keep Client QL DB Updated

Kevin Marois1-Jul-13 7:02

Kevin Marois

1-Jul-13 7:02

I am in the middle of development on a WPF/SQL 2008 project.

As I make changes to the application I need to push changes to my client for testing. So I can create a ClickOnce for the binaries, but what's the right way to push DB changes?

Thanks

If it's not broken, fix it until it is

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.