SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Pickup Rate based on effective date
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ingineu
Yak Posting Veteran

89 Posts

Posted - 10/16/2005 :  15:29:54  Show Profile  Reply with Quote
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

India
22754 Posts

Posted - 10/17/2005 :  00:33:10  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

chiragkhabaria
Flowing Fount of Yak Knowledge

India
1907 Posts

Posted - 10/17/2005 :  01:46:07  Show Profile  Visit chiragkhabaria's Homepage  Send chiragkhabaria a Yahoo! Message  Reply with Quote
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
Go to Top of Page

ingineu
Yak Posting Veteran

89 Posts

Posted - 10/17/2005 :  12:48:07  Show Profile  Reply with Quote
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
Go to Top of Page

chiragkhabaria
Flowing Fount of Yak Knowledge

India
1907 Posts

Posted - 10/18/2005 :  02:13:30  Show Profile  Visit chiragkhabaria's Homepage  Send chiragkhabaria a Yahoo! Message  Reply with Quote
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
Go to Top of Page

ingineu
Yak Posting Veteran

89 Posts

Posted - 10/18/2005 :  14:33:59  Show Profile  Reply with Quote
tblShipmentDet is the Input and the Ouput. I am updating the tblShipmentDet records with the RATE from the tblShippers file.
Go to Top of Page

ingineu
Yak Posting Veteran

89 Posts

Posted - 10/24/2005 :  20:34:13  Show Profile  Reply with Quote
Is there no solution available?
Go to Top of Page

ingineu
Yak Posting Veteran

89 Posts

Posted - 10/24/2005 :  22:10:51  Show Profile  Reply with Quote
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.
Go to Top of Page

leetb
Starting Member

1 Posts

Posted - 01/30/2007 :  12:59:37  Show Profile  Reply with Quote
Could you please post your workaround. I am having the same issue, and don't exactly understand what you did.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000