大神们！！！！！！怎么能把卡类类型，如图（金卡）合并以及相关的积分，积分损失金额，费用等数字合并。
这是查询语句declare @开始时间__datetime datetime,@结束时间__datetime datetime,@供应商编码 nvarchar(20),@卡类型 nvarchar(20),@机构 nvarchar(20)
set @开始时间__datetime='${sdate}'
set @结束时间__datetime='${edate}'
set @供应商编码='${gys}'
set @卡类型=''
set @机构='${jg}'
select a.c_amount as c_amount ,DATEPART(yy,a.c_dt)*100 + DATEPART(mm,a.c_dt) AS mm,a.c_fee_class ,
case when a.c_fee_name='白金卡损失'then '白金卡' when a.c_fee_name='金卡损失' then '金卡' else '天美卡' end as c_cardname,
case when len(a.c_id)=14 then SUBSTRING(a.c_id,3,5) else SUBSTRING(a.c_id,4,5) end as c_provider
into #a
from tb_o_feeg a(nolock)
where EXISTS(SELECT 1 FROM tb_o_fee(nolock) WHERE c_id=a.c_id AND c_status='已审核' )
and a.c_fee_class = '会员积分损失'
and ( @开始时间__datetime is null or datediff(day,@开始时间__datetime,a.c_dt )>=0)
and ( @结束时间__datetime is null or datediff(day,@结束时间__datetime,a.c_dt)<=0)
and (@供应商编码 is null or @供应商编码='' or case when len(a.c_id)=14 then SUBSTRING(a.c_id,3,5) else SUBSTRING(a.c_id,4,5) end=@供应商编码)
AND (@卡类型 is null or @卡类型='' OR CHARINDEX(@卡类型,a.c_fee_name)=0 )
SELECT SUM(c_amount) AS c_amount ,mm,c_cardname, c_provider INTO #b FROM #a GROUP BY mm,c_cardname, c_provider
SELECT a.c_store_id as 机构,a.c_provider as 供应商编码,
b.c_name as 供应商名称,
a.c_cardname as 卡类型,
DATEPART(yy,a.c_datetime) *100+ DATEPART(mm,a.c_datetime) mm,
SUM(a.c_amount_jf_sg) AS 积分,
cast(ROUND(SUM(a.c_amount_jf_sg)*0.01,2) as decimal(9,2)) AS 积分损失金额
INTO #c
from tb_provider_score_fee a(nolock)
join tb_partner b(nolock) on a.c_provider = b.c_no
WHERE
( @供应商编码 is null or @供应商编码='' or a.c_provider=@供应商编码)
and ( @卡类型 is null or @卡类型='' or a.c_cardname=@卡类型)
and (@开始时间__datetime is null or datediff(day,@开始时间__datetime,a.c_datetime)>=0)
and (@结束时间__datetime is null or datediff(day,@结束时间__datetime,a.c_datetime)<=0)
GROUP BY a.c_store_id,a.c_provider,a.c_cardname,b.c_name,DATEPART(yy,a.c_datetime) *100+ DATEPART(mm,a.c_datetime)
order by a.c_store_id,a.c_provider,DATEPART(yy,a.c_datetime) *100+ DATEPART(mm,a.c_datetime),a.c_cardname
/*----++++*/
SELECT #c.机构 as 机构,#c.供应商编码 as 供应商编码,
#c.供应商名称 as 供应商名称,
#c.卡类型 as 卡类型 ,
#c.mm AS 日期,
cast(#c.积分 as decimal(9,2) ) as 积分,
#c.积分损失金额 as 积分损失金额,
isnull(#b.c_amount,0) as 费用,
cast(#c.积分损失金额-isnull(#b.c_amount,0) as decimal(9,2) ) as 差额
FROM #c
LEFT JOIN #b ON #c.供应商编码= #b.c_provider AND #c.卡类型=#b.c_cardname AND #c.mm=#b.mm
where ( @机构 is null or @机构='8' or #c.机构=@机构)
ORDER BY #c.机构,#c.供应商编码,#c.mm,#c.卡类型
编辑于 2017-7-10 15:46
