统计各个商品每月的销售金额
-- 主表ORDR 其中单号docentry, 客户cardcode,日期docdate
-- 销售订单子表RDR1 其中单号docentry, 行号linenum,物料itemcode,数量quantity, 单价price
select t.cardcode '客户编码',t1.itemcode '物料编码',
isnull(convert(dec(18,2),sum(case month(t.docdate) WHEN '1' then t1.quantity * t1.price end)),0) '一月',
isnull(convert(dec(18,2),sum(case month(t.docdate) WHEN '2' then t1.quantity * t1.price end)),0) '二月',
isnull(convert(dec(18,2),sum(case month(t.docdate) WHEN '3' then t1.quantity * t1.price end)),0) '三月',
isnull(convert(dec(18,2),sum(case month(t.docdate) WHEN '4' then t1.quantity * t1.price end)),0) '四月',
isnull(convert(dec(18,2),sum(case month(t.docdate) WHEN '5' then t1.quantity * t1.price end)),0) '五月',
isnull(convert(dec(18,2),sum(case month(t.docdate) WHEN '6' then t1.quantity * t1.price end)),0) '六月',
isnull(convert(dec(18,2),sum(case month(t.docdate) WHEN '7' then t1.quantity * t1.price end)),0) '七月',
isnull(convert(dec(18,2),sum(case month(t.docdate) WHEN '8' then t1.quantity * t1.price end)),0) '八月',
isnull(convert(dec(18,2),sum(case month(t.docdate) WHEN '9' then t1.quantity * t1.price end)),0) '九月',
isnull(convert(dec(18,2),sum(case month(t.docdate) WHEN '10' then t1.quantity * t1.price end)),0) '十月',
isnull(convert(dec(18,2),sum(case month(t.docdate) WHEN '11' then t1.quantity * t1.price end)),0) '十一月',
isnull(convert(dec(18,2),sum(case month(t.docdate) WHEN '12' then t1.quantity * t1.price end)),0) '十二月'
from RDR1 t1
left join ORDR t on t.docentry = t1.docentry
group by t.cardcode,t1.itemcode
可参考:
;
with
cte_tot
as
(
select
PID,
sum
(
count
) totalmoney,
convert
(
varchar
(10),
month
(
date
))+
'月'
date
from
数据表
with
(nolock)
where
date
between
'2013-01-01'
and
'2013-08-15'
group
by
PID,
month
(
date
) )