试试看下面的sql query。不是很清楚你要的东西,我给你的这个可以从attendance看到salary amount base on attendance date。如果是你想要的话,建议你create个view statement 。
select a.*, b.dblSalAmt from tblAtt a
left outer join tblSalary b on a.strEmpUid = b.strEmpUid
where a.dtDateRecord >= b.dtDateAdjust作者: AhPang 时间: 2010-8-4 10:55 AM
上面那个有duplicate,给你另一个试试看。不过有点乱
select e.*, f.dtDateAdjust, f.dblSalAmt from tblAtt e
left outer join
(select c.strEmpUid, c.dtDateRecord, c.dtDateAdjust, d.dblSalAmt
(select a.strEmpUid,a.dtDateRecord, max(b.dtDateAdjust) as dtDateAdjust from tblAtt a
left outer join tblSalary b on a.strEmpUid = b.strEmpUid
where a.dtDateRecord >= b.dtDateAdjust
group by a.strEmpUid,a.dtDateRecord) c
left outer join tblSalary d on c.strEmpUid=d.strEmpUid and c.dtDateAdjust=d.dtDateAdjust) f
on e.strEmpUid=f.strEmpUid and e.dtDateRecord = f.dtDateRecord作者: zechs 时间: 2010-8-7 02:29 PM
SELECT intAttID, strEmpUid, dtDateRecord,
(SELECT dblSalAmt FROM tblSalary WHERE tblAtt.strEmpUid = tblSalary.strEmpUid
AND tblSalary.dtDateAdjust <= tblAtt.dtDateRecord ORDER BY tblAtt.dtDateAdjust DESC TOP 1/LIMIT 1)
FROM tblAtt