求本年本期数、上年同期、同比。。。

xiaohua_xu 2012-09-21 09:20:14
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.[商品代码]

以这脚本求出来的上年同期数,结果是上年整年的数据。而不是上年同期的数据。请各们大侠帮忙看看。脚本问题出在哪里。谢谢!
...全文
430 2 打赏 收藏 转发到动态 举报
写回复
用AI写文章
2 条回复
切换为时间正序
请发表友善的回复…
发表回复
xiaohua_xu 2012-09-24
  • 打赏
  • 举报
回复
可否要告诉我怎么写才可以实现吗?
结果正常全分。
人生无悔 2012-09-21
  • 打赏
  • 举报
回复
你都沒有取月份或限制月份的地方,只有會計年度,肯定是取整年的

27,579

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 应用实例
社区管理员
  • 应用实例社区
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