Pickup Rate based on effective date

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

General SQL Server Forums

New to SQL Server Programming

Pickup Rate based on effective date

Author

Topic

ingineu
Yak Posting Veteran

89 Posts

Posted - 2005-10-16 : 15:29:54

I have a procedure that requires picking up the Rate based on Effective Date. This is what I have so far:


SELECT 
      SHPD.ProductID,
      SHPD.ReceivedDate,
      SHPD.Shipper, 
      SHIP.UnitRate 
FROM tblShipmentDet SHPD 
LEFT OUTER JOIN tblShippers ON SHIP.ProductID = SHPD.ProductID AND SHIP.Shipper = SHPD.Shipper AND Max???(SHIP.Effectivedate) <= SHPD.ReceivedDate

Because there can be more than 1 Shipper record, I would somehow need to pickup the Maximum EffectiveDate in each case. I realize I cannot use the Max aggregate in the JOIN. Not sure where to go from here. On the Mainframe I used a LOOKUP function that would return the correct EffectiveDate. Help would be appreciated.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-17 : 00:33:10

You can make use of Group and Having Clause

Can you post some sample data and the result you want?

Madhivanan

Failing to plan is Planning to fail

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-10-17 : 01:46:07

You require somthing like this .. ???
SELECT
SHPD.ProductID,
SHPD.ReceivedDate,
SHPD.Shipper,
SHIP.UnitRate
FROM tblShipmentDet SHPD
LEFT OUTER JOIN tblShippers ON SHIP.ProductID = SHPD.ProductID AND SHIP.Shipper = SHPD.Shipper
Group by SHPD.ProductID,SHPD.ReceivedDate,SHPD.Shipper, SHIP.UnitRate
Having Max(SHIP.Effectivedate) <= SHPD.ReceivedDate

Complicated things can be done by simple thinking

ingineu
Yak Posting Veteran

89 Posts

Posted - 2005-10-17 : 12:48:07

I cannot group the SHPD data. This is what I'm looking for:

tblShipmentDet:
11111 8/1/2000 KNU01 $15.00 <-- pickup Rate
11111 9/5/2005 KNU01 $20.00
22222 7/8/2004 CRA05 $ 5.00

tblShippers:
11111 KNU01 7/1/2000 $15.00
11111 KNU01 7/1/2005 $20.00
11111 GAB01 6/8/2005 $23.00
22222 CRA05 1/1/2004 $5.00

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-10-18 : 02:13:30

ok.. this is the data in both the table.. but can u post what is the desired output that you would require.. ???

Complicated things can be done by simple thinking

ingineu
Yak Posting Veteran

89 Posts

Posted - 2005-10-18 : 14:33:59

tblShipmentDet is the Input and the Ouput. I am updating the tblShipmentDet records with the RATE from the tblShippers file.

ingineu
Yak Posting Veteran

89 Posts

Posted - 2005-10-24 : 20:34:13

Is there no solution available?

ingineu
Yak Posting Veteran

89 Posts

Posted - 2005-10-24 : 22:10:51

Found a workaround. In my join I create an ending date using another Select which then gives me a from -- to range for each Supplier record.