一个数据统计有关问题

一个数据统计问题
 商品买卖的统计问题
基础表记录 每次进货和出货的信息，主要包括 日期 商品名称 买卖标识 数量 单价 额外费用，这个额外费用，是算在成本里面的，比如：买的时候运费，卖的时候租金（房租摊下来）具体不用管，算到成本里面就可以。
需要统计：
1、每次进货的收益（单个商品）：（出货数量×单价-额外费用）-（进货数量*单价+额外费用）
2、每个月收益按商品：相当于对没次进货收益 按商品名称和日期group一下
但问题是 一次进货的商品 可能分多次才能卖完。
ID 日期  标识   商品  单价 数量 额外费用（标识 1进货 0出货）
12012-10-05 1牙膏3.520020
22012-10-06 0牙膏6  505  
32012-10-07 0牙膏5  13010
42012-10-08 1牙膏3.8  12015
52012-10-09 0牙膏6  806
92012-10-10 0牙膏6  605
同样的商品，因为进货或者出货的数量不一样，价格也可能不一样的。
上面这几条记录：
 日期  商品名称   亏盈（亏是负数）
2012-10-05  牙膏 （6*20-6/2（不是同一次进货，额外费用平摊））+（5*130-10）+（6*50-5）-（3.5*200+20）=332
2012-10-08  牙膏  （60*6-6/2）+（60*6-5）-（3.8*120+15）=241

因为每次出货的商品，可能不是同一次进的货。稍微有些麻烦，为了统计方便，也可以添加一个中间表，麻烦各位大侠，帮看一下。

 
[最优解释]
---------------------------------
--  Author: HEROWANG(让你望见影子的墙)
--  Date  : 2012-11-05 13:22:44
--  blog  : blog.csdn.net/herowang
---------------------------------
 
IF OBJECT_ID('[tb]') IS NOT NULL 
    DROP TABLE [tb]
go
CREATE TABLE [tb] (ID INT,日期 DATETIME,标识 INT,商品 VARCHAR(4),单价 NUMERIC(2,1),数量 INT,额外费用 INT)
INSERT INTO [tb]
SELECT 1,'2012-10-05',1,'牙膏',3.5,200,20 UNION ALL
SELECT 2,'2012-10-06',0,'牙膏',6,50,5 UNION ALL
SELECT 3,'2012-10-07',0,'牙膏',5,130,10 UNION ALL
SELECT 4,'2012-10-08',1,'牙膏',3.8,120,15 UNION ALL
SELECT 5,'2012-10-09',0,'牙膏',6,80,6 UNION ALL
SELECT 9,'2012-10-10',0,'牙膏',6,60,5
--select * from [tb]
;
with
wang as(
select *,zj=case 标识 when 1 then -(单价*数量+额外费用) else 单价*数量-额外费用 end,sl=case 标识 when 1 then 数量 else -数量 end
from tb),
wang1 as
(
select 日期,商品,sl=case when exists(select 1 from wang where 商品=t.商品 and 日期>t.日期 and 标识=1) 
      then (select sum(sl) from wang where 商品=t.商品 and 日期>=t.日期 and 日期<(select top 1 日期 from wang where 日期>t.日期 and 标识=1 order by 日期))
      else (select sum(sl) from wang where 商品=t.商品 and 日期>=t.日期) end
,单价=case when exists(select 1 from wang where 商品=t.商品 and 日期>t.日期 and 标识=1) 
      then (select top 1 单价 from wang where 日期>(select top 1 日期 from wang where 日期>t.日期 and 标识=1 order by 日期))
      else (select top 1 单价 from wang where 商品=t.商品 and 日期>t.日期) end
,额外费用=case when exists(select 1 from wang where 商品=t.商品 and 日期>t.日期 and 标识=1) 
      then (select count(*) from wang where 日期>(select top 1 日期 from wang where 日期>t.日期 and 标识=1 order by 日期))
      else (select count(*) from wang where 商品=t.商品 and 日期>t.日期) end

from wang t
where 标识=1
),
wang2
as
(select *,盈亏=sl*单价-单价/额外费用 from wang1)

