Joining two different table with date condition

1

I want to join two table with date condition.

query of Ist table

SELECT [Item]
,[Effective Date]
,[Material Cost]
,[Operation Cost]
,[Std.Cost]
FROM [intelliviewdb].[dbo].[FMSTDCOSTEFFECTIVEDATE]
where [Item]='FM020001'
order by [Item],[Effective Date]

Query of 2nd table
select t_pdno as [Production Order],
ltrim(t_mitm) as [Item],
t_prdt as [Prod Start Date]
FROM [NPCINFORFP7L2].[inforfp7db].dbo.[ttisfc001400]
where t_pdno ='FMP009124'

Result of Ist table
Item Effective Date Material Cost Operation Cost Std.Cost
FM020001 2015-11-19 05:31:44.000 2.442 0.2684 2.7104
FM020001 2015-12-20 06:02:00.000 2.3204 0.2684 2.5888
FM020001 2016-01-21 08:19:37.000 2.2917 0.2684 2.5601
FM020001 2016-02-15 11:32:51.000 2.2917 0.35 2.6417
FM020001 2016-02-16 11:06:41.000 2.3566 0.35 2.7066

Result of 2nd table
Production Order Item Prod Start Date
FMP009124 FM020001 2016-02-14 03:00:00.000

My result would be like these
condition look the production start date of 2nd table it is less than or equal to effective date of 1st table then pick the last last value (descending)
Production Order Item Prod Start Date Mater Co Op. Cost Std.co
FMP009124 FM020001 2016-02-14 2.2917 0.2684 2.5601

2

Does this work? I have no idea. Without table definitions (CREATE TABLE), I'm just guessing at things. Sample data as INSERT statements would be helpful, too. Help us to help you.select a.* from ( select f.*, t.*, row_number() over (partition by f.Item order by t.t_prdt) rownum from [intelliviewdb].[dbo].[FMSTDCOSTEFFECTIVEDATE] f inner join [NPCINFORFP7L2].[inforfp7db].dbo.[ttisfc001400] t on f.item = f.item and t.t_prdt <= f.[Effective Date] where f.[Item]='FM020001' and t.t_pdno ='FMP009124' ) a where a.rownum = 1