SubQuery issue - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

SubQuery issue

Author

Topic

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2006-07-21 : 16:04:49

I can not get this query to work.

Basically what I want to do is; I have a table which has rates that have effective dates in it (ie 01/01/2001 2.000,01/01/2002,3.00, etc). Each rate has a Benefit type corresponding with it that relates to a contract in a seperate table with start dates and end dates.

What I want to do get the most recent plan rate that is less then todays date. Then tell me what contract this falls into; the only corralation between the two is the plan rate effective date is >= the max contract startdate <= today. Below is the query I am using, it works fine unless I have 2 rates that one is in one contract and one in in another, and both effective dates are <= today.

SELECT MAX(a.EffectiveDate) AS effectivedate, a.EmployerID, a.PlanID,c.PlanBenWWaitID,c.BillingCycleID
FROM TMI_EmployeePlanRate a INNER JOIN
TMI_Plan b ON a.PlanID = b.PlanID INNER JOIN
TMI_EmployerPlanBenWWait c ON b.BenefitTypeID = c.BenefitTypeID and c.ContractID = (select distinct cont1.ContractID from
TMI_EmployerContract Cont1
Where cont1.EmployerID = a.EmployerID and
Cont1.ContractStartDate = (Select Max(Cont.ContractStartDate)
from TMI_EmployerContract cont
Where cont.EmployerID = a.EmployerID
and cont.ContractStartDate <= getdate()
group by cont.EmployerID,cont.ContractID)
Where a.EffectiveDate <= getdate()
GROUP BY a.EmployerID, a.PlanID,c.PlanBenWWaitID,c.BillingCycleID

Please help, I know this post is hard to follow, but it's been causing me a lot of headaches.

Thanks

airjrdn
Starting Member

35 Posts

Posted - 2006-07-25 : 09:27:59

A couple of thoughts/questions...

1 - Is the distinct a temporary fix to get around the issue you're having?
2 - Are you missing a closing paren after "cont.EmployerID,cont.ContractID" ?
3 - If you run many queries like this, you may be money ahead to first populate a temporary table with the latest data (most recent rates by contract type, etc.) then join to that. It would negate one of your subselects and improve performance and maintainability.
4 - Is there the possiblity of two having the same effectivedate? If so, you still stand the chance of returning more than one record at some point.
5 - Assuming my paren question is correct, can the nested subselect's group by be removed like so:


SELECT	MAX(a.EffectiveDate) AS effectivedate,
		a.EmployerID,
		a.PlanID,
		c.PlanBenWWaitID,
		c.BillingCycleID
FROM	TMI_EmployeePlanRate a
		INNER JOIN TMI_Plan b ON a.PlanID = b.PlanID
		INNER JOIN TMI_EmployerPlanBenWWait c ON b.BenefitTypeID = c.BenefitTypeID
											 and c.ContractID = (select	cont1.ContractID
																from	TMI_EmployerContract Cont1
																Where	cont1.EmployerID = a.EmployerID
																and		Cont1.ContractStartDate = (	Select	Max(Cont.ContractStartDate)
																									from	TMI_EmployerContract cont 
																									Where	cont.EmployerID = a.EmployerID
																									and		cont.ContractStartDate <= getdate()
																								  )
																)
Where	a.EffectiveDate <= getdate()
GROUP BY
		a.EmployerID,
		a.PlanID,
		c.PlanBenWWaitID,
		c.BillingCycleID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources