VStrSql :=' select *,seam+import+apply+changelist+provetest+sendlift+annex sumall from(' + //得分 ' select A.JOBNO,((case when (A.VESSEL is not null and A.VESSEL <> '''' AND A.MAWB_NO is not null and A.MAWB_NO <> '''') AND (A.HX_NO is not null and A.HX_NO <> '''') then 3 else 0 end)+(case when (A.ISBUSS =''N'') then 10 else 0 end)' + ' +(case when (A.WEITUO_NO is not null and A.WEITUO_NO <> '''') then 2 else 0 end)+(case when (A.CO_ID is not null and A.CO_ID <> '''' and A.CO_NM is not null and A.CO_NM <> '''' and A.PKGS is not null) then 5 else 0 end)) seam' + //接单 10 ' ,(case when (select count(*) from CUSDECL C where (A.HX_NO=C.PASS_NO or A.MAWB_NO=C.BL_NO))>0 then 10 else 0 end)+(case when len(A.DECL_NO)=9 then 5 else 0 end) import' + //报关单录入 20 ' ,(case when (select count(*) from TMP_BGWRK D where D.DCL_NO = A.DECL_NO)>0 and (select BG_WRK_TIME from TMP_BGWRK G where G.DCL_NO = A.DECL_NO) <> '''' then 20 ' + ' when (select count(*) from TMP_BGWRK H where H.DCL_NO = A.DECL_NO)>0 and (select BG_WRK_TIME from TMP_BGWRK G where G.DCL_NO = A.DECL_NO) is null then 10 else 0 end) apply' + //报关作业 20 ' ,(case when (select count(*) from IMPGTBILL E where E.JOB_NO = A.JOBNO)>0 then 10 else 0 end) changelist' + //换单作业 10 ' ,(case when (select count(*) from TMP_IMPTXWRK F where F.DCL_NO = A.DECL_NO)>0 then 10 else 0 end) provetest ' + //报关证明联作业 10 ' ,(case when A.AC_GOODS_DATE <> '''' then 20 else 0 end) sendlift '+ //送提货信息 20 ' ,(case when (A.ISBUSS =''Y'')and(select count(*) from EVERIFY H where H.VRJOB_NO = A.JOBNO)>0 then 10 else 0 end) annex' + //附加 10 ' from IEJOB A where A.JOB_TYPE = ''I'' and A.OP_USER =''' + APerson +''' and convert(char(10),A.IN_TIME,102) = ' + ' ''' + Formatdatetime('yyyy.mm.dd',DPTdate.date) + ''') B order by B.JOBNO';
老师查询同学的成绩
VStrSql :=' select Z.OP_USER,count(*) notecount,' + ' sum(sumall) sumcount,convert(decimal(18,2),sum(sumall)/cast(count(*)as float)) result from (' + ' select T.OP_USER,seam+import+apply+drawback+pigeonhole+annex+sendlift+changelist sumall from(' + //得分 ' select A.OP_USER,((case when (A.VESSEL is not null and A.VESSEL <> '''' AND A.MAWB_NO is not null and A.MAWB_NO <> '''') AND (A.HX_NO is not null and A.HX_NO <> '''') then 3 else 0 end)+(case when (A.ISBUSS =''N'') then 10 else 0 end)' + ' +(case when (A.WEITUO_NO is not null and A.WEITUO_NO <> '''') then 2 else 0 end)+(case when (A.CO_ID is not null and A.CO_ID <> '''' and A.CO_NM is not null and A.CO_NM <> '''' and A.PKGS is not null) then 5 else 0 end)) seam' + //接单 10 ' ,(case when (select count(*) from CUSDECL C where (A.HX_NO=C.PASS_NO or A.MAWB_NO=C.BL_NO))>0 then 10 else 0 end)+(case when len(A.DECL_NO)=9 then 5 else 0 end) import' + //报关单录入 20 ' ,(case when (select count(*) from TMP_BGWRK D where D.DCL_NO = A.DECL_NO)>0 and (select BG_WRK_TIME from TMP_BGWRK G where G.DCL_NO = A.DECL_NO) <> '''' then 20 ' + ' when (select count(*) from TMP_BGWRK H where H.DCL_NO = A.DECL_NO)>0 and (select BG_WRK_TIME from TMP_BGWRK G where G.DCL_NO = A.DECL_NO) is null then 10 else 0 end) apply' + //报关作业 20 ' ,(case when (select count(*) from TMP_IMPTXWRK F where F.DCL_NO = A.DECL_NO)>0 then 10 else 0 end) drawback' + //出口:退税作业 10 进口:报关证明联作业 10 ' ,(case when (A.JOB_TYPE = ''E'') and (select count(*) from TMP_TXWRK E where E.DCL_NO = A.DECL_NO and E.BK_NO<> '''')>0 then 20 else 0 end) pigeonhole' + //出口:归档作业 20 ' ,(case when (A.ISBUSS =''Y'')and(select count(*) from EVERIFY H where H.VRJOB_NO = A.JOBNO)>0 then 10 else 0 end) annex' + //附加 10 ' ,(case when (A.JOB_TYPE = ''I'') and (A.AC_GOODS_DATE <> '''') then 20 else 0 end) sendlift '+ //进口:送提货信息 20 ' ,(case when (A.JOB_TYPE = ''I'')and (select count(*) from IMPGTBILL M where M.JOB_NO = A.JOBNO)>0 then 10 else 0 end) changelist' + //进口:换单作业 10 ' from IEJOB A where '; if cboType.ItemIndex =1then VStrSql := VStrSql +'A.JOB_TYPE = ''I'' and ' elseif cboType.ItemIndex =2then VStrSql := VStrSql +'A.JOB_TYPE = ''E'' and '; VStrSql := VStrSql +' convert(char(10),A.IN_TIME,102) = ''' + Formatdatetime('yyyy.mm.dd',DPTdate.date) + ''') T' + ' ) Z' + ' group by Z.OP_USER';