Stored Procedure Help

Question

Post reply

Stored Procedure Help

Steven Aiello

SSChasing Mays

Points: 604
More actions
April 13, 2007 at 12:24 pm

#195108

I have a table @CJPercentages that's populated like this in my stored procedure:
ID LowerLimit UpperLimit Percentage Rate
----------------------------------------------------------------------------
1 0 999999999 200 0
2 0 50 350 10
2 50 100 400 5
2 100 999999999 500 0
And another table @TempPlaySUM:
PlayDate ActualHold CJShareID
---------------------------------------------------------
04-12-2007 400 1
04-12-2007 -100 2
04-12-2007 85 2
In my stored procedure I am trying to join these tables and pretty much have an end result like this:
PlayDate ActualHold Percentage FixedRate
------------------------------------------------------------------------
04-12-2007 400 200 0
04-12-2007 -100 200 0
04-12-2007 85 400 5
But, the row with the negative ActualHold isn't showing up in my resultset. Now I'm stumped at how to join the tables to carry over the negative values of the ActualHold. The ActualHold has to fall between the LowerLimit and UpperLimit for the CJShareID. If the ActualHold is negative I want to default the Percentage to 200 and the FixedRate to 0. Here is what my Join looks like:
INSERT INTO @TempPlay (PlayDate, PaytableName, LegalConfigNumber, SkinName, Denomination, EPSName, TotalBet, TotalWin, Configuration, ActualHold, TheoreticalHold, ZoneName, CJPercentage, FixedRate)
SELECT p.PlayDate, p.PaytableName, p.LegalConfigNumber, p.SkinName, p.Denomination, p.EPSName, p.TotalBet, p.TotalWin, p.Configuration, p.ActualHold, p.TheoreticalHold, p.ZoneName, ISNULL(b.CJPercent, 200), ISNULL(b.FixedRate, 0)
FROM @TempPlaySUM p LEFT OUTER JOIN @CJPercentages b ON p.CJShareID = b.CJShareID
WHERE (p.ActualHold > b.LowerLimit) AND (p.ActualHold < b.UpperLimit)
GROUP BY p.PlayDate, p.EPSName, p.PaytableName, p.LegalConfigNumber, p.Configuration, p.SkinName, p.Denomination, p.TheoreticalHold, p.ZoneName, p.TotalBet, p.TotalWin, p.ActualHold, b.CJPercent, b.FixedRate
Where am I going wrong????

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Carl Federl One Orange Chip Points: 25384 More actions · Answer 1

The section "WHERE (p.ActualHold > b.LowerLimit) AND (p.ActualHold b.LowerLimit) AND (p.ActualHold < b.UpperLimit)

SQL = Scarcely Qualifies as a Language

Steven Aiello SSChasing Mays Points: 604 More actions · Answer 2

Thanks for your help Carl. I've been looking at this query and modifying it numerous times over the past 3 days and got stuck on that....ahhhhh!....Thanks again!