--本店在其他店商品消费
select d.deptname, b.deptname deptnameM,sum(a.TotalAmt) Price into #a from dbo.CusOrderM a
left join basdept b on a.deptno=b.deptno
inner join Customer c on a.CustomerNo =c.CustomerNo
left join basdept d on c.deptno =d.deptno
where a.deptno<>'010101' and c.deptno='010101' and a.paytype=0 and a.IfFree=0 and a.docno
not in(select orderno from OrderReservation where a.docno=orderno) and a.DocDate>='2016-1-1' and a.DocDate<'2016-5-1'
group by d.deptname, b.deptname
--本店在其他店服务消费
select d.deptname, b.deptname deptnameM,sum(a.Price) Price into #b from dbo.OrderReservation a
left join basdept b on a.deptno=b.deptno
inner join Customer c on a.CustomerNo =c.CustomerNo
left join basdept d on c.deptno =d.deptno
where a.deptno<>'010101' and c.deptno='010101' and a.paytype=0 and a.IfFree=0 and a.PlaceTime>='2016-1-1' and a.PlaceTime<'2016-5-1'
group by d.deptname, b.deptname
--本店在其他店续卡
select d.deptname, b.deptname deptnameM,-sum(a.RechAmount) Price into #c from RechargeRecord a
left join basdept b on a.deptno=b.deptno
inner join Customer c on a.CustomerNo =c.CustomerNo
left join basdept d on c.deptno =d.deptno
where a.deptno<>'010101' and c.deptno='010101' and a.Rechtime>='2016-1-1' and a.Rechtime<'2016-5-1'
group by d.deptname, b.deptname
--其他店在本店商品消费
select d.deptname, b.deptname deptnameM,sum(a.TotalAmt) Price into #d from dbo.CusOrderM a
left join basdept b on a.deptno=b.deptno
inner join Customer c on a.CustomerNo =c.CustomerNo
left join basdept d on c.deptno =d.deptno
where a.deptno='010101' and c.deptno<>'010101' and a.paytype=0 and a.IfFree=0
and a.docno not in(select orderno from OrderReservation where a.docno=orderno) and a.DocDate>='2016-1-1' and a.DocDate<'2016-5-1'
group by d.deptname, b.deptname
--其他店在本店服务消费
select d.deptname, b.deptname deptnameM,sum(a.Price) Price into #e from dbo.OrderReservation a
left join basdept b on a.deptno=b.deptno
inner join Customer c on a.CustomerNo =c.CustomerNo
left join basdept d on c.deptno =d.deptno
where a.deptno='010101' and c.deptno<>'010101' and a.paytype=0 and a.IfFree=0 and a.PlaceTime>='2016-1-1' and a.PlaceTime<'2016-5-1'
group by d.deptname, b.deptname
--其他店在本店续卡
select d.deptname, b.deptname deptnameM,-sum(a.RechAmount) Price into #f from RechargeRecord a
left join basdept b on a.deptno=b.deptno
inner join Customer c on a.CustomerNo =c.CustomerNo
left join basdept d on c.deptno =d.deptno
where a.deptno='010101' and c.deptno<>'010101' and a.Rechtime>='2016-1-1' and a.Rechtime<'2016-5-1'
group by d.deptname, b.deptname
--取本店和其他店来往帐
select * into #dept from (
select #a.deptname,#a.deptnameM from #a
union
select #b.deptname,#b.deptnameM from #b
union
select #c.deptname,#c.deptnameM from #c
union
select #d.deptnameM,#d.deptname from #d
union
select #e.deptnameM,#e.deptname from #e
union
select #f.deptnameM,#f.deptname from #f) a
--取报表
select #dept.deptname '门店', #dept.deptnameM '其他门店',#a.Price '本店会员在其他门店消费产品',
#b.Price '本店会员在其他门店消费服务',#c.Price '本店会员在其他门店消费续卡',#a.Price+#b.Price+#c.Price '支出合计',
#d.Price '其他店会员在本店消费产品',#e.Price '其他店会员在本店消费服务',#f.Price '其他店会员在本店消费服务续卡',#d.Price+#e.Price+#f.Price '总收入'
from #dept
left join #a on #dept.deptname=#a.deptname and #dept.deptnameM=#a.deptnameM
left join #b on #dept.deptname=#b.deptname and #dept.deptnameM=#b.deptnameM
left join #c on #dept.deptname=#c.deptname and #dept.deptnameM=#c.deptnameM
left join #d on #dept.deptname=#d.deptnameM and #dept.deptnameM=#d.deptname
left join #e on #dept.deptname=#e.deptnameM and #dept.deptnameM=#e.deptname
left join #f on #dept.deptname=#f.deptnameM and #dept.deptnameM=#f.deptname
drop table #a,#b,#c,#d,#e,#f,#dept
