sql存储过程动态列行列转换统计递归

/** Object: StoredProcedure [dbo].[Sp_StatSingleLineProfit] Script Date: 12/12/2020 15:54:01 **/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/
单趟毛利统计
declare @total int
declare @columnheads varchar(max)
declare @receplatdebit numeric(18,2)
declare @chargemoney numeric(18,2)
declare @salaryway numeric(18,2)
declare @profit numeric(18,2)
exec [Sp_StatSingleLineProfit] 0,100,’’,’asc’,’2020-11-01’,’2020-12-01’,2,’PG2012010031’,’’,
@total out,
@columnheads out,
@receplatdebit output,
@chargemoney output,
@salaryway output,
@profit output
print @total
print @columnheads
print @receplatdebit
print @chargemoney
print @salaryway
print @profit
/
alter procedure [dbo].Sp_StatSingleLineProfit,
@sortorderby VARCHAR(50),
@begdate VARCHAR(30),
@enddate VARCHAR(30),
@kind int, —1:每趟汇总 2:每趟明明细
@rootjobno VARCHAR(50),
@wayname VARCHAR(50),
@total INT out,
@columnheads VARCHAR(max) out,
@receplatdebit numeric(18,2) output,
@chargemoney numeric(18,2) output,
@salaryway numeric(18,2) output,
@profit numeric(18,2) output
)
AS
DECLARE @sql NVARCHAR(max)
DECLARE @tmpbegdate VARCHAR(50)
DECLARE @tmpenddate VARCHAR(50)
DECLARE @kindnames NVARCHAR(MAX)
DECLARE @kindnos NVARCHAR(MAX)
DECLARE @columns NVARCHAR(MAX)
DECLARE @columnnames NVARCHAR(MAX)
DECLARE @addcolumnnames NVARCHAR(MAX)
DECLARE @updatecolumnnames NVARCHAR(MAX)
DECLARE @sumcolumnnames NVARCHAR(MAX)
BEGIN

IF(@begdate = ‘null’ OR isnull(@begdate,’’)=’’ ) SET @begdate = ‘’
IF(@enddate = ‘null’ OR isnull(@enddate,’’)=’’) SET @enddate = ‘’
—时间各前后放宽一周
SET @tmpbegdate = CONVERT(varchar(10), dateadd(day,-7,@begdate),120 )
SET @tmpenddate = CONVERT(varchar(10),dateadd(day,7,@enddate),120) + ‘ 23:59:59’
SELECT @kindnames=STUFF((SELECT ‘,’+kindname FROM chargekind FOR XML PATH(‘’)) ,1,1,’’ )
set @columnheads = @kindnames
PRINT @kindnames
SELECT @kindnos=STUFF((SELECT ‘,’+kindno FROM chargekind FOR XML PATH(‘’)) ,1,1,’’ )
PRINT @kindnos
SELECT @columns=STUFF((SELECT ‘,isnull(‘+kindno+’,0) as ‘+kindname FROM chargekind FOR XML PATH(‘’)),1,1,’’ )
PRINT @columns
SELECT @columnnames=STUFF((SELECT ‘,isnull(‘+kindname+’,0) as ‘+kindname FROM chargekind FOR XML PATH(‘’)),1,1,’’ )
PRINT @columnnames
SELECT @addcolumnnames=STUFF((SELECT ‘,’+kindname+’ numeric(18,2) not null default 0 ‘ FROM chargekind FOR XML PATH(‘’)),1,1,’’ )
PRINT @addcolumnnames
SELECT @updatecolumnnames=STUFF((SELECT ‘,’+kindname+’=b.’+kindname FROM chargekind FOR XML PATH(‘’)),1,1,’’ )
PRINT @updatecolumnnames
SELECT @sumcolumnnames=STUFF((SELECT ‘,sum(‘+kindname+’) as ‘+kindname FROM chargekind FOR XML PATH(‘’)),1,1,’’ )
PRINT @sumcolumnnames

IF(isnull(@sortcolumn,’’)=’’) SET @sortcolumn = ‘waypath’
create table #jobdetail(jobno varchar(100) primary key,detailid int ,carno varchar(100),
fromjobno varchar(50) ,wayname varchar(100),receplatdebit numeric(18,2),chargemoney numeric(18,2),
salaryway numeric(18,2) ,profit numeric(18,2) ,
jobpath varchar(max) not null default ‘’ ,waypath varchar(max) not null default ‘’,
rootjobno varchar(50) not null default ‘’
) —派工单信息

