Left Join acting like Join
-
I am attempting to build a query that will tell me how many policies were created in a previous year that were renewed the following year.
The Policies table has the policy number for the current policy (PolicyID) and the policy number for the policy from which this one was renewed (OldPolicyID).
I figured I could join the table upon itself. The previous year policy group would be (new) and the following year policy group would be (ren). I want to pull all records from the (new) table and only the matching records for the (ren) table.
select count(new.PolicyID) as New, count(ren.PolicyID) as Ren
from Policies new left join Policies ren on new.PolicyID=ren.OldPolicyID
where new.Effective between dateadd(yy,-3,getdate()) and dateadd(yy,-2,getdate())
and ren.Effective between dateadd(yy,-2,getdate()) and dateadd(yy,-1,getdate())
The effective date for the (new) table is the year prior to that of the (ren) table.
The results are New(24221), Ren(24221). It's the same result I get when using a join operator.
If I run the following query which only focuses on the previous year policy group:
select count(new.PolicyID) as New
from Policies new
where new.Effective between dateadd(yy,-3,getdate()) and dateadd(yy,-2,getdate())
I get the following result: New(50321).
My final output for the initial query should produce: New(50321), Ren(24221).
Any help would be greatly appreciated.
Keith Wiggans
-
The second portion of your where clause is effectively turning the join into an inner join
select count(new.PolicyID) as New, count(ren.PolicyID) as Renfrom Policies new left join Policies ren on new.PolicyID=ren.OldPolicyID
where new.Effective between dateadd(yy,-3,getdate()) and dateadd(yy,-2,getdate())
and ren.Effective between dateadd(yy,-2,getdate()) and dateadd(yy,-1,getdate())
Left join means that unmatched rows in the left table will be returned and the resulting values for the join table will be null. Your between is eliminating nulls. If you want a left join tio behave like a left join, rewrite the query in one of the following ways. I don't know which is right, because I don't know exactly what you want returning.
Option 1: This allows unmatched rows to be returned because of the additional check for IS NULL.
SELECT count(new.PolicyID) as New, count(ren.PolicyID) as RenFROM Policies new LEFT OUTER JOIN Policies ren on new.PolicyID=ren.OldPolicyID
WHERE new.Effective between dateadd(yy,-3,getdate()) AND dateadd(yy,-2,getdate())
AND ren.Effective BETWEEN dateadd(yy,-2,getdate()) AND dateadd(yy,-1,getdate()) OR ren.Effective IS NULL
Option 2: This applies the filter on ren before the join happens
SELECT count(new.PolicyID) as New, count(ren.PolicyID) as RenFROM Policies new
LEFT OUTER JOIN Policies ren on new.PolicyID=ren.OldPolicyID AND ren.Effective BETWEEN dateadd(yy,-2,getdate()) AND dateadd(yy,-1,getdate())
WHERE new.Effective BETWEEN dateadd(yy,-3,getdate()) AND dateadd(yy,-2,getdate())
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Brilliant Gila! 😎
I thought perhaps the where clause may have been messing me up, but I was too close to the problem to see the solution.
Thanks for the insight.
Keith Wiggans
-
If neither query does exactly what you want, post the table structure, some sample data and your expected result.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply