sqlserver递归统计

—获取当前及以下部门
Create proc GetCurrentAndUnderOrg
@orgId int
as
begin
WITH cte
AS
(
SELECT ,0 AS level FROM Static_Organ WHERE OrganID=@orgId —pCateNo not in (select cateno from partscate)
UNION ALL
SELECT g.,level+1 FROM Static_Organ g INNER JOIN cte
ON g.ParentOrgan=cte.OrganID
)
SELECT * FROM cte
end

—向上递归
WITH cte
AS
(
SELECT ,LevelIndex AS level FROM EL_Departments WHERE ID=170
UNION ALL
SELECT g.,g.LevelIndex AS level FROM EL_Departments g INNER JOIN cte
ON g.Id=cte.ParentId
)

//============================================================
/
车辆运费明细
declare @total int
declare @columnheads varchar(max)
declare @sumprofit numeric(18,2)
declare @summoney numeric(18,2)
declare @SalaryCredit numeric(18,2)
exec [sp_StatMonthDispatchList] 0,1000,’’,’’,’’,’二组冀A123452’,’Y00026’,’’,’’,’2020-04-29’,’2020-08-31’,
@total out,
@columnheads out,
@sumprofit out,
@summoney out,
@SalaryCredit out
print @total
print @columnheads
/
ALTER procedure [dbo].sp_StatMonthDispatchList,
@sortorderby VARCHAR(50),
@cargroup VARCHAR(50),
@carno VARCHAR(50),
@wayno VARCHAR(50),
@drivername VARCHAR(50),
@drivertel VARCHAR(50),
@begdate VARCHAR(30),
@enddate VARCHAR(30),
@total INT out,
@columnheads VARCHAR(max) out,
@sumprofit numeric(18,2) output,
@summoney numeric(18,2) output,
@SalaryCredit 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)
BEGIN
IF(@begdate = ‘null’ OR isnull(@begdate,’’)=’’ ) SET @begdate = ‘’
IF(@enddate = ‘null’ OR isnull(@enddate,’’)=’’) SET @enddate = ‘’
SET @tmpbegdate = @begdate
SET @tmpenddate = @enddate + ‘ 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

