查询某日期段明细报表，求“单据日期”字段三个前的最后一笔单价为原单价是多少？

[nui2009]

SELECT
T0.TaxDate AS '单据日期',T0.CardCode AS '供应商代码',T0.CardName AS '供应商名称',T0.U_DocCode AS '采购单号',
T1.ItemCode AS '物料编号',T2.U_FrgnName AS '规格型号',T2.ItemName AS '物料描述',T1.Currency AS '币别',T1.unitMsr AS '单位',

T4.PriceAfVAT AS '原单价',
T1. PriceAfVAT AS '现单价',
''AS '调价比率%', T1.Quantity AS '采购数量','' AS Total, /*降价金额*/
T1.U_PrNote3 AS '备注' ,T3.UserName AS '责任人'

FROM
OPOR T0
INNER JOIN
POR1 T1 ON T1.DocEntry=T0.DocEntry
LEFT JOIN
OITM T2 ON T2.ItemCode=T1.ItemCode
LEFT JOIN
T_CUSE T3 ON T3.UserID=T0.U_jockey
LEFT JOIN
( select T1.ItemCode,T0.CardCode, max(T0.TaxDate)
from OPOR T0
LEFT JOIN POR1 T1 ON T1.DocEntry=T0.DocEntry
where T0.TaxDate<=Dateadd(MM,-3,T0.TaxDate)

) T4 ON T4.ItemCode=T1.ItemCode AND T4.CardCode =T0.CardCode




WHERE (T0.TaxDate>='{0}' OR '{0}'='') AND (T0.TaxDate<='{1}' OR '{1}'='') /*查询何任一个日期段明细,原单价与现单价比较*/



TaxDate CardCode CardName U_DocCode temCode Quantity PriceAfVAT U_PrNote3 UserName
2012-6-15 J036 sdddddddd 11111 1130188 20.00 3.13 包装 张三
2012-6-18 J036 sdddddddd 11210 1130188 1,000.00 3.32 包装 李四
2012-19 J036 sdddddddd 11266 1130188 572.00 3.30 包装 李要
2012-6-23 J036 sdddddddd 11404 1130188 2,000.00 3.00 包装 张左
2012-10-17 J036 sdddddddd 12675 1130188 9,110.00 3.25 包装 张有
2012-10-24 J036 sdddddddd 11404 1130188 2,000.00 3.12 包装 张左
2012-10-25 J036 sdddddddd 12675 1130188 9,110.00 3.18 包装 张有

需要查询结果：查询10-1至10.31的明细及三个月前原单价。
TaxDate CardCode CardName U_DocCode ItemCode Quantity PriceAfVAT U_PrNote3 UserName 原单价
2012-10-17 J036 sdddddddd 12675 1130188 9,110.00 3.25 包装 张有 3.13
2012-10-24 J036 sdddddddd 11404 1130188 2,000.00 3.12 包装 张左 3.00
2012-10-25 J036 sdddddddd 12675 1130188 9,110.00 3.18 包装 张有 3.00

2012-10-17减去三个月最后一笔后为2012-6-17 3.13
2012-10-25、24减去三个月最后一笔后为2012-6-23 3.00

[nui2009]

T4.PriceAfVAT AS '原单价',
怎么用下面显示出原单价

LEFT JOIN
( select T1.ItemCode,T0.CardCode, max(T0.TaxDate)
from OPOR T0
LEFT JOIN POR1 T1 ON T1.DocEntry=T0.DocEntry
where T0.TaxDate<=Dateadd(MM,-3,T0.TaxDate)

[nui2009]

请各位高手帮助，谢谢！

[nui2009]

各位帮帮忙，谢谢

[汤姆克鲁斯]

[Quote=引用楼主 的回复:]
SELECT
T0.TaxDate AS '单据日期',T0.CardCode AS '供应商代码',T0.CardName AS '供应商名称',T0.U_DocCode AS '采购单号',
T1.ItemCode AS '物料编号',T2.U_FrgnName AS '规格型号',T2.ItemName AS '物料描述',T1.Currency AS '币别',T1.unitMsr A……
[/Quote]2012-10-17减去三个月最后一笔后为2012-6-17 3.13

10月减掉3个月是7月啊

就算你的计算机老师可以原谅你，但是你的数学老师一定不会的

[汤姆克鲁斯]

--> 测试数据：#tb
IF OBJECT_ID('TEMPDB.DBO.#tb') IS NOT NULL DROP TABLE #tb
GO 
CREATE TABLE #tb([TaxDate] VARCHAR(10),[CardCode] VARCHAR(4),[CardName] VARCHAR(9),[U_DocCode] INT,[temCode] INT,[Quantity] VARCHAR(8),[PriceAfVAT] NUMERIC(3,2),[U_PrNote3] VARCHAR(4),[UserName] VARCHAR(4))
INSERT #tb
SELECT '2012-7-15','J036','sdddddddd',11111,1130188,'20.00',3.13,'包装','张三' UNION ALL
SELECT '2012-7-18','J036','sdddddddd',11210,1130188,'1,000.00',3.32,'包装','李四' UNION ALL
SELECT '2012-7-19','J036','sdddddddd',11266,1130188,'572.00',3.30,'包装','李要' UNION ALL
SELECT '2012-7-23','J036','sdddddddd',11404,1130188,'2,000.00',3.00,'包装','张左' UNION ALL
SELECT '2012-10-17','J036','sdddddddd',12675,1130188,'9,110.00',3.25,'包装','张有' UNION ALL
SELECT '2012-10-24','J036','sdddddddd',11404,1130188,'2,000.00',3.12,'包装','张左' UNION ALL
SELECT '2012-10-25','J036','sdddddddd',12675,1130188,'9,110.00',3.18,'包装','张有'
--------------开始查询--------------------------

SELECT *, 原单价=(select top 1 [PriceAfVAT] from #tb where [TaxDate]<dateadd(mm,-3,t.[TaxDate]) order by [TaxDate] desc )
FROM #tb as t where [TaxDate] between '2012-10-01' and '2012-10-31'
----------------结果----------------------------
/* 
TaxDate	CardCode	CardName	U_DocCode	temCode	Quantity	PriceAfVAT	U_PrNote3	UserName	原单价
2012-10-17	J036	sdddddddd	12675	1130188	9,110.00	3.25	包装	张有	3.13
2012-10-24	J036	sdddddddd	11404	1130188	2,000.00	3.12	包装	张左	3.00
2012-10-25	J036	sdddddddd	12675	1130188	9,110.00	3.18	包装	张有	3.00
*/