SQL-Query with a multi-part identifier problem within a subquery

Question

i've a query that is supposed to return the sum for "status"-duration entries. The duration is calculated by using datediff(n, datestamp, (subquery that returns the datestamp ending the current status, i.e. finds the next fitting "status change"-entry after the one locked at)

My Problem is that the following query returns an multi-part identifier error

• The INC table is giving me the "INCIDENT_NUMBER" i'm looking for wich is related to "NUMBER" in the other tables
• ACTM1 holds all DATESTAMP-Entries
• ACTA1 is related to ACTM1 via "THENUMBER" and it holds all the information about if an entry is an fitting status change or not

Code:

SELECT SUM(DATEDIFF(n, ACTM1.DATESTAMP, END_DATESTAMP_TABLE.END_DATESTAMP))
FROM INC                LEFT OUTER JOIN
  ACTM1   ON INC.INCIDENT_NUMBER = ACTM1.NUMBER  LEFT OUTER JOIN
  ACTA1   ON ACTM1.THENUMBER  = ACTA1.THENUMBER  LEFT OUTER JOIN
/**/
    (SELECT  ACTM1_1.NUMBER, ACTM1_1.DATESTAMP AS END_DATESTAMP
  FROM ACTM1 AS ACTM1_1               LEFT OUTER JOIN
/**/
      (SELECT ACTM1_1_1.NUMBER, MIN(ACTM1_1_1.THENUMBER) AS FOLLOWUP_THENUMBER
    FROM ACTM1 AS ACTM1_1_1
    WHERE  (ACTM1_1_1.THENUMBER > /**/ ACTM1_1.THENUMBER)/*I think here lies the problem*/
      AND (ACTM1_1_1.[TYPE]  IN ('Open', 'Status Change', 'Resolved', 'Closed')))
    AS FOLLOWUP_THENUMBER_TABLE
/**/
            ON ACTM1_1.NUMBER = FOLLOWUP_THENUMBER_TABLE.NUMBER)
  AS END_DATESTAMP_TABLE
/**/
            ON ACTM1.NUMBER = END_DATESTAMP_TABLE.NUMBER
WHERE ...

I would be grateful for any helpful comment or hint you could give me on this,

PS

Answer 1

The left side join relation cannot reference the right side, so this is illegal:

SELECT ...
FROM A
JOIN (SELECT ...FROM ... WHERE ... = A.Field) AS B ON A.ID = B.ID;

Use the APPLY operator instead:

SELECT ...
FROM A
APPLY (SELECT ...FROM ... WHERE ... = A.Field AND ID = A.ID) AS B;

In your case would probably be like following:

SELECT SUM(DATEDIFF(n, ACTM1.DATESTAMP, END_DATESTAMP_TABLE.END_DATESTAMP))
FROM INC                
LEFT OUTER JOIN ACTM1 ON INC.INCIDENT_NUMBER = ACTM1.NUMBER  
LEFT OUTER JOIN ACTA1 ON ACTM1.THENUMBER  = ACTA1.THENUMBER  
LEFT OUTER JOIN (
   SELECT  ACTM1_1.NUMBER, ACTM1_1.DATESTAMP AS END_DATESTAMP
   FROM ACTM1 AS ACTM1_1
   OUTER APPLY (
      SELECT ACTM1_1_1.NUMBER, /* MIN(ACTM1_1_1.THENUMBER) */ AS FOLLOWUP_THENUMBER
        FROM ACTM1 AS ACTM1_1_1
        WHERE  (ACTM1_1_1.THENUMBER > ACTM1_1.THENUMBER)
        AND (ACTM1_1.NUMBER = FOLLOWUP_THENUMBER_TABLE.NUMBER)
        AND (ACTM1_1_1.[TYPE]  IN ('Open', 'Status Change', 'Resolved', 'Closed'))
    ) AS FOLLOWUP_THENUMBER_TABLE
) AS END_DATESTAMP_TABLE ON ACTM1.NUMBER = END_DATESTAMP_TABLE.NUMBER

Obviously the MIN inside the inner query makes no sense though.