Query Logic Problem!

[alwayshouston]

Hi All
I have a table that records employee salary. First time when someone
loads the data, the Original field is marked "Y" and Modified field is
marked "N". If that particular employee record is updated, the
original field is update "N" and Modified field is updated with "Y".
Below is the table structure with records in which employee ID 100 is
modified:

EmpID Salary Original Modified
100 1000 Y N
200 2000 Y N
300 3000 Y N
100 1500 N Y


I need to write a query that would give me the most updated/modified
record for each employee as below:

EmpID Salary
100 1500
200 2000
300 3000

Can someone please help me write a query that would generate above
output? Thanks in advance!

 

[Steve Schapel]

Alwayshouston,

It is impossible to do what you ask with your present data design. You
would need to add a field, for example DateModified or some such, in
order for the query to assess the most recent record for each employee.

By the way, as an aside, it seems to me that there is no purpose served
in having both the fields Original and Modified. They in fact both
contain the same information. For example, if you removed the Original
field entirely from the table, you know that the original records are
those with a N in the Modified field.

 

[John Spencer (MVP)]

If salary always increases, then you could just get the largest salary.

SELECT EmpID, Max(Salary) as BigDollars
FROM YourTable

If not, you could probably do it with some nested sub queries. Although as
you've already been told, it would be a lot easier if you just had an effective
date in the table instead of the Original, modified.

The two step process below MIGHT work for you.

SELECT EmpID, Min(Modified = "Y") as ModYN
FROM YourTable
Group By EmpID

Save that as QueryOne

Select EmpID, Salary
FROM YourTable Inner Join QueryOne
ON YourTable.EmpID = QueryOne.EmpID
AND YourTable.Modified =
IIF(QueryOne.ModYN = 0,"N","Y")

 

[Steve Schapel]

John,

Am I correct in my understanding that the type of approach you are
suggesting will only work if an employee only has one salary
modification? If someone has more than one modification, you won't know
which one of these to use as the latest (unless you assume, as you
suggested, that the modification will always be an increase).

 

[alwayshouston]

Thanks All for your input and feedback.

I can easily add the date to the table. But it won't serve the
purpose for what I am trying to accomplish. Let me give you another
scenario. Let say, I receive one from file ERP Payroll Module and I
upload that file as ORIGINAL ='Y' and MODIFIED='N'. I get another
file from Accounting (final records) in which they are modifications
for few records and I marked this file record as MODIFIED='Y' and
ORIGINAL ='N'. In my case there is no guarantee that I will receive
the first file from ERP Payroll Module and Second file from
Accounting. There is no gurantee in sequence. I need override
whatever I get from ERD to Accounting. How do I accomplish this?

Again Thanks for your feedback!

 

[Steve Schapel]

Alwayshouston,

There will only ever be one record for each employee where Modified='N',
am I correct? (As mentioned before, the Original field is redundant, so
I will ignore it). So that's fine, having a date or some other means of
tracking sequence will not be important for the Modified='N' record, as
we can always assume it is the first record for that employee, and then
if that employee has no Modified='Y' records, the Modified='N' record
will obviously also be their last. The method suggested by John will
take care of this. The problem I mentioned only arises in the case of
an employee having more than one Modified='Y' record (which I assume
will sometimes be the case), and therefore needing to differentiate
between these modifications to determine the most recent. This is where
a ModificationDate would come in handy.

 

[John Spencer (MVP)]

Well, I must admit that I only thought of a maximum of two records for any one
empid. So, I guess I can only say "DDUUUUUHHHHH!"