PIVOT OR CROSSTABS? I DONT KNOW.

hi everyone,

im very puzzled with this data how to arrange in the way like this.

---

emp_code am_time_in am_time_out pm_time_in pm_time_out

---

the rules is:

1. from 6:30am until 1:30pm.

this will fall into "am_time_in" and "am_time_out".

question: what if you found more than 2 rows between 6:30am to 1:30pm?

figure: 630am, 742am, 844am and 1254noon

answer: just get only the first and last row. so that the the 630am will fall in "am_time_in" column

and 1254noon will be at "am_time_out" column

PLEASE HELP:

from the sample raw data below, how can sql statement will find only first and lastrow between 6:30 - 1:30..some are

2 entries, some are 3 entries and some are 4 or 5.

and after i found those two rows, the first and the last, respectively, how can i aline it in same row.

im sorry if my illustrations is not so clear or my goal is not stated clearly here. i am willing to answer some sort of question to solve my problem.

thank you in advance.

my email address is : liberateddreams@.yahoo.com

---

Here is the raw data in a table separated in each column

empcode day month year time

27 25 1 2007 1657 27 25 1 2007 1657 27 25 1 2007 1657 27 25 1 2007 1658 27 25 1 2007 1847 27 25 1 2007 1847 27 25 1 2007 1929 27 27 1 2007 739 27 27 1 2007 740 27 27 1 2007 741 27 27 1 2007 755 27 27 1 2007 1300 27 27 1 2007 1302 27 27 1 2007 1303 27 27 1 2007 1618 35 25 1 2007 1909 35 25 1 2007 1929 35 27 1 2007 726 35 27 1 2007 1302 43 25 1 2007 1850 43 25 1 2007 1850 43 27 1 2007 720 43 27 1 2007 1559 91 25 1 2007 1649 91 25 1 2007 1649 91 25 1 2007 1659 91 25 1 2007 1830 91 27 1 2007 732

Hi JB,

First, if it is possible to change your table, this will be much easier to handle if you use a single datetime column instead of four (4) separate columns. You will most likely discover that this a not a very good design.

Second, you really should have to way to determine if a row is a 'IN' or an 'OUT' time. For example, if there were only three (3) rows, how would you decide if row #3 was IN or OUT time? Even with two rows, they both could be IN or both could be OUT. How would you know?

Employee 27 in your sample data has seven (7) entries for the 25th, How do you know that he/she didn't return to work at 19:39. It seems like you are assuming that the last entry is OUT -but how do you know?

How do you handle someone that is IN at 22:00, and OUT at 06:00 the next day? As you have presented the problem and the sample data, the assumption would be that 22:00 was IN, there was no OUT, and on the next day, there was an IN at 06:00.

This is Very confusing and will give you a lot of grief unless you make some changes while you can.

|||

I'll have to agree with Arnie on this one. Very confusing.

Unless there is a very good reason to have a field for each value, the table should only have 2 fields empcode and a timestamp which will contain both the date and time.

This will make your life a lot easier.

However, if you choose to work with the current structure, you have military times, what exactly is the question?

Adamus

|||

Revisiting this after I posted, how do you know which is IN and which is OUT?

It seems that you would need a field to distinguish this.

Adamus