34,838
社区成员




select [金额],0,[金额],month([月份]) from Offer where year([月份]) = @year
create procedure CalMoney
@year smallint
as
set nocount on
;with a as
(
select nm,sum(tMoney) tMoney,sum(oMoney) oMoney,m
from
(
select [销售人] nm,[金额] tMoney,0 oMoney,month([月份]) m from Total where year([月份]) = @year
union all
select [金额],0,[金额],month([月份]) from Offer where year([月份]) = @year
)t
group by nm, m
)
select
n.nm [销售人]
,isnull(a.tMoney,0) [Total.金额]
,isnull(a.oMoney,0) [Offer.金额]
,sp.number [月份]
from (select distinct a.nm from a ) n
inner join master..spt_values sp
on sp.type = 'P'
and sp.number >=1
and sp.number <= 12
left join a
on n.nm = a.nm
and sp.number = a.m
set nocount off
create procedure CalMoney
@year smallint
as
set nocount on
;with a as
(
select nm,sum(tMoney) tMoney,sum(oMoney) oMoney,m
from
(
select [人] nm,[金] tMoney,0 oMoney,month(日期) m from Total where year(日期) = @year
union all
select 人,0,金,month(日期) from Offer where year(日期) = @year
)t
group by nm, m
)
select
n.nm
,isnull(a.tMoney,0) tMoney
,isnull(a.oMoney,0) oMoney
,sp.number
from (select distinct a.nm from a ) n
inner join master..spt_values sp
on sp.type = 'P'
and sp.number >=1
and sp.number <= 12
left join a
on n.nm = a.nm
and sp.number = a.m
set nocount off
select number from master..spt_values where type='p' and Number between 1 and 12
select * from
(select 销售人,sum(金额) as 金额 from [Total] group by 销售人 )a
full join
(select 销售人,sum(金额) as 金额,month(日期) as 日期 from [Total] group by 销售人,month(日期) )b
on
a.销售人=b.销售人
full join
(select number from master.dbo.spt_values where type = 'p' and number between 1 and 12)c
on
b.日期=c.number
order by
c.number
select 销售人,sum(t.金额) Total.金额,sum(o.金额) Offer.金额,month(t.日期)
from total as t,Offer as o
where t.销售人=o.销售人
group by t.销售人,month(t.日期)
order by t.销售人,month(t.日期)