SELECT ... FROM (SELECT ... - what's wrong? 1

[mp9]

Am using a UNION query to collate two disparate queries, and then want to use that query nested within another. This is what I have:

SELECT DISTINCT cid, logdt, MIN(closedt) FROM
(
SELECT cid, logdt, closedt 
FROM Call C 
WHERE datediff(ss, C.logdt, C.closedt) < 21600
UNION
SELECT c.cid, c.logdt, MAX(h.adddt) AS resolvedt
FROM Call C INNER JOIN CallHist h ON c.cid=h.cid
WHERE  datediff(ss, C.logdt, h.adddt) < 21600
AND h.txt='Preclose'
GROUP BY c.cid, c.logdt
)

but SQL Server spits out an error about an error near the closing bracket on the last line. The UNION query works fine on its own, by the way.

What's wrong?

Thanks.

 

[PHV]

SELECT cid, logdt, MIN(closedt) FROM
(
SELECT cid, logdt, closedt
FROM Call
WHERE datediff(ss, logdt, closedt) < 21600
UNION
SELECT c.cid, c.logdt, MAX(h.adddt)
FROM Call C INNER JOIN CallHist h ON c.cid=h.cid
WHERE datediff(ss, C.logdt, h.adddt) < 21600
AND h.txt='Preclose'
GROUP BY c.cid, c.logdt
) AS U
GROUP BY cid, logdt

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[mp9]

Cheers, you can tell it's been a while since I've had to do this...