Very demanding qns, sry but i need help. SQL statements.
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.
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.
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.
Re: Very demanding qns, sry but i need help. SQL statements.
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?