Solved

# T-SQL Querry

Posted on 2010-01-08
Medium Priority
255 Views
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

LVL 75

Expert Comment

ID: 26211452
try joining like this

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

LVL 1

Author Comment

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

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

LVL 1

Author Comment

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

LVL 27

Expert Comment

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

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

LVL 1

Author Comment

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

LVL 1

Author Comment

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

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.
0

LVL 27

Expert Comment

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

LVL 1

Author Comment

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

ID: 26275485
Figured it out.  Used a theta join.  Thanks for the help everyone.
0

LVL 27

Expert Comment

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

## Featured Post

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, â€¦