?
Solved

T-SQL Querry

Posted on 2010-01-08
13
Medium Priority
?
255 Views
Last Modified: 2012-05-08
I have two tables.  One table shows Corn Future Settlement dates from 1971-2010.  This table has 6-10 transactions a day (one for the march contract, one for the may contract, etc.)  The other table shows corn transactions of individuals for a given day (e.g. 1996-03-25).  I want to find the difference (basis) between the corn contract and the settlement price.  The problem I'm having is being able to find the corn contract that needs to be used.  For instance, if the transaction happened in March 25, 1996, then I would want to use the March Contract, not the may, aug, dec, contract.  I could use a cursor, but was wondering if I could use a join to do this with.  Any Sugestions?
0
Comment
Question by:badrhino
13 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 26211452
try joining like this

Month(transactionDate) = month(contractDate) and Year(TransactionDate) = year(contractDate)
0
 
LVL 1

Author Comment

by:badrhino
ID: 26211635
Thanks for the quick response.  However, I don't think the above will work because the corn contracts are not every month.  Corn contracts are Mar, May, Jul, Sep, and Dec.  So if corn is sold in June, the July contract has to be used. Likewise, if corn is sold in July after the July contract is settled, then the Sept contract has to be used.  
0
 
LVL 27

Expert Comment

by:Chris Luttrell
ID: 26212079
are you running this on a SQL 2000 machine?  your tag suggests that you are even though you posted it in the SQL 2008 zone.  Just checking because people will give you solutions that will run in 2008 but not on 2000 if it is not clear.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 1

Author Comment

by:badrhino
ID: 26212130
Yes. I'm running on SQL Enterprise 2000.   Sorry about the confussion.
0
 
LVL 27

Expert Comment

by:Chris Luttrell
ID: 26212366
no problem
couple more questions:
1. you said "This table has 6-10 transactions a day (one for the march contract, one for the may contract, etc.) " but later mention "Corn contracts are Mar, May, Jul, Sep, and Dec" which is only 5, so where do the 6-10 transactions come from?
2. you say "if corn is sold in July after the July contract is settled, then the Sept contract has to be used" how do you know what day the July contract is settled to be able to make that switch?
3. are you opposed to creating a helper table to hold the contract date ranges, ie
Year  ContractMonth  StartDt          EndDt
1971 Mar                    1/1/1971      3/x/1971
1971 May                    3/x+1/1971  5/?/1971
etc
this could probably be built automatically from your existing tables as long as there is decent logic for the contract settlement datein question #2.
0
 
LVL 41

Expert Comment

by:Sharath
ID: 26213356
Can you post some sample data from both the tables and your expected result?
0
 
LVL 1

Author Comment

by:badrhino
ID: 26213618
Sorry for the delay....I sure can.  Give me 15 minutes....
0
 
LVL 1

Author Comment

by:badrhino
ID: 26213971
Ok,  I saved in Excel 2003 format so hopefully everyone can access it.  Table 1 is a list of the contracts and the the column LastTrade is the date for the ClosePrice.  Table 2 shows when the contract expires (the contract roll date).  Table 3 shows the sale data for each transaction. Saledate is the data of sale, avgprice is the average price for the sale, and year is the year.

What I need to do is based on the saledate, I need to find which ClosePrice (from table 1) to use.  As you can see, in table one LastTrade has multiple contracts for the date '19780419'.  I need to find the closest contract (Closest in time) and use the CLosePrice from this table to calcualte a basis on table 3; however, I can not have any contract from the past---meaning if today was '19780421' then I could not use the Apr 1978 contract, I would have to use the May1978 contract
Futures.xls
0
 
LVL 1

Author Comment

by:badrhino
ID: 26214003
CGLuttrell:  I didn't see your comment.  
1. I am doing this for multiple commodities--Your right corn only has 5 transactions per day
2. we know when to switch based on a table of RollDates (I attached an Excel example of this--I called it table 2 on the first sheet)
3I'm not opposed to anything as long as it is correct.
Thanks for your time....
0
 
LVL 27

Expert Comment

by:Chris Luttrell
ID: 26214023
thanks, I'll look at your examples to see what we can do.
0
 
LVL 1

Author Comment

by:badrhino
ID: 26214050
I just noticed that I have a slight mistak on my attached file.  On "Table I wish to get" the fourth field Table1.LastTrade, should have the same date as the first fireld Table3.SaleDate
0
 
LVL 1

Accepted Solution

by:
badrhino earned 0 total points
ID: 26275485
Figured it out.  Used a theta join.  Thanks for the help everyone.
0
 
LVL 27

Expert Comment

by:Chris Luttrell
ID: 26275899
Glad you found something.  But what are you meaning by "a theta join"?
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question