SQL_作业统计报表

PROCEDURE upWORKPLACE @empid VARCHAR(30), @empname VARCHAR(30), @depart VARCHAR(30), @beginDate DATETIME, @endDate DATETIME, @loginid VARCHAR(20) AS BEGIN DECLARE @SQL0 VARCHAR(8000),@SQL1 VARCHAR(8000) ,@SQL2 VARCHAR(8000),@SQL3 VARCHAR(max) ,@sql VARCHAR(8000) ,@countSql VARCHAR(8000) ,@charenter VARCHAR(10) SET @charenter=CHAR(10)+CHAR(13) /*1.获取筛选部门后的数据*/ SET @SQL0=‘ IF OBJECT_ID(‘‘tempdb..#tmpemps20170901‘‘) IS NOT NULL DROP TABLE #tmpemps20170901 SELECT e.empid,e.empname,e.departmentdesc,e.[job],e.infactory,e.outfactory,e.leaveflag INTO #tmpemps20170901 FROM Employees AS e (NOLOCK) WHERE e.short_number=‘‘RD‘‘ AND e.departmentdesc like ‘‘%WORKDEPT%‘‘ ‘ IF @empid IS NOT NULL SET @SQL0=RTRIM(@SQL0)+ ‘ and e.empid like ‘+@empid +@charenter IF @empname IS NOT NULL SET @SQL0=RTRIM(@SQL0)+ ‘ and e.empname like ‘+@empname +@charenter IF @depart IS NOT NULL SET @SQL0=RTRIM(@SQL0)+‘ AND e.departmentdesc like ‘+@depart +@charenter /*3.获取小配件数据 INTO #tmpsmallsparts20170901 */ SET @SQL2=‘ IF OBJECT_ID(‘‘tempdb..#tmpsmallsparts20170901‘‘) IS NOT NULL DROP TABLE #tmpsmallsparts20170901 SELECT ‘‘1‘‘ AS sn,a.workerID,a.sc_number,a.mbtypeid,‘‘‘‘ AS mbversion,‘‘‘‘ AS nwjian,a.processID,a.effectiveDate,a.uPrice,a.multiple ,SUM(a.qty)OVER(PARTITION BY a.workerID,a.sc_number,a.mbtypeid,a.processID,a.effectiveDate,a.uPrice,a.multiple) AS qty ,SUM(a.pay)OVER(PARTITION BY a.workerID,a.sc_number,a.mbtypeid,a.processID,a.effectiveDate,a.uPrice,a.multiple) as partsPay INTO #tmpsmallsparts20170901 from ( select a.workerID,a.matioid AS sc_number,a.sc_number AS mbtypeid,a.processID,a.processDesc,a.effectiveDate,a.uPrice,a.multiple,a.qty ,cast(ISNULL(a.uPrice,0)*ISNULL(a.multiple,1)*ISNULL(a.qty,0) as numeric(18,4)) as pay from (select case when a.pricetype=‘‘P‘‘ then 2 else 3 end as sn ,a.workerID,d.empname ,case when a.priceType =‘‘P‘‘ then ‘‘计件‘‘ else ‘‘计时‘‘ end as sc_number ,a.styleno as matioid ,CONVERT(varchar(10),a.effectiveDate,120) as effectiveDate ,a.sortType as processID ,a.sortName as processDesc ,a.qty, case when a.sortType=‘‘TT002‘‘ then a.bgprice else b.price end as uPrice ,b.multiple ,a.remark from tab_sortSalary(nolock) a left join tab_smallPartsSort(nolock) b on b.sortType=a.sortType left join tab_timeSort(nolock) c on c.costLevel=a.sortType left join (SELECT * FROM Employees AS e WHERE e.short_number=‘‘RD‘‘)d on d.empid=a.workerID ) a where 1=1 ‘ IF @empid IS NOT NULL SET @SQL2=RTRIM(@SQL2)+ ‘ and a.workerID like ‘+@empid +@charenter IF @beginDate IS NOT NULL SET @SQL2=RTRIM(@SQL2)+ ‘ and a.effectiveDate >=‘‘‘+CONVERT(varchar(10),@beginDate,120)+‘‘‘ ‘ +@charenter IF @endDate IS NOT NULL SET @SQL2=RTRIM(@SQL2)+ ‘ and a.effectiveDate <=‘‘‘+CONVERT(VARCHAR(10),@endDate,120)+‘‘‘ ‘ +@charenter SET @SQL2=RTRIM(@SQL2)+ ‘ )a WHERE 1=1 ‘ /*4.获取计件作业数据 INTO #tmpsalarypay20170901*/ SET @SQL3=‘ IF OBJECT_ID(‘‘tempdb..#tmpsalarypay20170901‘‘) IS NOT NULL DROP TABLE #tmpsalarypay20170901 SELECT ‘‘0‘‘ AS sn,a.workerID,a.sc_number,a.mbtypeid,a.mbversion,a.nwJian,a.processID,a.jieDanDate,e0.unitPrice,a.rate ,SUM(a.qty)OVER(PARTITION BY a.workerID,a.sc_number,a.mbtypeid,a.mbversion,a.nwJian,a.processID,a.jieDanDate,e0.unitPrice,a.rate) AS qty ,SUM(a.qty)OVER(PARTITION BY a.workerID,a.sc_number,a.mbtypeid,a.mbversion,a.nwJian,a.processID,a.jieDanDate,e0.unitPrice,a.rate)* ISNULL(e0.unitPrice,0) * ISNULL(a.rate,0) AS gujiaPay INTO #tmpsalarypay20170901 from ( select a.workerID,a.processid,a.sc_number,a.mbtypeid,a.mbversion,CONVERT(varchar(10),a.jieDanDate,120) AS jieDanDate ,a.qty,a.nwJian,a.[size],a.updateFlag,a.payMoney ,case when a.processID=‘‘CJ001‘‘ then (select rate from tab_processRate(nolock) where processID=a.processID and mbtypeid=a.mbtypeid) else (select isnull(rate,1) from tab_processRate(nolock) where processID=a.processID) end as rate from tab_CheFJob(nolock) a where 1=1 ‘ IF @empid IS NOT NULL SET @SQL3=RTRIM(@SQL3)+ ‘ and a.workerID like ‘+@empid +@charenter IF @beginDate IS NOT NULL SET @SQL3=RTRIM(@SQL3)+ ‘ and a.jieDanDate >=‘‘‘+CONVERT(VARCHAR(10),@beginDate,120)+‘‘‘‘ +@charenter IF @endDate IS NOT NULL SET @SQL3=RTRIM(@SQL3)+ ‘ and a.jieDanDate <=‘‘‘+CONVERT(VARCHAR(10),@endDate,120)+‘‘‘‘ +@charenter SET @sql3=RTRIM(@SQL3)+ ‘ )a left join (select a.typeID,a.sc_number,a.mbVersion ,b.processID,b.unitPrice,a.aroundType ,RTRIM(a.sc_number+a.mbVersion+a.aroundType) as cc from (SELECT sc_number,mbVersion,isnull(aroundType,‘‘‘‘)AS aroundType,typeID,MAX(sn) AS sn FROM tab_banValuation_head where typeID=‘‘A‘‘ GROUP BY sc_number,mbVersion,isnull(aroundType,‘‘‘‘),typeID ) a inner join tab_banValuation_detail(nolock) b on b.sn=a.sn where 1=1 and a.typeID=‘‘A‘‘ and not exists ( select distinct rtrim(a1.sc_number+a1.mbVersion+a1.aroundType) as cc from (SELECT sc_number,mbVersion,isnull(aroundType,‘‘‘‘)AS aroundType,typeID,MAX(sn) AS sn FROM tab_banValuation_head where typeID=‘‘B‘‘ GROUP BY sc_number,mbVersion,isnull(aroundType,‘‘‘‘),typeID) a1 inner join tab_banValuation_detail(nolock) b1 on b1.sn=a1.sn where 1=1 and a1.typeID=‘‘B‘‘ and ISNULL(b1.unitPrice,0) <> 0 and RTRIM(a.sc_number+a.mbVersion+a.aroundType)=rtrim(a1.sc_number+a1.mbVersion+a1.aroundType) ) )e on e.sc_number=a.sc_number and e.mbVersion=a.mbversion AND isnull(e.aroundType,‘‘‘‘)=ISNULL(a.nwjian,‘‘‘‘) and e.processID=a.processID left join ( select a.typeID,a.sc_number,a.mbVersion ,b.processID,b.unitPrice,a.aroundType from (SELECT sc_number,mbVersion,isnull(aroundType,‘‘‘‘)AS aroundType,typeID,MAX(sn) AS sn FROM tab_banValuation_head where typeID=‘‘B‘‘ GROUP BY sc_number,mbVersion,isnull(aroundType,‘‘‘‘),typeID) a inner join tab_banValuation_detail(nolock) b on b.sn=a.sn where 1=1 and a.typeID=‘‘B‘‘ )e0 on e0.sc_number=a.sc_number and e0.mbVersion=a.mbversion and ISNULL(e0.aroundType,‘‘‘‘)=ISNULL(a.nwJian,‘‘‘‘) and e0.processID=a.processID where 1=1 ‘ /*5.统计 INTO #tmpcount20170901*/ SET @sql=‘ IF OBJECT_ID(‘‘tempdb..#tmpcount20170901‘‘) IS NOT NULL DROP TABLE #tmpcount20170901 select a.empid,a.empname,a.departmentdesc,a.[job],a.infactory,a.outfactory,a.leaveflag ,b.workerID,b.sc_number,b.mbtypeid,b.mbversion,b.nwjian,b.processID,b.jieDanDate,b.unitPrice,b.rate,b.qty,b.gujiaPay,b.sn INTO #tmpcount20170901 from #tmpemps20170901 as a left join ( select * from #tmpsalarypay20170901 union select * from #tmpsmallsparts20170901 ) b ON b.workerID=a.empid WHERE a.leaveflag=0 ‘ SET @countSql=‘ DROP TABLE #tmpemps20170901 DROP TABLE #tmpsmallsparts20170901 DROP TABLE #tmpsalarypay20170901 SELECT empid,empname,departmentdesc,[job],infactory,outfactory,leaveflag ,workerID,a.sc_number,a.mbtypeid,a.mbversion,a.nwjian,a.processID,jieDanDate,a.unitPrice,a.rate,a.qty ,cast(gujiaPay as numeric(16,2)) as gujiaPay,sn ,b.processDesc ,case when sn=0 then c.mbtypename else a.mbtypeid end mbtypename ,case when sn=0 then d.banid else a.sc_number end as banid FROM #tmpcount20170901 a left join ( SELECT DISTINCT processID,processDesc FROM tab_jobProcess(NOLOCK) UNION SELECT DISTINCT sortType,sortDesc FROM tab_smallPartsSort (NOLOCK) )b on b.processID=a.processID left join b_mbtype (nolock)c on c.mbtypeid=a.mbtypeid left join b_styleno (nolock)d on d.sc_number=a.sc_number WHERE 1=1 order by sn asc,empid,jieDanDate ‘ PRINT @SQL0 print @SQL1 print @SQL2 PRINT @SQL3 print @sql EXEC (@SQL0+@SQL1+@SQL2+@SQL3+@sql+@countSql) END

2.　FastReport报表设计

 

SQL_作业统计报表

标签：effective   where   报表   evel   rtm   money   lock   src   部门