|
#2
happynight2008-04-28 09:16
|
select DISTINCT convert(nvarchar(10),datepart(yy,a.ddate))+'-'+convert(nvarchar(10),
datepart(mm,a.ddate)) as 时间, as 仓库名称 ,(select isnull(sum(f.sl),0)
from m_kc as f
) as 库存合计 , (select isnull(sum(g.number),0) from m_stockcontent
as g where g.oddtype=1 and g.ddate>='2007-04-26' and g.ddate<='2008-07-27' ) as 进货数量,
(select isnull(sum(k.ypay),0) from m_stockdj as k where k.oddtype=1 and
k.ddate>='2007-04-26' and k.ddate<='2008-07-27') as 进货总金额,
(select isnull(sum(g.number),0) from m_stockcontent as g where g.oddtype=2 and
g.ddate>='2007-04-26' and g.ddate<='2008-07-27' ) as 退货数量,
(select isnull(sum(k.ypay),0) from m_stockdj as k where k.oddtype=2 and
k.ddate>='2007-04-26' and k.ddate<='2008-07-27') as 退货总金额,
(select isnull(sum(l.number),0) from m_sellcontent as l where l.oddtype=3 or
l.oddtype=5 and l.ddate>='2007-04-26' and l.ddate<='2008-07-27') as 销售数量,
(select isnull(sum(u.amount),0) from m_sellcontent as u where u.oddtype=3 or u.oddtype=5
and u.ddate>='2007-04-26' and u.ddate<='2008-07-27') as 销售总金额,
(select isnull(sum(j.number),0) from m_sellcontent as j where j.oddtype=4 or
j.oddtype=6 and j.ddate>='2007-04-26' and j.ddate<='2008-07-27') as 销售退货数量,
(select isnull(sum(u.amount),0) from m_sellcontent as u where u.oddtype=4 or u.oddtype=6 and
u.ddate>='2007-04-26' and u.ddate<='2008-07-27') as 销售退货总金额 ,
(select isnull(sum(j.number),0) from m_sellcontent as j where j.oddtype=7 and
j.ddate>='2007-04-26' and j.ddate<='2008-07-27') as 报损数量,
(select isnull(sum(u.amount),0) from m_sellcontent as u where u.oddtype=7 and
u.ddate>='2007-04-26' and u.ddate<='2008-07-27') as 报损总金额,
( (select isnull(sum(u.amount),0) from m_sellcontent as u where u.oddtype=3 or
u.oddtype=5 and u.ddate>='2007-04-26' and u.ddate<='2008-07-27' )-
(select isnull(sum(u.amount),0) from m_sellcontent as u where u.oddtype=4 or
u.oddtype=6 and u.ddate>='2007-04-26' and u.ddate<='2008-07-27')-
(select isnull(sum(u.amount),0) from m_sellcontent as u where u.oddtype=7 and
u.ddate>='2007-04-26' and u.ddate<='2008-07-27')- (select isnull(sum(l.number*u.stockprice),0)
from m_sellcontent as l join m_sp as u on u.id=l.spid where l.oddtype=3 or l.oddtype=5 and
l.ddate>='2007-04-26' and l.ddate<='2008-07-27') ) as 销售利润
from m_stockcontent as a
join m_ck as b on b.id=a.cid where a.ddate>='2007-04-26' and a.ddate<='2008-07-27'
group by datepart(yy,a.ddate) ,datepart(mm,a.ddate), order by 时间 desc
结果
2008-5 主仓库 2163 2010 153658.59 34 4916.8911 513 16190.3153 187 7324.4995 0 0.00 -14801.6119
2008-4 主仓库 2163 2010 153658.59 34 4916.8911 513 16190.3153 187 7324.4995 0 0.00 -14801.6119
我想显示每个月的数量 ····郁闷了 请高手 指点一下