SQL Question: - SQL Server Forums

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

Transact-SQL (2000)

SQL Question:

Author

Topic

mhg1063
Starting Member

27 Posts

Posted - 2004-09-23 : 09:19:38

I’m trying to extract data from a table for a particular combination. AS I’m building my syntax I’m having an issue getting the proper value. The prices table has five columns and stores pricing information. Every day records are written to the table the following fields
mkt_dte (datetime),inst(varchar(30), pr_in_dy(int), rate(numeric), price(numeric). There are many inst,pr_in_dy,rate combinations. My task is to extact fields a subset of fields from a different table.

Here is my subset:
select r.profile_name,
r.loan_id,
r.investor_instrument_name,
r.rate,
r.lock_date,
r.next_auction_date,
DATEDIFF(dd,dbo.sf_ps_dateonly(lock_date),dbo.sf_ps_dateonly(next_auction_date))as pricing_window_calc,

Next I have to Using prices find:
The market date nearest to (while being equal to or greater than) the lock date.
For the investor_instrument and rate combination.
Which I’ve accomplished with:

select r.profile_name,
r.loan_id,
r.investor_instrument_name,
r.rate,
r.lock_date,
r.next_auction_date,
DATEDIFF(dd,dbo.sf_ps_dateonly(lock_date),dbo.sf_ps_dateonly(next_auction_date))as pricing_window_calc,
min(rp.market_date),

from rm_l r

LEFT OUTER JOIN prices rp on r.investor_instrument_name =rp.investor_instrument_name
AND r.note_rate = rp.rate
AND rp.market_date > = r.lock_date

GROUP BY
r.profile_name,
loan_id,
r.investor_instrument_name,
r.note_rate,
r.rate,r.lock_date,
next_auction_date

Here’s the part that’s throwing me for a loop. I need to Return the price for the pricing window that is nearest to (while being equal or greater than) the pricing window calculated in Step #2. Once I have the proper market_date. I have a few choices for the price. SO if my pricing window calculated is 75 and my choices are the following. I would want the third row.
market_date investor_instrument_name pricing_win_in_days rate price ----------------------------------------------- ------------------------------ ---------------------- ------------- -----------------
2004-07-06 14:14:47.000 15 fhlmc mbs 13 5.50000000 102.71875000
2004-07-06 14:14:47.000 15 fhlmc mbs 41 5.50000000 102.37500000
2004-07-06 14:14:47.000 15 fhlmc mbs 75 5.50000000 101.96875000
2004-07-06 14:14:47.000 15 fhlmc mbs 104 5.50000000 101.50000000

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-23 : 10:36:48

so isn't this a simple inner join between step 1 and step 2 on pricing_win_in_days??

Go with the flow & have fun! Else fight the flow

mhg1063
Starting Member

27 Posts

Posted - 2004-09-23 : 12:19:37

I don't think so. 75 was probably a bad example since one of the actual pricing_win_in_days is 75 . What if my pricing window calculated is 58 and my choices are the following. I would want the third row.
market_date investor_instrument_name pricing_win_in_days rate price ----------------------------------------------- ------------------------------ ---------------------- ------------- -----------------
2004-07-06 14:14:47.000 15 fhlmc mbs 13 5.50000000 102.71875000
2004-07-06 14:14:47.000 15 fhlmc mbs 41 5.50000000 102.37500000
2004-07-06 14:14:47.000 15 fhlmc mbs 75 5.50000000 101.96875000
2004-07-06 14:14:47.000 15 fhlmc mbs 104 5.50000000 101.50000000

mhg1063
Starting Member

27 Posts

Posted - 2004-10-07 : 11:14:55

It's been resolved. Using derived tables. Thanks to all.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources