Sql Problem

[rHasan]

I've a table called Attendance which has three fields - CardNo, Date, Time. This table has three record for each card no for one day, like -

003795, 20070216, 080203
003795, 20070216, 020315
003795, 20070216, 170203

How can I show records in a grid like - CardNo, Date, Time, WorkHous? What will be the SQL?

Thnx in advance for your kind help!

 

[Geert Verhoeven]

Hi,

What is the exact meaning of the Time column?
Does it contain from to values?

What result do you need?
170203 - 080203? What about lunch time?

Greetz,

Geert

 

[rHasan]

Hi Geertz,

These records come from our barcode reader. This are card punching records. Workers punch their cards 3 times daily - morning, after launch, evening. I need results like -

Id - Date - Entered - Leaved - Work Hour

Thnx!

 

[Geert Verhoeven]

Hi,

Here is an example:
/*** DB SETUP
if object_id('DummyTable') is not null drop table DummyTable
create table DummyTable
(
CardNo varchar(7),
Date varchar(8),
Time varchar(6)
)
insert into DummyTable values ('003795', '20070216', '080203')
insert into DummyTable values ('003795', '20070216', '140315')
insert into DummyTable values ('003795', '20070216', '170203')
****/
select
CardNo,
Date,
min(convert(datetime, date + ' ' + left(time, 2) + ':' + substring(time, 3, 2) + ':' + right(time, 2), 112)) START_TIME,
max(convert(datetime, date + ' ' + left(time, 2) + ':' + substring(time, 3, 2) + ':' + right(time, 2), 112)) END_TIME,
convert(varchar(8), max(convert(datetime, date + ' ' + left(time, 2) + ':' + substring(time, 3, 2) + ':' + right(time, 2), 112)) -
min(convert(datetime, date + ' ' + left(time, 2) + ':' + substring(time, 3, 2) + ':' + right(time, 2), 112)), 108) WORK_HOURS
from DummyTable
group by CardNo, Date

NOTE: I did change the 020315 to 140315. Don't know if it is logic. With this procedure you get the time between the min and max (start and stop). This is the total time. If the lunch time is fixed, you can substract it from the result.

Greetz,

Geert

 

[rHasan]

Hey, I am using access database. Will this work for access or only sql server? thnx!