to insert a data into a table - Page 2

varunkrish · #16 (**permalink**) 06-28-08, 23:49

hi
thnk for ur reply
it represent real time data ,as u say that creating two rows in single table ;it vary if the employee 101 went n times u hav to create n rows,how do we how many employee went outside and come inside

varunkrish · #17 (**permalink**) 06-28-08, 23:51

hi
thnk for ur reply
it represent real time data ,as u say that creating two rows in single table ;it vary if the employee 101 went n times u hav to create n rows,how do we how many employee went outside and come inside

i am sure that entry1<exit 1

varunkrish · #18 (**permalink**) 06-28-08, 23:52

Quote:

Originally Posted by buckeye234

OK, now we may be getting somewhere. Let's assume, for this discussion, that all eid's for the entries being discussed are the same, say 101. Let's also assume that there are two rows in the entry table, referred to as entry1 and entry2, and two rows in the exit table, referred to as exit1 and exit2. Let's also assume that the first row in each table is at a time that is earlier than the second row, for example, entry1 < than entry2.

Given this scenario as a starting point:
1. Does this represent the real world data?
2. Can you gaurantee that entry1 < exit1 and exit1 < entry2 and entry2 < exit2?
3. If that is true, can you say the same is true for any number of rows in the entry table and exit table (Again, assuming the same eid for the rows being considered)?

Asnwer these questions and we'll continue from there.

hi
thnk for ur reply
it represent real time data ,as u say that creating two rows in single table ;it vary if the employee 101 went n times u hav to create n rows,how do we how many employee went outside and come inside
i am sure that entry1<exit1

buckeye234 · #19 (**permalink**) 06-30-08, 08:34

OK, given what I believe to be the way the data exists, here is a select statement that returns the data in the order you want:

Code:

select a.eid, a.entry, b.exit
from t1 as a, t2 as b
where a.eid = b.eid
and b.exit in
( select min(c.exit)
  from t2 as c
  where a.eid = c.eid
  and c.exit > a.entry)

Try this out and see if it works. If it does, you can then turn it into the insert statement that you need. If not, then let me know what is wrong.

Internet Services

Web Development

Digital Marketing

Consumer Tech