同期数据

[xiaohua_xu]

drop table t1
drop table t2
drop table t5
SELECT
distinct
([采购发票分录].[FAmount]) AS [金额], ([采购发票分录].[FPrice]) AS [单价],
([采购发票分录].[FQty]) AS [数量], ([商品表].[FName]) AS [商品名称],
([商品表].[FTypeID]) AS [商品分类], ([商品表].[FNumber]) AS [商品代码],
([采购发票].[FYearPeriod]) AS [钩稽期间],
([采购发票].[FYear]) AS [年份]
into t1
FROM [ICPurchase] AS [采购发票] INNER JOIN [ICPurchaseEntry] AS [采购发票分录]
ON [采购发票].[FInterID]=[采购发票分录].[FInterID]
LEFT JOIN [t_ICItem] AS [商品表] ON [商品表].[FItemID]=[采购发票分录].[FItemID]
-- where [采购发票].[FYearPeriod]>='2012-01'and [采购发票].[FYearPeriod]<='2012-06'
--and [商品表].[FNumber]='1.AZPJ.02' ORDER BY ([商品表].[FNumber]) ASC
--===================================================================================================================================
SELECT ([采购发票分录].[FAmount]) AS [上年同期金额],
case when [采购发票分录].[FQty]<>'0'then
[采购发票分录].[FPrice]/[采购发票分录].[FQty]
else 0 end AS [上年同期平均单价],
([采购发票分录].[FQty]) AS [上年同期数量],
([商品表].[FNumber]) AS [商品代码],
([采购发票].[FYear]) AS [年份]
into t2
FROM [ICPurchase] AS [采购发票] INNER JOIN [ICPurchaseEntry] AS [采购发票分录]
ON [采购发票].[FInterID]=[采购发票分录].[FInterID] LEFT JOIN [t_ICItem] AS [商品表]
ON [商品表].[FItemID]=[采购发票分录].[FItemID]
where ([采购发票].[FYear])=YEAR(GETDATE())-1
--[采购发票].[FYearPeriod]>='2012-03'and [采购发票].[FYearPeriod]<='2012-04'and [商品表].[FNumber]='1.AZPJ.02' ORDER BY ([商品表].[FNumber]) ASC
--============================================================================================================================
select distinct
t3.FYear AS [会计年度], t3.FPeriod AS [会计期间], t3.FBegBal AS 期初结存余额,t3.FBegQty AS 期初结存数量,
case when t3.FBegQty<>'0'then t3.FBegBal/t3.FBegQty else 0 end AS [年初库存单价],
T4.FNumber AS [商品代码]
into t5
FROM ICInvBal AS T3 LEFT JOIN t_ICItem AS T4
ON T3.FItemID =T4.FItemID
--WHERE (t3.FYear)=YEAR(GETDATE())and (t3.FPeriod)=month(1)
---=========================================================================
select distinct
t1.*,t2.[上年同期金额],t2.[上年同期数量],t5.[会计期间],t5.[会计年度],
case when t2.[上年同期平均单价]<>'0'THEN
t2.[上年同期平均单价]* 1
else t1.[单价]end AS [上年同期平均单价],
case when t2.[上年同期数量]<>'0'then
T2.[上年同期金额]/t2.[上年同期数量]else t1.[单价] end AS [上年同期平均单价],t5.[年初库存单价],
case when t5.[年初库存单价]<>'0'then t5.[年初库存单价] * 1
else 2 end AS [年初库存单价]
from t1
left join t2 on t1.[商品代码]=t2.[商品代码]
left join t5 on t2.[商品代码] = t5.[商品代码] and t1.[年份]= t5.[会计年度]


where t1.[商品代码]='1.AZPJ.02' AND t5.[年初库存单价]<>'0'and t5.[会计年度]='2012'

order by t1.[商品代码]

数据不对。。且数据有重复。
想要的结果是：
金额 单价 数量 商品名称 商品分类 商品代码 钩稽期间 年份 上年同期金额 上年同期数量 会计期间 会计年度 上年同期平均单价 上年同期平均单价 年初库存单价 年初库存单价
6000.0000 60.0000000000 100.0000000000 音箱支架 _306 黑色 40069 1.AZPJ.02 2012-03 2012 6600.0000 110.0000000000 1 2012 0.54545455 60.000000000000 60.0000000000 60.00000000
6000.0000 60.0000000000 100.0000000000 音箱支架 _306 黑色 40069 1.AZPJ.02 2012-03 2012 6600.0000 110.0000000000 2 2012 0.54545455 60.000000000000 60.0000000000 60.00000000
6000.0000 60.0000000000 100.0000000000 音箱支架 _306 黑色 40069 1.AZPJ.02 2012-03 2012 6600.0000 110.0000000000 3 2012 0.54545455 60.000000000000 60.0000000000 60.00000000

[Mr_Nice]

lz这个问题应该是出在同期的时间比对上。 自己检查一下同期的时间比对看看。

另外，重复的问题，lz先看看链接相关的部分。

参考

[xiaohua_xu]

看不出来是哪里的问，请各位兄弟帮忙改一下脚本，可以不。
小弟先谢过了。

[xiaohua_xu]

这里刚才看过。T1 T2表中的日期格式为：2012-01 而t5表中的会计区间只用1，2，3表示
T2 where ([采购发票].[FYear])=YEAR(GETDATE())-1 这语句是本年-1，不知道这里需要怎么改才行。请各位兄弟帮忙看看。谢谢！

[xiaohua_xu]

有哪位大侠改脚本后， 结果正确，再加40份给你。