Look for missing Data

[Lethugs]

Hi,

I have a data, employee attendance record with following format

01-0002,I,4/21/2014,07:34:00
01-0002,O,4/21/2014,18:09:00
01-0002,I,4/22/2014,07:47:00
01-0002,O,4/22/2014,18:09:00
01-0002,I,4/23/2014,07:54:00
01-0002,O,4/23/2014,18:07:00

where I is in and O is out

The table contains lots of records for this saves records from the start

how can i search a record with no In or no Out from the date range specified?

I need to create a report for those employees with no In but with Out, or with In but no Out.
Another report needs to generate percentage of lates (individually or all)

For now this is the query I used but no luck

 query = "Select LID, EID, Type, Dates, Time From Tlogs where EID = @e and not exists (Select Types, Dates From Tlogs t1 where t1.Type = 'I' and t1.Dates = Tlogs.Dates)"

where EID is Emp ID (01-0002), Type is In or Out (I,O), Dates (4/23/2014), Time (18:07:00)

Thanks in advance

[pritaeas]

how can i search a record with no In or no Out from the date range specified?

If you group by employee number and date, the COUNT(*) should be even. The ones with an uneven count miss an in or an out. Unfortunately, this does not account for say two IN records (if even possible).

[Lethugs]

How about if there IN's and OUT's have duplicates? Like what you say two or more IN;s or OUT's for the same date. How can I get the right result?

thanks

[pritaeas]

Ummm...

You could group by number, date and in/out and get an intermediaite result containing the grouped count for in and out in one record (for a number and date). You can then select the records that have the counts for in and out that are not identical.

[Lethugs]

I will try to construct a query for that, but if you can give me ideas through code, that could be a great help for me

thanks again

[pritaeas]

Something like this:

SELECT `LId`, `Date`, SUM(IF(`Type` = 'I', 1, 0)) InCount, SUM(IF(`Type` = 'O', 1, 0)) OutCount
FROM `TLogs`
GROUP BY `LId`, `Date`
WHERE InCount <> OutCount

Had to guess the columns, but you get the picture I think.

[Lethugs]

Hi pritaeas,

Sorry for late follow up, my code works now
thank you for sharing some knowledge

and because of that, for somebody who will encounter same problem as mine, here's what i used

"Select EID, Type, Dates, Time From Tlogs where Dates Between @f and @t " & usid & " and not exists (Select 1 From Tlogs Incount where Incount.EID = Tlogs.EID and Incount.Dates = Tlogs.Dates and Incount.Type = 'I') " & _
"or not exists (Select 1 From Tlogs Incount where Incount.EID = Tlogs.EID and Incount.Dates = Tlogs.Dates and Incount.Type = 'O') "

thanks again for helping sir