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

Question

Hi All,

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.

Answer 2

I would rewrite the query to not use subqueries at all:

SELECT
     SUM(DATEDIFF(n, A1.datestamp, A2.datestamp))

FROM
     INC AS I
INNER JOIN ACTM1 AS A1 ON
     A1.number = INC.incident_number
INNER JOIN ACTM1 AS A2 ON
     A2.number > A1.number AND
     A2.type IN ('Open', 'Status Change', 'Resolved', 'Closed')
LEFT OUTER JOIN ACTM1 AS A3 ON
     A3.number > A1.number AND
     A3.type IN ('Open', 'Status Change', 'Resolved', 'Closed') AND
     A3.number < A2.number
WHERE
     A3.number IS NULL

I wasn't able to fully reverse engineer your statement. I don't know if you needed the left joins or not and I didn't see where ACTA1 was actually being used, so I left it out. As a result, you may need to tweak the above. The general idea though is to find a row with a greater number, which has the type that you need, but for which there is no other row (A3) with the right type and a number that falls in between the two numbers.