Messy Where clause - Re-Posted full Query

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 2005 Forums

Transact-SQL (2005)

Messy Where clause - Re-Posted full Query

Author

Topic

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-02-12 : 16:05:38

I have a where clause that is beginning to get very messy. I need to get the records from a table (TSV_BenefitClosed) based off the dates in my TSV_BenefitInterest table. I have made it work using a ton of top 1 queries to factor in each scenerio and get the proper record associated, but is there a better way to do this. It seems as if I am hitting the same table numberous times, that I should be able to use a cross join and filter the same way but with improved performance. The issue is if I use a join, how would I be able to still filter appropriatly only returning the "Top 1" in order to make sure I am returning the correct value.

Thanks,


Where
	( --No Closed Dates
		(not exists (Select top 1 c.ClosedDate from TSV_BenefitClosed c where not c.closeddate is null))
	Or -- Closed Dates exist but current period is not closed
		(not exists (Select top 1 c.ClosedDate from TSV_BenefitClosed c where c.closeddate > bi.Startdate order by c.closeddate desc)
		 and
		(bi.startdate >= (Select top 1 c.ClosedDate from TSV_BenefitClosed c where c.closeddate <= bi.Startdate order by c.closeddate desc)))
	or --Closed Dates exist but everything is closed out
		(bi.startdate < (Select top 1 c.ClosedDate from TSV_BenefitClosed c where c.closeddate > bi.Startdate order by c.closeddate desc))
		and
        (bi.startdate >=(Select top 1 c.ClosedDate from TSV_BenefitClosed c where  c.closeddate < bi.Startdate order by c.closeddate desc))
	)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-12 : 21:02:40

why not use LEFT OUTER JOIN ?

Please post your full query

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-02-13 : 14:57:29

Here is the full query. I don't see how a left outer join will work
due to I would still need to somehow be able to implement the top 1
statments. The query is set to genereate interest on records applied
from the next period (for example interest for 2007 gets applied to
2006). This query then factors in if the account was closed, and if
it was it generates the proper amount of interest to the record. To
make it simpler to understand take off the columns in my select list
and know that I am just trying to filter the items based off my where
clause.

[Code]
Select bi.StartDate,bi.BenefitInterestID,0,0,
cast(Coalesce(datediff(m,bi.enddate,bc.closedDate), --Get ClosedDate
datediff(m, bi2.StartDate, bi2.EndDate) + 1) as Decimal(14,4)) /
Cast(datediff(m, bi2.StartDate, bi2.EndDate) + 1 as decimal(14,4)),0
From TSV_BenefitInterest bi left Join @TmpTblInterest ti
on ti.DateReceived between bI.StartDate and bI.EndDate
left join TSV_BenefitInterest bi2
on bi.BenefitTypeID = Bi2.BenefitTypeID
left Join TSV_BenefitClosed bc on
bc.closeddate between bi2.startdate and bi2.Enddate
and bi2.BenefitTypeID = bc.BenefitTypeID
where ti.BenefitTypeID is null
and bc.EmployeeID = @EmployeeID
and bi2.BenefitInterestID = (Select top 1 c.BenefitInterestID from TSV_BenefitInterest c where c.StartDate > bi.Startdate and c.BenefitTypeID = @BenefitTypeID order by c.Startdate asc)
and bi.Enddate <= getdate()
and
( --No Closed Dates
(not exists (Select top 1 c.ClosedDate from TSV_BenefitClosed c where c.EmployeeID = @EmployeeID and c.BenefitTypeID = @BenefitTypeiD and not c.closeddate is null))
Or -- Closed Dates exist but current period is not closed
(not exists (Select top 1 c.ClosedDate from TSV_BenefitClosed c where c.EmployeeID = @EmployeeID and c.BenefitTypeID = @BenefitTypeiD and c.closeddate > bi.Startdate order by c.closeddate desc)
and
(bi.startdate >= (Select top 1 c.ClosedDate from TSV_BenefitClosed c where c.EmployeeID = @EmployeeID and c.BenefitTypeID = @BenefitTypeiD and c.closeddate <= bi.Startdate order by c.closeddate desc)))
or --Closed Dates exist but everything is closed out
(bi.startdate < (Select top 1 c.ClosedDate from TSV_BenefitClosed c where c.EmployeeID = @EmployeeID and c.BenefitTypeID = @BenefitTypeiD and c.closeddate > bi.Startdate order by c.closeddate desc))
and
(bi.startdate >=(Select top 1 c.ClosedDate from TSV_BenefitClosed c where c.EmployeeID = @EmployeeID and c.BenefitTypeID = @BenefitTypeiD and c.closeddate < bi.Startdate order by c.closeddate desc))
) [/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources