Reply to :Why use the cross join?
Yes it could be done in a way you have shown but then the whole personid should be marked as improper if any DEDATE is found mismatched
Divya,
Your given query is essentially marking the personID as improper. In that case a query like:
SELECT PersonID, 1 as improper
FROM #PersonRecord A
INNER JOIN #PersonRecord B
ON A.PersonID = B.PersonID
AND A.Version = B.Version-1
WHERE A.DEDate >= B.DEDate
GROUP BY PersonID
Seems much more simple than the original code.
In Addition, the CROSS JOIN is not necessary (and probably removed by the optimizer) in the original code. The following is the same logic with the Cross join replaced with an INNER JOIN:
SELECT *,
(SELECT CASE
WHEN (SUM(CASE WHEN B.DEDate =1
THEN 1 ELSE 0
END AS isawk
from #PersonRecord B INNER JOIN #PersonRecord C
ON B.PersonID = C.PersonID
AND B.Version < C.Version
WHERE B.PersonID=A.PersonID) AS [Is ImProper]
from #PersonRecord A
If I were aiming for this result, I would probably use something like:
SELECT A.PersonID, Version, DEDate, COALESCE(Improper,0) as Improper
FROM #PersonRecord A
LEFT JOIN (SELECT DISTINCT B.PersonID, 1 as improper
FROM #PersonRecord B
INNER JOIN #PersonRecord C
ON B.PersonID = C.PersonID
AND B.Version = C.Version-1
WHERE B.DEDate >= C.DEDate) as t
ON A.PersonID = t.PersonID
I prefer to put the subquery in the FROM Clause instead of in the SELECT Clause, if possible. The benefit of this is seen when you want to move that subquery into a VIEW so that it can be used as a source in multiple queries.
--
JimFive