select t.日期,t.商品, 盈亏=盈亏+isnull((select sum(zj) from wang where 商品=t.商品 and 日期>=t.日期 and 日期<(select top 1 日期 from wang where 日期>t.日期 and 标识=1 order by 日期)),(select sum(zj) from wang where 商品=t.商品 and 日期>=t.日期))
from wang t left join wang2 on t.日期=wang2.日期 and t.商品=wang2.商品

2012-10-05 00:00:00.000牙膏332.0
2012-10-06 00:00:00.000牙膏NULL
2012-10-07 00:00:00.000牙膏NULL
2012-10-08 00:00:00.000牙膏235.0
2012-10-09 00:00:00.000牙膏NULL
2012-10-10 00:00:00.000牙膏NULL

ps：
没有搞清楚60*6-6/2 后面的6/2是什么意思，第二个有一些误差。比较麻烦，


[其他解释]

引用:

---------------------------------
--  Author: HEROWANG(让你望见影子的墙)
--  Date  : 2012-11-05 13:22:44
--  blog  : blog.csdn.net/herowang
---------------------------------
 
IF OBJECT_ID(……

膜拜啊   写了一半我直接给脚本删了  是在不想写下去  太麻烦了  这个问题放在数据库做 性能什么的就不要怎么奢望了吧
[其他解释]

引用:

那6*20-6/2 中的6/2这个6 是什么？是这20个的出货价格？

（60*6-6/2）+中的6*60 是什么？

5 2012-10-09  0 牙膏 6   80 6
相当于这次出货的80里面 有第一次进货的20，还有后面一次进货的60个，所以要分开来算。就是分拆这块儿比较麻烦，2012-10-05 进货200，到2012-10-09的时候只剩下20，原则上总是以前的货先出，所以2012-10-09 出货80就相当于 先出2012-10-05剩余的20，额外费用均分就是（20*6+6/2），剩余的卖是第二次进货（60*6-6/2）
[其他解释]
 非常感谢，是可以的，结贴。
[其他解释]
null
[其他解释]

引用:

那6*20-6/2 中的6/2这个6 是什么？是这20个的出货价格？

（60*6-6/2）+中的6*60 是什么？

楼主的意思应该是60是2012-10-09这个出货里面减去上一次进货没消售完的20，所以是60。
楼主的需求我是明白了，等待高手解决，学习下。
[其他解释]
6*20-6/2（不是同一次进货，额外费用平摊

为什么是6*20 
[其他解释]

引用:

6*20-6/2（不是同一次进货，额外费用平摊

为什么是6*20

因为第一次进货 200，后面出货2次分别是50 和130 ，剩余的20 在  2012-10-09 出货的 80里面啊。这也是比较麻烦的地方。
[其他解释]

 那6*20-6/2 中的6/2这个6 是什么？是这20个的出货价格？

（60*6-6/2）+中的6*60 是什么？
[其他解释]
好了，OK了

wang2
as
(select *,盈亏=case when sl>0 then sl*单价-单价/额外费用  else sl*单价+单价/额外费用 end from wang1)



select t.日期,t.商品, 盈亏=盈亏+isnull((select sum(zj) from wang where 商品=t.商品 and 日期>=t.日期 and 日期<(select top 1 日期 from wang where 日期>t.日期 and 标识=1 order by 日期)),(select sum(zj) from wang where 商品=t.商品 and 日期>=t.日期))

from wang t left join wang2 on t.日期=wang2.日期 and t.商品=wang2.商品

就是把wang2 的生成稍微修改一下。
2012-10-05 00:00:00.000牙膏332.0
2012-10-06 00:00:00.000牙膏NULL
2012-10-07 00:00:00.000牙膏NULL
2012-10-08 00:00:00.000牙膏241.0
2012-10-09 00:00:00.000牙膏NULL
2012-10-10 00:00:00.000牙膏NULL

这样后面这个也对上了。这个确实很麻烦
[其他解释]
null

查看更多 下一篇