Multiple Effective Dates (latest date) 1

[netrusher]

Below is the code I have for a query. This query shows Processes, Operations, etc. Due to multiple Effective_ (Effective_ stands for Date) The Operations show multiple times. What I would like to do is have each Operation show only one time based on the latest Effective_ i.e. If Operation START-UP is listed three times due to three effective dates 2007-01-04, 2007-01-08 & 2007-01-17 I would only like to the the one associated with the 2007-01-17. Is there a way to accomplish this in my query?

SELECT ASSYROP.PROCESS, ASSYROP.OPERATION, ASSYROP.OPERATION_, ASSYWC1.PROCESS_AT, ASSYROP.OPERATIO_1, ASSYROP.EFFECTIVE_
FROM ASSYROP INNER JOIN ASSYWC1 ON ASSYROP.OPERATION = ASSYWC1.OPERATION
GROUP BY ASSYROP.PROCESS, ASSYROP.OPERATION, ASSYROP.OPERATION_, ASSYWC1.PROCESS_AT, ASSYROP.OPERATIO_1, ASSYROP.EFFECTIVE_
ORDER BY ASSYROP.PROCESS, ASSYROP.OPERATION, ASSYROP.EFFECTIVE_;

 

[Golom]

SELECT R.PROCESS, R.OPERATION, R.OPERATION_, W.PROCESS_AT, R.OPERATIO_1, R.EFFECTIVE_

FROM ASSYROP R INNER JOIN ASSYWC1 W ON R.OPERATION = W.OPERATION

WHERE R.EFFECTIVE_ = (SELECT MAX(R.EFFECTIVE_) FROM ASSYROP T
                      WHERE T.PROCESS    = R.PROCESS
                        AND T.OPERATION  = R.OPERATION
                        AND T.OPERATION_ = R.OPERATION_
                        AND T.OPERATIO_1 = R.OPERATIO_1)

ORDER BY R.PROCESS, R.OPERATION, R.EFFECTIVE_;

Assuming that all those fields have some key involvement. If they don't then remove the non-key fields from the sub-query.

 

[netrusher]

Thanks,

I am still getting all the effective dates showing up. It is not filtering on the lastest date.

Also, What does the R. W. stand for?

 

[Golom]

It's probably because all the fields that I put in the sub-query are not necessary. Which of the fields Process, Operation, Operation_ and Operatio_1 does the date field (Effective_) apply to?

To put it another way, do you want the MAX(Effective_) for a Process? For an Operation? For the combination of a Process and an Operation?

If, for example, it's just the first two (i.e. Process and Operation) then remove the other two from the sub-query.

I just assigned the aliases "R" and "W" to the tables in your FROM clause to make thinks a bit easier to read.

 

[netrusher]

Thanks again,

I guess I am confused. I have tried about every combination of the above and I am still getting all of the effective dates. The only link between the two tables is Process. I am not sure what you mean by:
Sorry I am being so dense on this!

To put it another way, do you want the MAX(Effective_) for a Process? For an Operation? For the combination of a Process and an Operation?

If, for example, it's just the first two (i.e. Process and Operation) then remove the other two from the sub-query.

 

[netrusher]

I want the the row of data that has the Max effective date on the same row! Again, I am not doing well explaining or understanding this.

 

[lespaul]

Golom

shouldn't the R be T?

SELECT R.PROCESS, R.OPERATION, R.OPERATION_, W.PROCESS_AT, R.OPERATIO_1, R.EFFECTIVE_

FROM ASSYROP R INNER JOIN ASSYWC1 W ON R.OPERATION = W.OPERATION

WHERE R.EFFECTIVE_ = (SELECT MAX([b]R[/b].EFFECTIVE_) FROM ASSYROP T
                      WHERE T.PROCESS    = R.PROCESS
                        AND T.OPERATION  = R.OPERATION
                        AND T.OPERATION_ = R.OPERATION_
                        AND T.OPERATIO_1 = R.OPERATIO_1)

ORDER BY R.PROCESS, R.OPERATION, R.EFFECTIVE_;

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[PHV]

And what about this ?
SELECT R.PROCESS, R.OPERATION, R.OPERATION_, W.PROCESS_AT, R.OPERATIO_1, R.EFFECTIVE_
FROM (ASSYROP R
INNER JOIN ASSYWC1 W ON R.OPERATION = W.OPERATION)
INNER JOIN (
SELECT A.PROCESS, A.OPERATION, Max(A.EFFECTIVE_) AS LastDate
FROM ASSYROP A GROUP BY A.PROCESS, A.OPERATION
) L ON R.PROCESS = L.PROCESS AND R.OPERATION = L.OPERATION AND R.EFFECTIVE_ = L.LastDate

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

 

[netrusher]

Mis Leslie,

I am still getting all of the dates.

 

[netrusher]

PH

Thanks, your example is getting the last date. I still do not understand Alias. I will try and learn it as soon as I can.

Again, Thanks for your help!