SQL Query Help needed

[callnishant]

Hi,
I have a simple but tricky for me question.
I have a table that has two columns: "EmployeeID" and "Came_Late". This table keeps track if an employee came late or not. The data kind of looks like this:

EmployeeID Came_Late
125175 No
125175 Yes
125175 No
334321 Yes
334321 No
77889 No
77889 Yes
-----------
Now, I am trying to make a new table as a subset of this table which would tell me the EmployeeID where there was any value under Came_Late after a "Yes" for the same employee. For this example, my new table would look like:

EmployeeID
125175
334321

The EmployeeID 77889 didn't qualify because for this employee Came_Late was "Yes", but no value after a Yes.

Any help would be appreciated!!

Thanks in advance!

 

[PHV]

How did you sort the rows ?
You don't have a date column ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[callnishant]

This table was provided to me. There is no date or row number column in it! IS there anything we can do about this?

All Employee Data is stacked together, I didn't sort anything. In the table I have at most three entries of each employee together, then next employee...and it goes on like that.

Thanks for replying!

 

[PHV]

So, seems you have no reliable way to know which is the last Came_Late value for each EmployeeID.

 

[callnishant]

The last entry for the EmployeeID in the table is the last value for the employee. Fro example, EmployeeID 125175 has the last entry of Came_Late as "No". For 77889 it is "Yes".
But I don't know how to find this using a query!

 

[PHV]

A query returns rows randomly unless it has an ORDER BY clause and you have no column to sort...

 

[callnishant]

Can we do something like adding a new column in the original table providing some sort of ranking to the data for each employee:

EmployeeID Came_Late Rank
125175 No 1
125175 Yes 2
125175 No 3
334321 Yes 1
334321 No 2
77889 No 1
77889 Yes 2
--------
Here rank is provided based on the repitition of the EmployeeID in the table. I don't know how we can do this with a query.

 

[callnishant]

Ok, let's add a column say DateTime also to the data. Will it be possible to do it then?

I can try to get the date data.

 

[PHV]

Does your table really have only 2 columns ?
If so, I don't think you may find a reliable SQL way to retrieve the info you want.

 

[callnishant]

The table does have other columns, but its more like some junk data.