April 2, 2008 at 4:29 am
Hi,
I have three tables with some example data
CREATE Table P_value
(
P_valueid int identity(1,1),
PID int,
pValue float
)
go
create Table PB_Rule
(
PB_Ruleid int identity(1,1),
pID int,
bID int
)
go
create Table riders
(
riderIDint identity(1,1),
PID int,
RiderType int,
RiderRatio int
)
go
INSERT P_Value (PID,pValue)
values (1,5)
INSERT P_Value (PID,pValue)
values (2,10)
INSERT P_Value (PID,pValue)
values (3,1.2)
INSERT P_Value (PID,pValue)
values (4,3)
INSERT P_Value (PID,pValue)
values (200,1.3)
INSERT P_Value (PID,pValue)
values (500,2)
INSERT P_Value (PID,pValue)
values (400,2.1)
INSERT P_Value (PID,pValue)
values (5,4)
INSERT PB_Rule (pID,bID)
Values (1,200)
INSERT PB_Rule (pID,bID)
Values (1,300)
INSERT PB_Rule (pID,bID)
Values (2,400)
INSERT PB_Rule (pID,bID)
Values (3,500)
INSERT PB_Rule (pID,bID)
Values (4,600)
INSERT riders (PID,RiderType,RiderRatio)
Values (1,1,1)
INSERT riders (PID,RiderType,RiderRatio)
Values (2,1,5)
INSERT riders (PID,RiderType,RiderRatio)
Values (3,2,1)
INSERT riders (PID,RiderType,RiderRatio)
Values (4,2,-1)
I have a function which calculates an indicator depending on pValue. For example, for PID is 1 and P_Value is between 0 to 1, return 0, if P_value is between 2 to 5, return 1 etc.
My present problem is to implement another rider before passing pValue to the function I have written. The rider may increase or decrease the pValue based on PB_Rule and Riders table. Note that BID is a subset of PID.
This is to be done some thing like this:
For a particular @PID
If @PID is not in Riders table, return newPValue = PValue
If @PID is in Riders table then
if RiderType is 1,
PBSum = SELECT SUM(PValue) FROM P_Value INNER JOIN PB_RULE ON PB_RULE.BID=P_Value.PID WHERE PB_Rule.PID = @PID
newPValue = PValue - PBSUM*Riders.Ratio
if RiderType is 2
PBSum = SELECT Count(PID) FROM P_Value INNER JOIN
PB_Rule ON PB_RULE.BID=P_Value.PID WHERE PB_Rule.PID = @PID
IF PBSum >0
newPValue = PValue + Rider.Ratio
and so on for other RiderType
I need to pass the newPValue obtained to the existing function
To clarify further for the example data,
For PID = 1, the actual PValue is 5 and sum (BIDs) = 1.3 (Value of PID=200 & PID=300 is NULL), since Rider.Ratio is 1, newPValue = 5-1.3*1 = 3.7
The result should look like something like this:
PID PValue SUM(BID) or Count(BID) FinalPValue
1 5 1.3 3.7 (5-1.3*1)
2 10 2.1 -0.5 (10-2.1*5)
3 1.2 1(count) 2.2 (1.2+1)
4 3 1 (Count) 2 (3 - 1)
5 4 (no riders) 4
I am perplexed how to implement this. Any help would be highly appreciated.
Regards,
Dhandapani
April 2, 2008 at 11:10 am
It looks to me like you're most of the way to where you want to go.
Turn your logic on the Riders into a function, with @PID as an input, and call that function before you call the other function, or call it in the other other function before you do further processing.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 2, 2008 at 11:16 am
Thanks, figured out myself, very close to what you have suggested.
Regards,
Dhandapani
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply