Multiple Tables in ASP

[bjgrem]

I am working on a time clock application, and I've got most of it completed. The problem I'm having is the way I want to display information.
I have two tables. Employees and Clock.
There are similar fields (eg. Employees.EmplCode = Clock.EmployeeCode), so I have been able to select both tables. I wish to display the information like:
Employee Code - Name
date - in - out - total time
date - in - out - total time
for every employee in the "Employees" table.
Can anyone give me a suggestion?

 

[Krickles]

It depends on how the tables are structured and the business rules. For tblClock, are the columns similar to:

Employee Code | Date | InTime | OutTime

where multiple in/out times are in rows, or more similar to:

Employee Code | Date | InTime1 | OutTime1 | InTime2 | OutTime2

where multiple in/out times are in columns and limited per day (e.g., only 6 columns allowing 3 in/out times). The first case will be more difficult when dealing with multiple in/out times.

In other words, are the employees allowed multiple in/out times for a single day? If so, how do you handle this information?

Regardless, I've written something simple to handle one in/out time per employee per day.

SELECT     e.emplcode,
           c.date,
           c.intime,
           c.outtime,
           datediff(mi, c.intime, c.outtime)/60.0 AS total_time
FROM       employees AS e
INNER JOIN clock AS c ON c.EmployeeCode = e.EmplCode
GROUP BY  e.emplcode

Hope this helps.

 

[bjgrem]

I'll give it a shot.
There are multiple in-out times.
In - Out Lunch - In Lunch - Out
I'll mess around with the code and see if I can get it to work. Thanks for the response.

 

[Krickles]

I thought about this a little more and you may want to display the date and in/out times more like date and time versus datetime. If so, try:

CONVERT(CHAR(12), yourdatefield, 107) AS date,
CONVERT(CHAR(5), c.intime, 114) AS in_time,
CONVERT(CHAR(5), c.outtime, 114) AS out_time

Let me know if you have trouble with the multiple in/out times. If you do, supply the tblClock design.

 

[bjgrem]

Actually, what I've got the information on hours worked stored in the database, it's just that for each 1 record in the Employees table, I may have at least 10 time records in the Clock table, and I want to display the the employee name from the Employees table 1 time, then each record with the same employee code in the clock table below that name. For instance:
BJGREM
10/10/2003 - 11:00 AM - 1:30 PM - 2:00 PM - 6:00 PM
10/11/2003 - 10:00 AM - 1:15 PM - 1:45 PM - 5:00 PM
etc.

 

[Krickles]

You can have your query return repeated names and then put the results in a nested table so that the name shows once in the primary table, then in the secondary table you will have the details. This will also require nested loops.