Seleccionar una columna, conseguir 4

[Andres95]

Una alternativa seria utilizar subquerys con left join...con los indices correctos deberia operar relativamente bien..

aunque tambien le podemos agregar una tabla temporal para seleccionar la info que en realidad se va a procesar y no estar aplicando los filtros en cada subquery, porque como es una tabla de Log debe tener muchos registros...

Código:

SELECT  INV.INVOICE-NR ,
        DEL.[DATE] AS [DATE_DELETION] ,
        CAN.[DATE] AS [DATE_CANCELLATION] ,
        PAY.[DATE] AS [DATE_PAYMENT] ,
FROM    (
            SELECT   INVOICE-NR
            FROM     PROCESS_LOG
            WHERE    [MICONDITIONS]
            GROUP BY INVOICE-NR 
        ) INV
LEFT JOIN (
            SELECT   INVOICE-NR, [DATE]
            FROM     PROCESS_LOG
            WHERE    [MICONDITIONS] AND
                     ID_COMPANY_PROCESS = 1 
        ) DEL
ON      INV.INVOICE-NR = DEL.INVOICE-NR 
LEFT JOIN (
            SELECT   INVOICE-NR, [DATE]
            FROM     PROCESS_LOG
            WHERE    [MICONDITIONS] AND
                     ID_COMPANY_PROCESS = 2
        ) CAN
ON      INV.INVOICE-NR = CAN.INVOICE-NR 
LEFT JOIN (
            SELECT   INVOICE-NR, [DATE]
            FROM     PROCESS_LOG
            WHERE    [MICONDITIONS] AND
                     ID_COMPANY_PROCESS = 3
        ) PAY
ON      INV.INVOICE-NR = PAY.INVOICE-NR

Saludos!