with t(num) as (select 1 union all select num + 1 from t where num < 96)--临时表 select ST.GCBH ,ST.WPMC AS 物料名称,ST.BLSL AS 不良数量 ,ST.pdbllx AS 判定不良类型,ST.BLXXMC AS 判定不良现象--不良现象名称 ,SUBSTRING(CAST(CONVERT(varchar(100),ST.BLPDSJ,120) AS varchar),9,8) AS 判定时间/*不良判定时间*/,SUBSTRING(CAST(CONVERT(varchar(100),ST.BLDJSJ,120) AS varchar),9,8) AS 登记时间/*不良登记时间 */ ,(CASE WHEN ST.ZRGXMC IS NULL THEN ST.GXMC ELSE ST.ZRGXMC END ) AS 责任工序名称 ,ST.ZRID AS 责任人id ,(CASE WHEN ST.GXMC IS NULL THEN ST.ZRGXMC ELSE ST.GXMC END) AS 发现工序 ,ST.LZKKH AS 流转卡编号/*流转卡卡号*/ ,ST.blpdrxm AS 不良判定人姓名 ,FZ.JGDYMC AS 加工单元名称 ,FZ.JGSX AS 加工顺序 ,(ST.GCBH +' ' + ST.WPMC +' '+ ST.BLSL +' ' + ST.pdbllx +' ' + ST.BLXXMC +' ' + SUBSTRING(CAST(CONVERT(varchar(100),ST.BLPDSJ,120) AS varchar),9,8) /*不良判定时间*/ +' '+ SUBSTRING(CAST(CONVERT(varchar(100),ST.BLDJSJ,120) AS varchar),9,8) /*不良登记时间 */ +' '+ (CASE WHEN ST.ZRGXMC IS NULL THEN ST.GXMC ELSE ST.ZRGXMC END ) +' ' + ST.ZRID +' '+ (CASE WHEN ST.GXMC IS NULL THEN ST.ZRGXMC ELSE ST.GXMC END) +' ' + ST.LZKKH /*流转卡卡号*/ +' ' + ST.blpdrxm +' ' + FZ.JGDYMC +' ' + FZ.JGSX ) AS 标签 ,1 as '值' ,SUBSTRING(CAST(CONVERT(VARCHAR(100), B.sjd1,120) AS VARCHAR),12,5) AS 时间字段 ,SUBSTRING(CAST(CONVERT(VARCHAR(100), B.sjd1,120) AS VARCHAR),12,3) + '00' AS Y轴时间显示 ,(95-dateDiff(mi,B.sjd1,getDate())/15) AS 时间刻度 ,B.sjd1 from BHGPDJCXST ST JOIN (SELECT AA.JGDYBH,AA.JGDYMC ,BB.GXH,BB.GXMC,AA.JGSX FROM ((select DISTINCT A.JGDYBH,A.JGDYMC ,A.jgsx from JGDYPJ_BYDAY AS A where A.JGDYMC IS NOT NULL AND A.JGSX IS NOT NULL ) AS AA JOIN (select DISTINCT B.JGDYBH,B.GXH,B.GX inner join (select dateadd(mi,(num-0)*-15+15*ceiling(datepart(mi,getDate())/15.0),cast(convert(varchar(13),getDate(),20)+':00:00' as datetime)) as sjd1,dateadd(mi,(num-1)*-15+15*ceiling(datepart(mi,getDate())/15.0),cast(convert(varchar(13),getDate(),20)+':00:00' as datetime)) as sjd2 from t) b on ST.BLDJSJ BETWEEN B.sjd1 AND B.sjd2 WHERE ST.BLDJSJ>dateadd(dd,-1,getdate()) AND ST.GCBH ='0501'
|
最佳回答 |
||||
0
|
|