IF(isnull(@sortcolumn,’’)=’’) SET @sortcolumn = ‘confirmdate’
set @sql = ‘
WITH tmpJob AS
(
SELECT isnull(d.carno,’’’’) AS carno,d.detailid,d.jobno,confirmdate,drivername,drivertel,wayno
FROM JobDetailbill d INNER JOIN jobmasterbill m ON d.JobNo = m.JobNo
left join driverinfo dr on d.driverno1 = dr.driverno
where 1=1
union all
SELECT isnull(d.carno,’’’’) AS carno,d.rid,d.jobno,confirmdate,drivername,drivertel,wayno
FROM JobDetail d INNER JOIN jobmaster m ON d.JobNo = m.JobNo
left join driverinfo dr on d.driverno1 = dr.driverno
where 1=1
)
select into #jobdetail from tmpJob where 1=1’
IF(@begdate != ‘’)
BEGIN
set @sql = @sql + ‘ and confirmdate >= ‘’’+@tmpbegdate+’’’’
END
IF(@enddate != ‘’)
BEGIN
set @sql = @sql + ‘ and confirmdate <= ‘’’+@tmpenddate+’’’’
END
IF(@carno != ‘’)
BEGIN
set @sql = @sql + ‘ and carno like ‘’%’+@carno+’%’’’
END
IF(@cargroup != ‘’)
BEGIN
set @sql = @sql + ‘ and carno like ‘’%’+@cargroup+’%’’’
END
IF(@wayno != ‘’)
BEGIN
set @sql = @sql + ‘ and wayno = ‘’’+@wayno+’’’’
end
IF(@drivername != ‘’)
BEGIN
set @sql = @sql + ‘ and drivername like ‘’%’+@drivername+’%’’’
END
IF(@drivertel != ‘’)
BEGIN
set @sql = @sql + ‘ and DriverTel like ‘’%’+@drivertel+’%’’’
END
set @sql = @sql + ‘;’
—车辆司机工资
SET @sql = @sql +’
WITH tmpstattotal AS
(
SELECT d.DetailId as detailid,isnull(d.jobno,’’’’) as jobno,d.carruntime,d.confirmusername,d.confirmdate,d.carfinishdate,d.reviewdateafterconfirm,isnull(m.wayno,’’’’) as wayno,isnull(w.wayname,’’’’) as wayname,isnull(d.carno,’’’’) AS carno,m.CarNum as num,isnull(m.finishdate,’’’’) as finishdate,isnull(s.shipcompname,’’’’) as shipcompname,
ISNULL(d.LoadWeight,0.0) as loadweight,ISNULL(d.UnLoadWeight,0.0) as unloadweight,ISNULL(d.LoadWeight,0.0)-ISNULL(d.UnLoadWeight,0.0) as kuitonnage,
isnull(receplatdebit,0) as listmoney,isnull(charge.ChargeMoney,0) as chargemoney,isnull(receplatdebit-isnull(charge.ChargeMoney,0)-salarycredit,0) profit,
SalaryCredit,drivername,drivertel

FROM JobDetailbill d INNER JOIN jobmasterbill m ON d.JobNo = m.JobNo
left join driverinfo dr on d.driverno1 = dr.driverno
left join ShipCompany s on m.ShipCompNo=s.ShipCompNo
left join WaySet w on m.WayNo=w.WayNo
left join (select sum(chargemoney) as chargemoney,carno,jobno,JobDetailId from ChargeDetail d,chargemaster m where m.chargeno=d.chargeno and liststate >=0 group by carno,jobno,JobDetailId) as charge on charge.carno=d.CarNo and charge.jobno=d.JobNo and charge.JobDetailId=d.DetailId
where 1=1
union all
SELECT d.rid as detailid,isnull(d.jobno,’’’’) as jobno,d.carruntime,d.confirmusername,d.confirmdate,d.carfinishdate,d.reviewdateafterconfirm,isnull(m.wayno,’’’’) as wayno,isnull(w.wayname,’’’’) as wayname,isnull(d.carno,’’’’) AS carno,m.CarNum as num,isnull(m.finishdate,’’’’) as finishdate,isnull(s.shipcompname,’’’’) as shipcompname,
ISNULL(d.LoadWeight,0.0) as loadweight,ISNULL(d.UnLoadWeight,0.0) as unloadweight,ISNULL(d.LoadWeight,0.0)-ISNULL(d.UnLoadWeight,0.0) as kuitonnage,
isnull(receplatdebit,0) as listmoney,isnull(charge.ChargeMoney,0) as chargemoney,isnull(receplatdebit-isnull(charge.ChargeMoney,0)-salarycredit,0) profit ,
SalaryCredit,drivername,drivertel
FROM JobDetail d INNER JOIN jobmaster m ON d.JobNo = m.JobNo
left join driverinfo dr on d.driverno1 = dr.driverno
left join ShipCompany s on m.ShipCompNo=s.ShipCompNo
left join WaySet w on m.WayNo=w.WayNo
left join (select sum(chargemoney) as chargemoney,carno,jobno,JobDetailId from ChargeDetail d,chargemaster m where m.chargeno=d.chargeno and liststate >=0 group by carno,jobno,JobDetailId) as charge on charge.carno=d.CarNo and charge.jobno=d.JobNo and charge.JobDetailId=d.rid
where 1=1
)
select into #stattotal from tmpstattotal where 1=1’
IF(@begdate != ‘’)
BEGIN
set @sql = @sql + ‘ and confirmdate >= ‘’’+@tmpbegdate+’’’’
END
IF(@enddate != ‘’)
BEGIN
set @sql = @sql + ‘ and confirmdate <= ‘’’+@tmpenddate+’’’’
END
IF(@cargroup != ‘’)
BEGIN
set @sql = @sql + ‘ and carno like ‘’%’+@cargroup+’%’’’
END
IF(@carno != ‘’)
BEGIN
set @sql = @sql + ‘ and carno like ‘’%’+@carno+’%’’’
END
IF(@wayno != ‘’)
BEGIN
set @sql = @sql + ‘ and wayno = ‘’’+@wayno+’’’’
END
IF(@drivername != ‘’)
BEGIN
set @sql = @sql + ‘ and drivername like ‘’%’+@drivername+’%’’’
END
IF(@drivertel != ‘’)
BEGIN
set @sql = @sql + ‘ and DriverTel like ‘’%’+@drivertel+’%’’’
END
set @sql = @sql + ‘;’


—-司机费用
SET @sql = @sql + ‘
select jobno,jobdetailid,’+@columns+’ into #charge from (SELECT d.jobno,d.jobdetailid,isnull(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
where m.liststate>=0 ) t
PIVOT( sum(chargemoney) for kindno IN (‘+@kindnos+’) ) a ‘

—分页
SET @sql = @sql + ‘;with tmpFirst as ( select a.jobno,a.carno,a.profit,a.listmoney,a.salarycredit,a.drivername,a.drivertel,convert(varchar(20),a.confirmdate,120) as confirmdate,a.wayname,’+@columnnames+’ from #stattotal a left join #charge b on a.jobno=b.jobno and a.detailid = b.jobdetailid )’
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)
—合计
SET @sql = @sql + ‘select @total = count(1),@sumprofit = sum(profit),@summoney = sum(listmoney),@SalaryCredit = sum(SalaryCredit) from #stattotal a left join #charge b on a.jobno=b.jobno and a.detailid = b.jobdetailid ‘
PRINT @sql
EXEC sp_executesql @sql ,N’@total int out,
@sumprofit numeric(18,2) output,
@summoney numeric(18,2) output,
@SalaryCredit numeric(18,2) output’,
@total out,
@sumprofit output,
@summoney output,
@SalaryCredit output
RETURN 0
END