学步园

 朋友发的易飞LRP相关SQL

--批次需求计划系统
--重点作业的使用（日志）
--是否使用生成批次需求计划: 
SELECT CASE WHEN 
EXISTS(
SELECT TOP 1 PROGID
FROM DSCSYS.dbo.WORKLOG WITH (NOLOCK)
WHERE PROGID='LRPB01' AND COMPANYID='KANGPU'
) THEN 'Y' ELSE 'N' END AS WLOG

--是否使用维护批次生产计划-按品号:
SELECT CASE WHEN 
EXISTS(
SELECT TOP 1 PROGID
FROM DSCSYS.dbo.WORKLOG WITH (NOLOCK)
WHERE PROGID='LRPI01' AND COMPANYID='KANGPU'
) THEN 'Y' ELSE 'N' END AS WLOG

--是否使用维护批次生产计划-按开工日:
SELECT CASE WHEN 
EXISTS(
SELECT TOP 1 PROGID
FROM DSCSYS.dbo.WORKLOG WITH (NOLOCK)
WHERE PROGID='LRPI02' AND COMPANYID='KANGPU'
 ) THEN 'Y' ELSE 'N' END AS WLOG

--是否使用维护批次采购计划-按品号:
SELECT CASE WHEN 
EXISTS(
SELECT TOP 1 PROGID
FROM DSCSYS.dbo.WORKLOG WITH (NOLOCK)
WHERE PROGID='LRPI03' AND COMPANYID='KANGPU'
) THEN 'Y' ELSE 'N' END AS WLOG

--是否使用维护批次采购计划-按采购日: 
SELECT CASE WHEN 
EXISTS(
SELECT TOP 1 PROGID
FROM DSCSYS.dbo.WORKLOG WITH (NOLOCK)
WHERE PROGID='LRPI04' AND COMPANYID='KANGPU'
) THEN 'Y' ELSE 'N' END AS WLOG

--报表使用状况(日志） 
--最近3个月，最主要使用的报表及频率(从高到低): 
--程序编号,程序名称,使用次数"
SELECT MB001,MB002,COUNT(*) AS COUNTS
FROM DSCSYS.dbo.ADMMB AS ADMMB WITH (NOLOCK)
INNER JOIN DSCSYS.dbo.WORKLOG AS WORKLOG WITH (NOLOCK) ON MB001=PROGID
WHERE  CONVERT(varchar(8),DTSTART,112)>=CONVERT(varchar(8),DateAdd(month,-3,getdate()),112)
AND (PROGID in  ('LRPR03','LRPR04','LRPR05','LRPR06','LRPR07','LRPR08','LRPR09'))
AND COMPANYID='KANGPU'
GROUP BY MB001,MB002
ORDER BY COUNTS DESC

--未结项信息
--维护批次生产计划 
--开工日是检测日期之前的，没有发放的计划批号有多少张:
SELECT COUNT(DISTINCT TA001) AS COUNT_LRPTA FROM LRPTA WITH (NOLOCK)
WHERE TA007<=CONVERT(varchar(8),DateAdd(day,-1,getdate()),112)

--维护批次采购计划 
--采购日是检测日期之前的，没有发放的计划批号有多少张:
SELECT COUNT(DISTINCT TC001) AS COUNT_LRPTC FROM LRPTC WITH (NOLOCK)
WHERE TC007<=CONVERT(varchar(8),DateAdd(day,-1,getdate()),112)