Database

Re: Copy database from one remote environment to development environment

9-Jul-13 9:39

7-zip usually seems to produce smaller results than regular zip files. On one server, it compressed a 1.3Gb backup file to 55Mb, which took the nightly FTP transfer down from 7 hours to around 15 minutes.

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

vkEE10-Jul-13 3:20

vkEE

10-Jul-13 3:20

Yes, once zipped - the file size from 2 GB has come down to 850MB.

I am trying to do a copy of the file from remote server, literal right click copy, and right click paste on to my laptop. I keep losing the connection.

If we use a FTP tool, would this be the same issue, of losing the file transfer connection?

Thanks for all the ideas and suggestions.

Re: Copy database from one remote environment to development environment

Richard Deeming10-Jul-13 3:45

Re: Copy database from one remote environment to development environment

10-Jul-13 3:45

FTP tends to be more robust than copy+paste in Explorer. However, if you have underlying network problems, you'll still have problems with FTP transfers.

I'd be inclined to give 7zip[^] a go - the maximum compression settings will take a bit longer, but they should be able to get the file size down a lot further.

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

jschell10-Jul-13 8:24

jschell

10-Jul-13 8:24

vkEmerson wrote:
I keep losing the connection.

With a one-shot you just keep trying.

But if this needs to happen on a frequent basis then you should look into getting a restartable transfer mechanism in place.

myisam or innodb? how would you decide?

Jassim Rahma8-Jul-13 5:55

Jassim Rahma

8-Jul-13 5:55

Hi,

I would like to ask how would you decide if you are developing a hospital management software using C# .NEt and MySQL if you should go for myisam or innodb?

Thanks,

Technology News @ www.JassimRahma.com

Re: myisam or innodb? how would you decide?

Eddy Vluggen8-Jul-13 7:24

Re: myisam or innodb? how would you decide?

8-Jul-13 7:24

Jassim Rahma wrote:
a hospital management software using C# .NEt and MySQL

Why MySQL if you're using C#? What platform are you on?

Bastard Programmer from Hell Suspicious | :suss:

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

jschell8-Jul-13 8:43

jschell

8-Jul-13 8:43

InnoDB

You would only choose myisam if you have very specific requirements and if you analyzed the difference and determined that InnoDB would make it impossible.

Re: myisam or innodb? how would you decide?

Richard MacCutchan8-Jul-13 21:34

Richard MacCutchan

8-Jul-13 21:34

Neither, I would start by doing a proper review and analysis of the requirements and then decide which database system would have the features and power to support it. And for a hospital system I doubt that anything less than Oracle or SQL server would be sufficient. It is never a good idea to design your system so that it fits your choice of database and development language.

Use the best guess

Sql injection Attack

Aatif Ali from Bangalore5-Jul-13 3:32

Aatif Ali from Bangalore

5-Jul-13 3:32

What is SQL injection attack...?

Eddy Vluggen5-Jul-13 3:38

SQL Injection Attacks and Some Tips on How to Prevent Them[^]

5-Jul-13 3:38

Bastard Programmer from Hell Suspicious | :suss:

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

Aatif Ali from Bangalore5-Jul-13 21:16

Aatif Ali from Bangalore

5-Jul-13 21:16

Thnks..

Eddy Vluggen6-Jul-13 11:37

6-Jul-13 11:37

Don't thank me, just read the article I linked.

Bastard Programmer from Hell Suspicious | :suss:

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

Richard Deeming5-Jul-13 3:41

5-Jul-13 3:41

http://en.wikipedia.org/wiki/SQL_injection[^]

SQL injection is a technique often used to attack data driven applications. This is done by including portions of SQL statements in an entry field in an attempt to get the website to pass a newly formed rogue SQL command to the database (e.g., dump the database contents to the attacker).

https://www.owasp.org/index.php/SQL_Injection[^]

A SQL injection attack consists of insertion or "injection" of a SQL query via the input data from the client to the application. A successful SQL injection exploit can read sensitive data from the database, modify database data (Insert/Update/Delete), execute administration operations on the database (such as shutdown the DBMS), recover the content of a given file present on the DBMS file system and in some cases issue commands to the operating system. SQL injection attacks are a type of injection attack, in which SQL commands are injected into data-plane input in order to effect the execution of predefined SQL commands.

“That which can be asserted without evidence, can be dismissed without evidence.”

― Christopher Hitchens

Richard Deeming8-Jul-13 1:46

Calculation For Time Attendance System

8-Jul-13 1:46

Aatif Ali from Bangalore wrote:
How to avoid or prevent this attack...?

Use parameterized queries;
Don't use dynamic SQL, use parameterized queries;
Oh, and did I mention: use parameterized queries!

Input validation and other techniques have their place, but the only way to completely avoid SQL injection is to use parameterized queries. If you ever find yourself concatenating strings (other than known constant strings) to build a query, either in client code or a stored procedure, you've left yourself open to SQL injection.

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

caulsonchua3-Jul-13 22:13

caulsonchua

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

SQL Server 2012 - forming relationship between tables

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[^]

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