January 20, 2010 at 1:49 pm
I am trying to join two tables, but the criteria of is not this month=last month. It should be
inner join table2 table1.ref_date=table2.last month ref_date
Both tables are monthly historical data. Just table1's month is one month ahead of table 2. New to SQL. Thanks a lot.
January 20, 2010 at 1:53 pm
It is a little unclear what you are asking for. I see you are new to SSC as well. Please read through the article in my signature line. It will walk you through posting a question with sample data and expected results. That would help us give you the advice you need.
January 20, 2010 at 1:54 pm
Please read the first article I have referenced below in my signature block regarding asking for assistance. Please follow the instructions in that article and provide us with table definitions (CREATE TABLE statements), sample data (as a series of INSERT INTO statements) for the tables. the code you have tried yourself (helps us know what you are thinking and trying to accomplish), and (not shown in the article) expected results based on the sample data you provide as this shows us what our code should return.
Doing this will get you much better assistance, and in return you will also get tested code.
January 20, 2010 at 2:06 pm
Thanks for the prompt reply. Here is the tables.
Table 1:
stock_ID, Ref_date, stock price
M1, 20090731, 90
M2, 20090831, 89
M3, 20090930, 67
Table 2:
Stock_ID, Ref_Date, Character
M1, 20090630, Good
M2, 20090731, Good
M3. 20090831, Poor
Select * from Table1
inner join table2 on table1.stock_ID=table2.stock.ID and table1.ref_date=table2.last month of ref_date
Which means table1's ref_date is 1 month ahead of table2. I need to match July in table1 to jun in table 2.
Thanks a lot.
January 20, 2010 at 2:11 pm
What data type are your tables using for ref_date? Is it integer as shown (bad practice) or datetime?
January 20, 2010 at 2:12 pm
Datetime. Thanks.
January 20, 2010 at 2:15 pm
So given the sample data you've provided, what would your expected result set look like?
January 20, 2010 at 2:30 pm
Select * from Table1
inner join table2 on table1.stock_ID=table2.stock.ID and table1.ref_date=table2.last month of ref_date
I don't know how to do: table1.stock_ID=table2.stock.ID and table1.ref_date=table2.[font="Arial"]last month[/font] of ref_date. I guess I need a function to match July and Jun. Since the date of two months (20090731 and 20090630) will not be exactly matched.
January 20, 2010 at 2:34 pm
So what would you expect the results to be with your sample data? Which rows do you want the query to return?
January 20, 2010 at 2:36 pm
I want the result to be:
M1, 20090731, 90, good
M2, 20090831, 89, good
M3, 20090930, 67, poor
January 20, 2010 at 2:40 pm
Well, it looks to me like you have not taken into account your requirement for the Month-1 logic. To produce the results you've shown with your sample data, it is just a simple INNER JOIN:
DECLARE @Table1 TABLE (stock_ID char(2), Ref_date datetime, stock_price int)
INSERT INTO @Table1
SELECT 'M1', '2009-07-31', 90 UNION ALL
SELECT 'M2', '2009-08-31', 89 UNION ALL
SELECT 'M3', '2009-09-30', 67
DECLARE @Table2 TABLE (Stock_ID char(2), Ref_Date datetime, [Character] varchar(10))
INSERT INTO @Table2
SELECT 'M1', '2009-06-30', 'Good' UNION ALL
SELECT 'M2', '2009-07-31', 'Good' UNION ALL
SELECT 'M3', '2009-08-31', 'Poor'
SELECT t1.stock_id,
t1.ref_date,
t2.[character]
FROM @Table1 t1
INNER JOIN @Table2 t2
ON t1.stock_id = t2.stock_id
January 20, 2010 at 2:41 pm
Would something like the following work?
Please note that I added some more lines to make the concept a little easier to understand since your sample data could have been joined on stock_id only to give the expected output...
DECLARE @t1 TABLE (stock_ID char(2), Ref_date datetime, stockprice int)
INSERT INTO @t1
SELECT 'M1', '20090731', 90 UNION all
SELECT 'M2', '20090831', 89 UNION all
SELECT 'M1', '20090831', 89 UNION all
SELECT 'M3', '20090930', 67
DECLARE @t2 TABLE (stock_ID char(2), Ref_date datetime, Charac varchar(30))
INSERT INTO @t2
SELECT 'M1', '20090630', 'Good' UNION all
SELECT 'M1', '20090731', 'Poor' UNION all
SELECT 'M1', '20090601', 'Poor' UNION all
SELECT 'M2', '20090731', 'Good' UNION all
SELECT 'M3', '20090831', 'Poor'
SELECT * FROM @t1
SELECT * FROM @t2
SELECT t1.*,t2.charac
FROM @t1 t1
INNER JOIN @t2 t2
ON t1.stock_ID = t2.stock_ID
AND t2.ref_date < dateadd(month,datediff(month,0,t1.ref_date),0)
AND t2.ref_date >= dateadd(month,datediff(month,0,t1.ref_date)-1,0)
ORDER BY t1.ref_date,t1.stock_id
/* result set:
stock_IDRef_datestockpricecharac
M12009-07-31 00:00:00.00090Good
M12009-07-31 00:00:00.00090Poor
M12009-08-31 00:00:00.00089Poor
M22009-08-31 00:00:00.00089Good
M32009-09-30 00:00:00.00067Poor
*/
January 20, 2010 at 2:56 pm
Lutz, this is exactly what I want,
AND t2.ref_date < dateadd(month,datediff(month,0,t1.ref_date),0)
AND t2.ref_date >= dateadd(month,datediff(month,0,t1.ref_date)-1,0)
. Thank you so much.
And thanks to others too. Now I learnt how to post questions. Good day.
Viewing 13 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply