DECLARE @BgnDt DateTime ,
@EndDt DateTime
DECLARE @Customerid Varchar(20)declare @areaid varchar(20)SELECT @BgnDt ='2008-07-01'--:BgnDt
SELECT @EndDt ='2009-04-01'--:EndDt/*
SELECT @Customerid = '%'
SELECT @AreaID = '%'
*/
select tabinfo2.classid,tabinfo2.classname,tabinfo2.classnamedetail,
tabinfo2.daareaname,tabinfo2.areaname,tabinfo2.simplename,tabinfo2.userdef2 as pinpai,
--tabinfo2.outstockdt,tabinfo2.yewuqi,
--tabinfo2.fahuoje,
/*
sum(case tabinfo2.userdef2 when '惠步舒' then tabinfo2.fahuoje else 0 end) as '惠步舒',
sum(case tabinfo2.userdef2 when 'INT' then tabinfo2.fahuoje else 0 end) as 'INT',
sum(case tabinfo2.userdef2 when 'Durr迪尔' then tabinfo2.fahuoje else 0 end) as 'Durr迪尔',
sum(case tabinfo2.userdef2 when '小天使' then tabinfo2.fahuoje else 0 end) as '小天使',
sum(case tabinfo2.userdef2 when 'Q宝贝' then tabinfo2.fahuoje else 0 end) as 'Q宝贝',
sum(case tabinfo2.userdef2 when '蔚蓝系列' then tabinfo2.fahuoje else 0 end) as '蔚蓝系列',
sum(case tabinfo2.userdef2 when '《婴儿&母亲》' then tabinfo2.fahuoje else 0 end) as '《婴儿&母亲》'
*/sum(case tabinfo2.yewuqi when '2008-07' then tabinfo2.fahuoje else 0 end) as '2008-07',
sum(case tabinfo2.yewuqi when '2008-08' then tabinfo2.fahuoje else 0 end) as '2008-08',
sum(case tabinfo2.yewuqi when '2008-09' then tabinfo2.fahuoje else 0 end) as '2008-09',
sum(case tabinfo2.yewuqi when '2008-10' then tabinfo2.fahuoje else 0 end) as '2008-10',
sum(case tabinfo2.yewuqi when '2008-11' then tabinfo2.fahuoje else 0 end) as '2008-11',
sum(case tabinfo2.yewuqi when '2008-12' then tabinfo2.fahuoje else 0 end) as '2008-12',
sum(case tabinfo2.yewuqi when '2009-01' then tabinfo2.fahuoje else 0 end) as '2009-01',
sum(case tabinfo2.yewuqi when '2009-02' then tabinfo2.fahuoje else 0 end) as '2009-02',
sum(case tabinfo2.yewuqi when '2009-03' then tabinfo2.fahuoje else 0 end) as '2009-03',
sum(case tabinfo2.yewuqi when '2009-04' then tabinfo2.fahuoje else 0 end) as '2009-04'
from (select sal_.classid,
sal_.classname,
sal_.classnamedetail,
sal_.userdef2,
Sal_.Custid,
Sal_.SimpleName,
isnull(Sal_.AreaName,'BYM无地区') as AreaName,
isnull(Sal_.DaAreaName,'BYM无省区') as DaAreaName,
sal_.outstockdt,
--Sal_.nianyue,
--round(SUM(isnull(ordernum,0)),4) AS ordernum ,
--round(sum(isnull(orderje,0)),2) as orderje,
--round(sum(isnull(fahuonum,0)),2) as fahuonum,
round(SUM(isnull(fahuoje,0)),4) AS fahuoje,
sal_.yewuqi
--Sal_. Refrom(select 'classnamedetail'= case when pic.classid like '00102001%' then 'INT'
when pic.classid like '00102002%' then '惠步舒（06款）'
when pic.classid like '00102003%' then '惠步舒（08款）'
when pic.classid like '00102004%' then 'Durr迪尔'
when pic.classid like '00102005%' then '小天使婴儿鞋'
when pic.classid like '00102006%' then '蔚蓝系列'
when pic.classid like '00103001%' then '《婴儿&母亲》'
when pic.classid like '00102009%' then '惠步舒（07款）'
else 'null'
end,
SM.userbillid,sd.itemno,pci.classid,pic.classname,pim.userdef2,
SM.Custid,JOIN_CustID.SimpleName ,SM.outstockdt,
IsNull(PEp.AreaCode,'BYM') as AreaCode, IsNull(PA.AreaName,'BYM无地区') as AreaName ,
IsNull(PA2.AreaName,'BYM无省区') as DaAreaName ,
substring(convert(nvarchar(20),pfp.enddt,20),1,7) as yewuqi,
--sm.createdt,
round(isnull(sd.qtyoftradems,0) * sd.pricebtax ,2)as fahuoje
FROM SALSendBillMaster SM
JOIN SALSendBillDetail SD ON SD.SysBillID = SM.SysBillID
and SM.billsysstate not in ('5')
--and sm.createdt >= @bgndt
--and sm.createdt <= @enddt
LEFT JOIN PUBCustomer JOIN_CustID ON JOIN_CustID.CustomerID = SM.CustID
left JOIN PUBEnterprise PEp ON PEp.EnterpriseID=JOIN_CustID.CustomerID
LEFT JOIN PUBArea PA ON PA.AreaCode=PEp.AreaCode
LEFT JOIN PUBArea PA2 ON PEp.AreaCode like PA2.AreaCode + '%' and PA2.AreaCode like '____'join pubfiscalperiod pfp on 1=1
and datediff(day,pfp.bgndt,sm.outstockdt)>0
and datediff(day,pfp.enddt,sm.outstockdt)<0
LEFT JOIN pubclassitems PCI on pci.itemno = sd.itemnoleft join pubitemclass ic on ic.classid = pci.classidleft join pubitemmaster pim on pci.itemno = pim.itemnoLEFT JOIN PUBItemClass PIC ON PCI.ClassID
LIKE PIC.ClassID + '%'
where sm.billsysstate in ('2','6')
--and sm.createdt >= @bgndt
--and sm.createdt <= @enddt
and pic.classid like '00102001%'
--or pic.classid like '00102001__'
or pic.classid like '00102002%'
or pic.classid like '00102003%'
or pic.classid like '00102004%'
or pic.classid like '00102005%'
or pic.classid like '00102006%'
or pic.classid like '00103001%'
or pic.classid like '00102009%') Sal_
/*
where CustID like @Customerid--{$IFDEF P_AreaID}
AND AreaCode like @AreaID + '%'
--{$ENDIF P_AreaID}
*/group by --Sal_.userbillid ,
--sal_.itemno,
sal_.classnamedetail,
Sal_.DaAreaName,
Sal_.AreaName,
sal_.classid,
sal_.classname,
sal_.userdef2,
Sal_.Custid,Sal_.SimpleName,
sal_.outstockdt,
sal_.yewuqi
--Sal_. Re,
--Sal_.nianyue
--Sal_.createdt
--order by custid
) tabinfo2 group by --tabinfo2.itemno,
tabinfo2.classid,tabinfo2.classname,tabinfo2.classnamedetail,
tabinfo2.daareaname,tabinfo2.areaname,tabinfo2.simplename,tabinfo2.userdef2
--,tabinfo2.outstockdt,tabinfo2.outstockdt,tabinfo2.yewuqi--,tabinfo2.fahuoje
这个是关于某客户 2008-7月 到2009-04 月的 发货金额的总和,交叉表最后得出的结果大概是这个样子:
详细分类 省区 地区 客户 2008-07 2008-08 ........
int 金额 金额
int
惠步舒
.
.
.
哪个管库存的女人说,现在需要把金额分出来,因为这个统计把退货,和进货的都加一起了,现在要分开来,
实际情况是,退货也是用发货单salsendbillmaster 就是SM, 只不过数量和价税合计用负的.没特别的单据类型.俺的意见是,做一个参数,你爱咋咋的.她说不行,要做在一张表里.因为有的客户是没有退货的.那我说前后2组时间了,
前面的进货,后面的退.她说要对齐,没退货的就让他空着.请教高手指教.
即然退货都用负数表示你就sum(case when 数量<0 then 数量 else null end) as 退货数
不就得了。
结果类似与 1 正
1 负
....
我要的是 在一行 1正 1负 如果没有负的,那么是0.感谢15楼tengjian1981的意见.