没有合适的资源?快使用搜索试试~ 我知道了~
鼎捷易飞ERP体检SQL,如:产品类别订单准交率,月度接单和销货金额,工单平均生产周期,工单准时完工率,车间工单准时开工率,组装工单齐套率,采购平均交付天数,采购准交率,每月库存金额等常用sql --1【产品类别订单金额】:类别编号,类别名称,订单产品类别,年月,订单金额 select MA002 类别,MA003 类别简称,LEFT(TC003,6) 年, SUM(TD038*TC009) 订单金额 FROM COPTC left join COPTD ON TC001=TD001 AND TC002=TD002 left join INVMB ON TD004=MB001 LE
资源推荐
资源详情
资源评论
--==============================================================================
--1【产品类别订单金额】:类别编号,类别名称,订单产品类别,年月,订单金额
select MA002 类别,MA003 类别简称,LEFT(TC003,6) 年, SUM(TD038*TC009) 订单金额 FROM COPTC
left join COPTD ON TC001=TD001 AND TC002=TD002
left join INVMB ON TD004=MB001
LEFT JOIN INVMA ON MA002=MB005 AND MA001='1'
WHERE TC003>='20140101' AND TC003<='20161231' AND TC027='Y'
GROUP BY MA002,MA003,LEFT(TC003,6)
--==============================================================================
--2.1【订单产品类别实际交付周期】:客户编号,客户简称,产品类别,订单年月,实际周期=销货单审核日-订单审核日,
SELECT TG004 客户编号,COPMA.MA002 客户简称,MB005 类别,INVMA.MA003 类别名称,SUBSTRING(TG003,1,6) 订单月份,
SUM(datediff(day,TC003,TG003))/COUNT(TC003)周期 FROM COPTH
LEFT JOIN COPTG ON TH001=TG001 AND TH002=TG002
LEFT JOIN COPTC ON TH014=TC001 AND TH015=TC002
LEFT JOIN COPMA ON MA001=TG004
LEFT JOIN INVMB ON MB001=TH004
LEFT JOIN INVMA ON INVMA.MA002=MB005 AND INVMA.MA001='1'
WHERE TG023='Y' AND TG003>'20140101' AND TG003<'20161231'
GROUP BY TG004 ,COPMA.MA002,MB005,INVMA.MA003 , SUBSTRING(TG003,1,6)
ORDER BY TG004 ,COPMA.MA002,MB005,INVMA.MA003 , SUBSTRING(TG003,1,6)
--===========================================================================
--2.2【订单产品类别期望交期】:客户编号,客户简称,产品类别,订单年月,期望周期=预计交货日-订单创建日,
SELECT TC004 客户,MA002 名称,INVMB.MB005 产品类别 ,SUBSTRING(TC003,1,6) 月份,
AVG(DATEDIFF(DAY,SUBSTRING(COPTD.CREATE_DATE,1,8),TD013))期望平均值
FROM COPTD
LEFT JOIN COPTC ON TD001=TC001 AND TD002=TC002
LEFT JOIN COPMA ON MA001=TC004
LEFT JOIN INVMB ON MB001=TD004
WHERE TC027='Y' AND TC003>='20140101' and TC003<='20161231' AND TD013<>''
GROUP BY TC004,MA002,MB005, SUBSTRING(TC003,1,6)
ORDER BY TC004,MA002,MB005, SUBSTRING(TC003,1,6)
--==================================================================================
--2.3【产品类别订单准交率】:产品类别,订单年月,准交率=准时销货笔数/订单笔数
SELECT DISTINCT LEFT(TD013,6) AS 年月,MB005 类别编码,MA003 类别名称
,cast((cast(COUNTS1 as decimal(15,4))/Case When COUNTS2=0 then 1 else COUNTS2 end) as decimal(15,4)) 交付率
FROM COPTD
LEFT JOIN INVMB ON MB001=TD004
LEFT JOIN INVMA ON MB005=MA002 AND MA001='1'
LEFT JOIN
(Select MA002 类别1,LEFT(TD013,6) YM1,Count(Distinct TH014+TH015+TH016) COUNTS1 From COPTG --按照订单【预交货日】筛选同月已销且按时销货笔数
LEFT JOIN COPTH ON TG001=TH001 AND TG002=TH002
LEFT JOIN COPTD ON TD001=TH014 AND TD002=TH015 AND TD003=TH016
LEFT JOIN INVMB ON TD004=MB001
LEFT JOIN INVMA ON MA002= MB005
Where TG023='Y' AND TD013>='20140101' AND TD013<='20161231' AND MA001='1' and TG003<=TD013
GROUP BY LEFT(TD013,6),MA002
) AS CH ON LEFT(TD013,6)=YM1 AND MB005=类别1
LEFT JOIN
(Select MA002 类别2, LEFT(TD013,6) YM2,Count(Distinct TD001+TD002+TD003) COUNTS2 From COPTD --按照订单【预交货日】筛选同月订单笔数
LEFT JOIN INVMB ON TD004=MB001
LEFT JOIN INVMA ON MA002= MB005
Where TD021='Y' AND TD013>='20140101' AND TD013<='20161231' AND MA001='1'
GROUP BY LEFT(TD013,6),MA002
) AS YCH ON LEFT(TD013,6)=YM2 AND MB005=类别2
where TD013>='20140101' AND TD013<='20161231'
ORDER BY MB005,年月
--=======================================================================================================================
WHERE TC027='Y' AND TC003>='20140101' and TC003<='20161231' AND TD013<>''
GROUP BY TC004,MA002,MB005, SUBSTRING(TC003,1,6)
ORDER BY TC004,MA002,MB005, SUBSTRING(TC003,1,6)
--==================================================================================
--2.3【产品类别订单准交率】:产品类别,订单年月,准交率=准时销货笔数/订单笔数
SELECT DISTINCT LEFT(TD013,6) AS 年月,MB005 类别编码,MA003 类别名称
,cast((cast(COUNTS1 as decimal(15,4))/Case When COUNTS2=0 then 1 else COUNTS2 end) as decimal(15,4)) 交付率
FROM COPTD
LEFT JOIN INVMB ON MB001=TD004
LEFT JOIN INVMA ON MB005=MA002 AND MA001='1'
LEFT JOIN
(Select MA002 类别1,LEFT(TD013,6) YM1,Count(Distinct TH014+TH015+TH016) COUNTS1 From COPTG --按照订单【预交货日】筛选同月已销且按时销货笔数
LEFT JOIN COPTH ON TG001=TH001 AND TG002=TH002
LEFT JOIN COPTD ON TD001=TH014 AND TD002=TH015 AND TD003=TH016
LEFT JOIN INVMB ON TD004=MB001
LEFT JOIN INVMA ON MA002= MB005
Where TG023='Y' AND TD013>='20140101' AND TD013<='20161231' AND MA001='1' and TG003<=TD013
GROUP BY LEFT(TD013,6),MA002
) AS CH ON LEFT(TD013,6)=YM1 AND MB005=类别1
LEFT JOIN
(Select MA002 类别2, LEFT(TD013,6) YM2,Count(Distinct TD001+TD002+TD003) COUNTS2 From COPTD --按照订单【预交货日】筛选同月订单笔数
LEFT JOIN INVMB ON TD004=MB001
LEFT JOIN INVMA ON MA002= MB005
Where TD021='Y' AND TD013>='20140101' AND TD013<='20161231' AND MA001='1'
GROUP BY LEFT(TD013,6),MA002
) AS YCH ON LEFT(TD013,6)=YM2 AND MB005=类别2
where TD013>='20140101' AND TD013<='20161231'
ORDER BY MB005,年月
--=======================================================================================================================
剩余13页未读,继续阅读
资源评论
ytwangwt
- 粉丝: 0
- 资源: 7
上传资源 快速赚钱
- 我的内容管理 展开
- 我的资源 快来上传第一个资源
- 我的收益 登录查看自己的收益
- 我的积分 登录查看自己的积分
- 我的C币 登录后查看C币余额
- 我的收藏
- 我的下载
- 下载帮助
最新资源
- yolov5,SSD 可能使用到的一些代码
- 基于c51单片机+DS1302+DHT11温湿度模块+LCD1602显示的万年历硬件原理图+BOM+软件程源码序+仿真图.zip
- NSGA2的MATLAB代码
- Messagepassingtest_GCN_DGL.py
- Sh,Docker 运维好帮手,一招通过 sh 脚本批量快速启动和重启多个Docker 容器
- PCF2123.pdf
- 打开注册表操作.doc
- Windows 常见运行运行库32+64
- WMJUL8iC.html
- 基于3KW光伏并网单相逆变器设计(TMS320F28035控制板+显示板+STM32F103功率板)硬件(原理图+PCB)工程
资源上传下载、课程学习等过程中有任何疑问或建议,欢迎提出宝贵意见哦~我们会及时处理!
点击此处反馈
安全验证
文档复制为VIP权益,开通VIP直接复制
信息提交成功