[易飞]易飞LRP相关SQL

版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/david_520042/article/details/7327337

 朋友发的易飞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)
1348118583_4652.jpg
展开阅读全文

没有更多推荐了,返回首页