求高人写复杂SQL代码解决办法

求高人写复杂SQL代码
现有两张表：
dbo.bargaindate：BargainDate,DateType,UpdateTime,id
dbo.tIndexReport:ID,IndexCode,IndexName,BargainDate,CloseIndex

简单来说：表一记录了所有交易日期，表二记录了每个指数每个交易日的收盘价，但有些指数缺少部分交易日的数据，需要找出来。小弟初学SQL，写了以下代码，只能查一个指数的缺少情况，求高人改下，可以把所有指数一次都查出来，结果显示出indexname,bargaindate（缺失的交易日）。拜谢。


select * 
from ( 
select dbo.bargaindate.BargainDate,a.closeindex
from dbo.bargaindate
left join (select * from dbo.tIndexReport where IndexName='有色金属' ) as a
on dbo.bargaindate.BargainDate=a.BargainDate
where dbo.bargaindate.bargaindate>(select min(bargaindate) from dbo.tindexreport where indexname='有色金属')-1 and dbo.bargaindate.bargaindate<'2011-12-31'

) as b
where b.closeindex is null
order by b.bargaindate


[解决办法]

SQL code

select a.name, b.bargaindate from (select indexcode, max(indexname)name, min(bargaindate)md from tindexreport group by indexcode) ainner join bargaindate b on a.md<=b.bargaindate and b.bargaindate<'2011-12-31'left join tindexreport c on a.indexcode=b.indexcode and b.bargaindate=c.bargaindatewhere c.indexcode is null
[解决办法]
探讨


服务器: 消息 207，级别 16，状态 1，行 4
列名 'indexcode' 无效。