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

[#1]

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:

ID	EntryDT	ExitDT	StaffName	WorkingHrs
1	Unknown	21/11/12 23:01:00	John	Unknown
2	21/11/12 23:10:05	22/11/12 07:20:00	Peter	8hrs10mins
3	22/11/12 07:19:00	22/11/12 17:00:00	John	9hrs41mins
4	22/11/12 07:30:00	22/11/12 19:01:00	Joe	11hrs31min
5	22/11/12 08:00:00	22/11/12 19:30:00	Mike	11hrs30min
6	22/11/12 22:00:00	23/11/12 08:00:00	Mary	10hrs
7	23/11/12 07:01:00	Unknown	John	Unknown
8	23/11/12 09:00:00	Unknow	Peter	Unknown

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

[hic]

You need to do this in several steps using peek() and join. You'll find a solution in the attachment.

HIC

[#3]

Thank you so much, i asked this in many forums and only u replied. I need to digest your solutions... not familiar with QlikView sql syntax. I need to translate to normal sql syntax haha.

Thanks so much, i shall try this tml, let u know if it work out

Greatly appreciated,

10e5x

[hic · Accepted solution]

Same script, but now with some comments. Probably easier to understand.

HIC

[#5]

Thank you so much for being so helpful.

I finally understood what u did. Just some slight changes to my requirement: i do not want to catagorise those with multiple clock in or out as invalid, instead i should choosethe latest clock in from the chunk of multiple in as my clockIn and latest out from the chunk as clockOut.

Thanks to your wonderful help, QlikView side is finally gg on.

Btw just trying my luck, are u good with sql statements? i need to do this in sql for ms access too. No obligation. Its out of topic for this forum or do u know of some good forum for me to go to?

Thanks,

10e5x