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.sjd1from BHGPDJCXST STJOIN (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.GXMC from GXPJ_BYDAY AS B) AS BB ON AA.JGDYBH =BB.JGDYBH)) AS FZ ON FZ.GXH=ST.GXH 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'