set @sql = ‘insert into #jobdetail(carno,detailid,jobno,fromjobno,wayname,receplatdebit,chargemoney,salaryway)
SELECT isnull(d.carno,’’’’) AS carno,d.detailid,d.jobno,isnull(m.fromjobno,’’’’),isnull(ws.wayname,’’’’),receplatdebit,CostMoney,salaryway
FROM JobDetailbill d INNER JOIN jobmasterbill m ON d.JobNo = m.JobNo
inner join wayset ws on m.wayno = ws.wayno
where 1=1 and isgroupmanconfirm = 3 ‘
IF(@begdate != ‘’)
BEGIN
set @sql = @sql + ‘ and carfinishdate >= ‘’’+@tmpbegdate+’’’’
END
IF(@enddate != ‘’)
BEGIN
set @sql = @sql + ‘ and carfinishdate <= ‘’’+@tmpenddate+’’’’
END
print @sql
exec(@sql)
—更新总运费
/update
from select sum(chargemoney) as chargemoney,carno,jobno,JobDetailId from ChargeDetail d,chargemaster m where m.chargeno=d.chargeno and liststate = 2 and exists(select 1 from #init where jobno = d.jobno and detailid = d.JobDetailId
from #detail/

exec(‘alter table #jobdetail add ‘+@addcolumnnames)
—初始化每趟路线
select into #list from #jobdetail where isnull(fromjobno,’’) = ‘’ and jobno = case when ISNULL(@rootjobno,’’) = ‘’ then jobno else @rootjobno end

—-司机费用
SET @sql = ‘
select jobno,jobdetailid,’+@columns+’ into #charge from (SELECT d.jobno,d.jobdetailid,isnull(d.chargemoney,0) as chargemoney ,kindno FROM ChargeDetail d
inner join chargemaster m on d.chargeno = m.chargeno
inner join #jobdetail j on d.jobno = j.jobno and d.jobdetailid=j.detailid and d.carno = j.carno
left join chargekind k on d.chargekindno=k.kindno
/ left join PayDetail pd on pd.ListNo=d.ChargeNo and d.ChargeKindNo=’’F00010’’ /
where 1=1 / and m.liststate=2/ ) t
PIVOT( sum(chargemoney) for kindno IN (‘+@kindnos+’) ) a
/ select from #charge where jobno =’’PG2012010031’’ /
‘
exec(@sql)

—更新每单的费用
set @sql = @sql + ‘ update a set ‘+@updatecolumnnames+
‘ from #jobdetail a,#charge b where a.jobno=b.jobno and a.detailid = b.jobdetailid’
exec(@sql)
—select from #jobdetail
—select from #list
—查找每趟线路
;with tmpTable as
(
select jobno,isnull(fromjobno,’’) as fromjobno,jobno as rootjobno,
cast(jobno as nvarchar(max)) as jobpath,cast(wayname as nvarchar(max)) as waypath,
0 as level
from #jobdetail
where isnull(fromjobno,’’)=’’ and jobno = case when ISNULL(@rootjobno,’’) = ‘’ then jobno else @rootjobno end
union all
select c.jobno,isnull(c.fromjobno,’’) as fromjobno,p.rootjobno,
p.jobPath+’->’+c.jobno as jobpath,p.waypath+’->’+c.wayname as waypath,
p.level + 1 as level
from tmpTable P
inner join #jobdetail c
on p.jobno = isnull(c.fromjobno,’’)
)
—放入临时表，后面用2次
select into #tmpTable from tmpTable
—更新每个派工单初始路线
update a set rootjobno = b.rootjobno
from #jobdetail a, #tmpTable b
where a.jobno = b.jobno
—更新每趟完整路线
select a.rootjobno ,jobpath,waypath into #line from #tmpTable a,( select rootjobno,MAX(level) as level from #tmpTable group by rootjobno ) b
where a.rootjobno = b.rootjobno and a.level = b.level
update a set jobpath = b.jobpath,waypath = b.waypath
from #list a,#line b where a.jobno = b.rootjobno
if(@kind = 1) —每躺汇总
begin
—删除不是查询路线的记录
if(ISNULL(@wayname,’’)!=’’)
begin
delete from #list where waypath not like ‘%’+@wayname+’%’
end
—select from #tmpTable where rootjobno =’PG2012010031’
— select from #jobdetail where rootjobno =’PG2012010031’

—更新每趟线路的各项费用
set @sql = ‘
update a set receplatdebit = b.receplatdebit,chargemoney = b.chargemoney,salaryway = b.salaryway,’+@updatecolumnnames+
‘ from #list a,(select rootjobno ,SUM(receplatdebit) as receplatdebit,
SUM(chargemoney) as chargemoney,
SUM(salaryway) as salaryway, ‘+@sumcolumnnames +
‘ from #jobdetail group by rootjobno ) b
where a.jobno = b.rootjobno ‘
print @sql
exec(@sql)
update #list set profit = receplatdebit - chargemoney - salaryway
—分页
SET @sql = ‘;with tmpFirst as ( select from #list)’
SET @sql = @sql + ‘,tmpList AS(select ROW_NUMBER() OVER (ORDER BY ‘+@sortcolumn+’) AS ‘’RowNumber’’, from tmpfirst ‘+
‘ ‘+
‘) SELECT top ‘+convert(varchar,@limit)+’ FROM tmpList WHERE RowNumber > ‘+CONVERT(VARCHAR,@start)
exec(@sql)
select @total = count(1),@receplatdebit = sum(receplatdebit),
@chargemoney = sum(chargemoney),
@salaryway = sum(salaryway),
@profit = sum(profit)
from #list
end else —某趟明细
begin
update #jobdetail set profit = receplatdebit - chargemoney - salaryway
where rootjobno = @rootjobno
—分页
select from #jobdetail where rootjobno = @rootjobno
select @total = count(1),@receplatdebit = sum(receplatdebit),
@chargemoney = sum(chargemoney),
@salaryway = sum(salaryway),
@profit = sum(profit)
from #jobdetail
where rootjobno = @rootjobno
end

—合计
/SET @sql = @sql + ‘
select @total = count(1),
@receplatdebit = sum(receplatdebit),
@chargemoney = sum(chargemoney),
@salaryway = sum(salaryway),
@profit = sum(profit)
from #list
‘


PRINT @sql
print @columnnames
print @columnheads

EXEC sp_executesql @sql ,
N’@total int out,
@receplatdebit numeric(18,2) output,
@chargemoney numeric(18,2) output,
@salaryway numeric(18,2) output,
@profit numeric(18,2) output’,

@total out,
@receplatdebit out,
@chargemoney out,
@salaryway out,
@profit out

*/
RETURN 0

END