July 6, 2016 at 9:00 am
OK, here's your issue. Another poster changed to a LEFT JOIN to ensure you get all the projects, and you ran with that also. That's fine, but for projects that have no match in the NewProjects table, you're going to get NULLs in the NewProject columns. Therefore your DATEDIFF expression will look like this: ABS(DATEDIFF(month, p.Finish, NULL)) , which will always evaluate to NULL, which will always return False when the comparison <=2 is performed on it. That means that you'll never get any rows returned that don't have a match in NewProject, effectively turning your LEFT JOIN into an INNER JOIN. That's slightly over-simplified in this case, because of the conditional logic in your WHERE clause - I think it would be more accurate to say you'll never get any rows returned where there's no match and ProjectType is not "New". Anyway, the way round it is to take your WHERE clause and turn it into a join predicate, something like this:
WITH NewProjects
AS (
SELECT Location
,Finish
FROM Projects
WHERE ProjectType = 'New'
)
SELECT p.ProjectNo
,CASE p.ProjectType
WHEN 'New'
THEN p.ProjectType
ELSE ''
END AS ProjectType
,p.Location
,CASE
WHEN (p.ProjectType <> 'New')
AND (ABS(DATEDIFF(month, p.Finish, np.Finish)) <= 2)
THEN 'newChild'
WHEN p.ProjectType = 'New'
THEN 'new'
ELSE NULL
END AS ChildDef
,p.Finish
FROM Projects AS p
LEFT JOIN NewProjects AS np
ON np.Location = p.Location
AND (p.ProjectType <> 'New' AND ABS(DATEDIFF(month, p.Finish, np.Finish)) <= 2)
ORp.ProjectType = 'New')
But please, don't take my word for that - test it until your eyes water before it goes anywhere near production!
John
July 6, 2016 at 9:43 am
Appreciate the explanation and help. That produces duplicate rows though.
ProjectNoProjectTypeLocationChildDefFinish
abc123new 111 new 2015-12-01
abc123new 111 new 2015-12-01
abc123new 111 new 2015-12-01
abc123new 111 new 2015-12-01
abc123new 111 new 2015-12-01
abc127 111 newChild2015-11-01
abc126 111 NULL 2016-12-01
abc134 1332 NULL 2015-12-01
<snip>
July 6, 2016 at 9:47 am
Yes, but that's not because of the INNERness or the OUTERness of the join - it's because you're joining on Location. Since many projects can share the same location (I assume), your left table is going match up with everything in the right table that has the same location.
John
July 6, 2016 at 9:52 am
OK, but that's no good :ermm:
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply