4 Replies Latest reply: Dec 3, 2012 7:48 PM by guoxiang RSS

    Very demanding qns, sry but i need help. SQL statements.

      Hi all,

       

      I know this may not be totally related to QlikView but without extracting the correct data i cant even proceed.

      I am faced with a very troubled scenario. I am given a .mdb from access 2003. The data are so messy and i am tasked to derive the working hrs. After trying for quite some time, with the help with some pros here, i thought i got it but after sometime i realize i made a serious mistake. Therefore have to post it here, hoping that some pro here can help me derive the sql statements.

       

      TACS table:

      EventDate     StaffName     EventTime     Message

      21/11/12     John            23:01:00     OUT

      21/11/12     Peter            23:10:00     IN

      21/11/12     Peter            23:10:05     IN

      22/11/12     John            07:19:00     IN

      22/11/12     Peter            07:20:00     OUT

      22/11/12     Joe            07:30:00     IN

      22/11/12     Mike            08:00:00     IN

      22/11/12     John            17:00:00     OUT

      22/11/12     John            17:01:00     OUT

      22/11/12     Joe            19:01:00     OUT

      22/11/12     Mike            19:30:00     OUT

      22/11/12     Mary            22:00:00     IN

      23/11/12     John            07:01:00     IN

      23/11/12     Mary            08:00:00     OUT

      23/11/12     Peter            09:00:00     IN

       

       

      What i want to derive.

      FinalTable:

       

      IDEntryDTExitDTStaffNameWorkingHrs
      1Unknown 21/11/12 23:01:00JohnUnknown
      221/11/12 23:10:0522/11/12 07:20:00Peter8hrs10mins
      322/11/12 07:19:0022/11/12 17:00:00John9hrs41mins
      422/11/12 07:30:0022/11/12 19:01:00Joe11hrs31min
      522/11/12 08:00:00 22/11/12 19:30:00 Mike11hrs30min
      622/11/12 22:00:0023/11/12 08:00:00Mary10hrs
      723/11/12 07:01:00UnknownJohnUnknown
      823/11/12 09:00:00UnknowPeterUnknown

       

       

      If there are multiple clock in before out, i will use the latest clockIn as EntryDT, and vice versa, use the latest clockOut as ExitDT. However due to the mess, i dk how to write a statement to allow the prog to know which timing are for which event. Lastly if there are no respective clock in or out, it will be left unknown.

       

       

      Any help will be greatly appreciated.

       

       

      Thanks,

      10e5x