所有单据日期和审核日期不一致的影响库存的单据
SELECT X.单别 AS 单别,CMSMQ.MQ002 as 单据名称,X.单号 as 单号,X.单据日期 as 单据日期, X.审核日期 as 审核日期 FROM CMSMQ INNER JOIN
(
SELECT TG001 AS 单别,TG002 AS 单号 ,TG042 AS 单据日期 ,TG003 AS 审核日期 FROM COPTG
WHERE TG023='Y' AND LEFT(TG003,6)<>LEFT(TG042,6)
UNION
SELECT TI001 AS 单别,TI002 AS 单号 ,TI034 AS 单据日期 ,TI003 AS 审核日期 FROM COPTI
WHERE TI019='Y' AND LEFT(TI003,6)<>LEFT(TI034,6)
UNION
SELECT TG001 AS 单别,TG002 AS 单号 ,TG014 AS 单据日期 ,TG003 AS 审核日期 FROM PURTG
WHERE TG013='Y' AND LEFT(TG003,6)<>LEFT(TG014,6)
UNION
SELECT TI001 AS 单别,TI002 AS 单号 ,TI014 AS 单据日期 ,TI003 AS 审核日期 FROM PURTI
WHERE TI013='Y' AND LEFT(TI003,6)<>LEFT(TI014,6)
UNION
SELECT TC001 AS 单别,TC002 AS 单号 ,TC014 AS 单据日期 ,TC003 AS 审核日期 FROM MOCTC
WHERE TC009='Y' AND LEFT(TC003,6)<>LEFT(TC014,6)
UNION
SELECT TF001 AS 单别,TF002 AS 单号 ,TF012 AS 单据日期 ,TF003 AS 审核日期 FROM MOCTF
WHERE TF006='Y' AND LEFT(TF003,6)<>LEFT(TF012,6)
UNION
SELECT TH001 AS 单别,TH002 AS 单号 ,TH029 AS 单据日期 ,TH003 AS 审核日期 FROM MOCTH
WHERE TH023='Y' AND LEFT(TH003,6)<>LEFT(TH029,6)
UNION
SELECT TK001 AS 单别,TK002 AS 单号 ,TK027 AS 单据日期 ,TK003 AS 审核日期 FROM MOCTK
WHERE TK021='Y' AND LEFT(TK003,6)<>LEFT(TK027,6)
UNION
SELECT TA001 AS 单别,TA002 AS 单号 ,TA014 AS 单据日期 ,TA003 AS 审核日期 FROM INVTA
WHERE TA006='Y' AND LEFT(TA003,6)<>LEFT(TA014,6)
) AS X
ON CMSMQ.MQ001=X.单别
在@x及@y区间内未审核的影响库存的单据明细
declare @x char(8) ,@y char(8)
select @x = '20041101' ,@y = '20041130'
SELECT X.A AS 单别,CMSMQ.MQ002 as 单据名称,X.B as 单号 FROM CMSMQ INNER JOIN
(
SELECT TG001 AS A,TG002 AS B FROM COPTG
WHERE TG023='N' AND TG042>=@x AND TG042<=@y
UNION
SELECT TI001 AS A,TI002 AS B FROM COPTI
WHERE TI019='N'AND TI034>=@x AND TI034<=@y
UNION
SELECT TG001 AS A,TG002 AS B FROM PURTG
WHERE TG013='N'AND TG003>=@x AND TG003<=@y
UNION
SELECT TI001 AS A,TI002 AS B FROM PURTI
WHERE TI013='N'AND TI014>=@x AND TI014<=@y
UNION
SELECT TC001 AS A,TC002 AS B FROM MOCTC
WHERE TC009='N'AND TC014>=@x AND TC014<=@y
UNION
SELECT TF001 AS A,TF002 AS B FROM MOCTF
WHERE TF006='N'AND TF012>=@x AND TF012<=@y
UNION
SELECT TH001 AS A,TH002 AS B FROM MOCTH
WHERE TH023='N'AND TH029>=@x AND TH029<=@y
UNION
SELECT TK001 AS A,TK002 AS B FROM MOCTK
WHERE TK021='N'AND TK027>=@x AND TK027<=@y
UNION
SELECT TA001 AS A,TA002 AS B FROM INVTA
WHERE TA006='N'AND TA014>=@x AND TA014<=@y
) AS X
ON CMSMQ.MQ001=X.A
在@x的年月内所有单据日期和审核日期不一致的影响库存的单据
---在@x的年月内所有单据日期和审核日期不一致的影响库存的单据
declare @x char(6)
select @x = '200411'
SELECT X.单别 AS 单别,CMSMQ.MQ002 as 单据名称,X.单号 as 单号,X.单据日期 as 单据日期, X.审核日期 as 审核日期 FROM CMSMQ INNER JOIN
(
--销货单
SELECT TG001 AS 单别,TG002 AS 单号 ,TG042 AS 单据日期 ,TG003 AS 审核日期 FROM COPTG
WHERE TG023='Y' AND LEFT(TG003,6)<>LEFT(TG042,6) AND LEFT(TG042,6)=@x
UNION
SELECT TI001 AS 单别,TI002 AS 单号 ,TI034 AS 单据日期 ,TI003 AS 审核日期 FROM COPTI
WHERE TI019='Y' AND LEFT(TI003,6)<>LEFT(TI034,6) AND LEFT(TI034,6)=@x
--进货单
UNION
SELECT TG001 AS 单别,TG002 AS 单号 ,TG014 AS 单据日期 ,TG003 AS 审核日期 FROM PURTG
WHERE TG013='Y' AND LEFT(TG003,6)<>LEFT(TG014,6) AND LEFT(TG014,6)=@x
--退货单
UNION
SELECT TI001 AS 单别,TI002 AS 单号 ,TI014 AS 单据日期 ,TI003 AS 审核日期 FROM PURTI
WHERE TI013='Y' AND LEFT(TI003,6)<>LEFT(TI014,6) AND LEFT(TI014,6)=@x
--领/退料单
UNION
SELECT TC001 AS 单别,TC002 AS 单号 ,TC014 AS 单据日期 ,TC003 AS 审核日期 FROM MOCTC
WHERE TC009='Y' AND LEFT(TC003,6)<>LEFT(TC014,6) AND LEFT(TC014,6)=@x
--生产入库单
UNION
SELECT TF001 AS 单别,TF002 AS 单号 ,TF012 AS 单据日期 ,TF003 AS 审核日期 FROM MOCTF
WHERE TF006='Y' AND LEFT(TF003,6)<>LEFT(TF012,6) AND LEFT(TF012,6)=@x
--委外进货单
UNION
SELECT TH001 AS 单别,TH002 AS 单号 ,TH029 AS 单据日期 ,TH003 AS 审核日期 FROM MOCTH
WHERE TH023='Y' AND LEFT(TH003,6)<>LEFT(TH029,6) AND LEFT(TH029,6)=@x
--委外退货单
UNION
SELECT TK001 AS 单别,TK002 AS 单号 ,TK027 AS 单据日期 ,TK003 AS 审核日期 FROM MOCTK
WHERE TK021='Y' AND LEFT(TK003,6)<>LEFT(TK027,6) AND LEFT(TK027,6)=@x
--库存交易单
UNION
SELECT TA001 AS 单别,TA002 AS 单号 ,TA014 AS 单据日期 ,TA003 AS 审核日期 FROM INVTA
WHERE TA006='Y' AND LEFT(TA003,6)<>LEFT(TA014,6) AND LEFT(TA014,6)=@x
) AS X
ON CMSMQ.MQ001=X.单别
在@x和@y时间区间内是否有异动
declare @x char(8) ,@y char(8)
select @x = '20041101' ,@y = '20041130'
SELECT LA006 as 单别,MQ002 as 单据名称,LA007 as 单号,LA008 as 序号,LA004 as 审核日期,LA001 as 品号 ,MB002 as 品名 ,MB003 as 规格 FROM INVLA
INNER JOIN INVMB ON LA001=MB001
INNER JOIN CMSMQ ON LA006=MQ001
WHERE LA004>=@x and LA004<=@y
ORDER BY LA006
有单头没单身
--有单头没单身
SELECT X.TA001 AS 单别,MQ002 AS 单据名称,X.TA002 AS 单号 FROM CMSMQ
INNER JOIN
(--报价单
SELECT TA001,TA002 FROM COPTA
WHERE TA001+TA002 NOT IN (
SELECT DISTINCT TB001+TB002 FROM COPTB)
--客户订单
UNION
SELECT TC001,TC002 FROM COPTC
WHERE TC001+TC002 NOT IN (
SELECT DISTINCT TD001+TD002 FROM COPTD)
--销货单
UNION
SELECT TG001,TG002 FROM COPTG
WHERE TG001+TG002 NOT IN (
SELECT DISTINCT TH001+TH002 FROM COPTH)
--销退单
UNION
SELECT TI001,TI002 FROM COPTI
WHERE TI001+TI002 NOT IN (
SELECT DISTINCT TJ001+TJ002 FROM COPTJ)
UNION
--请购单
SELECT TA001,TA002 FROM PURTA
WHERE TA001+TA002 NOT IN (
SELECT DISTINCT TB001+TB002 FROM PURTB)
--采购单
UNION
SELECT TC001,TC002 FROM PURTC
WHERE TC001+TC002 NOT IN (
SELECT DISTINCT TD001+TD002 FROM PURTD)
--进货单
UNION
SELECT TG001,TG002 FROM PURTG
WHERE TG001+TG002 NOT IN (
SELECT DISTINCT TH001+TH002 FROM PURTH)
--退货单
UNION
SELECT TI001,TI002 FROM PURTI
WHERE TI001+TI002 NOT IN (
SELECT DISTINCT TJ001+TJ002 FROM PURTJ)
--工单
UNION
SELECT TA001,TA002 FROM MOCTA
WHERE TA001+TA002 NOT IN (
SELECT DISTINCT TB001+TB002 FROM MOCTB)
--领退单
UNION
SELECT TC001,TC002 FROM MOCTC
WHERE TC001+TC002 NOT IN (
SELECT DISTINCT TE001+TE002 FROM MOCTE)
--生产入库单
UNION
SELECT TF001,TF002 FROM MOCTF
WHERE TF001+TF002 NOT IN (
SELECT DISTINCT TG001+TG002 FROM MOCTG)
--委外进货单
UNION
SELECT TH001,TH002 FROM MOCTH
WHERE TH001+TH002 NOT IN (
SELECT DISTINCT TI001+TI002 FROM MOCTI)
--委外退货单
UNION
SELECT TK001,TK002 FROM MOCTK
WHERE TK001+TK002 NOT IN (
SELECT DISTINCT TL001+TL002 FROM MOCTL)
) AS X
ON X.TA001=MQ001
ORDER BY TA001
BOM有单头没单身
--BOM有单头没单身
SELECT MC001 AS 品号,MB002 as 品名,MB003 as 规格 FROM BOMMC
INNER JOIN INVMB
ON MC001=MB001
WHERE MC001 NOT IN(
SELECT DISTINCT MD001 FROM BOMMD)
BOM循环
--BOM循环
SELECT BOMMD.MD001 AS 主件品号 ,BOMMD.MD003 as 元件品号 FROM BOMMD
INNER JOIN
(SELECT MD003,MD001 FROM BOMMD) AS X
ON BOMMD.MD001=X.MD003 AND BOMMD.MD003=X.MD001
不存在产品结构中的品号
--不存在产品结构中的品号
SELECT MB001 as 品号,MB002 as 品名,MB003 as 规格 FROM INVMB
WHERE MB001 NOT IN (SELECT DISTINCT MD001 FROM BOMMD)
AND MB001 NOT IN (SELECT DISTINCT MD003 FROM BOMMD)
品名规格重复
--品名规格重复
SELECT DISTINCT INVMB.MB001 AS 品号,INVMB.MB002 as 品名 ,INVMB.MB003 as 规格 ,X.CONT as 重复数 FROM INVMB
INNER JOIN
(SELECT DISTINCT MB002 ,MB003, COUNT(MB002) AS CONT FROM INVMB
GROUP BY MB002,MB003
HAVING COUNT(MB002)>=2
) AS X
ON INVMB.MB002=X.MB002 AND INVMB.MB003=X.MB003
ORDER BY INVMB.MB002,INVMB.MB003
区间内单别异动合计
declare @x char(8) ,@y char(8)
select @x = '20041101' ,@y = '20041130'
SELECT LA006 as 单别,MQ002 as 单据名称,SUM(LA011*LA005) AS 数量,SUM(LA013*LA005) as 金额 FROM INVLA
INNER JOIN CMSMQ ON LA006=MQ001
INNER JOIN CMSMC ON LA009=MC001
WHERE LA004>=@x AND LA004<=@y
AND MC004=1
GROUP BY LA006,MQ002
ORDER BY LA006,MQ002
区间内的库存异动统计
declare @x char(8) ,@y char(8)
select @x = '20041101' ,@y = '2004
易飞系统常用SQL集.rar
5星 · 超过95%的资源 需积分: 50 47 浏览量
2021-08-11
15:37:27
上传
评论 2
收藏 4.52MB RAR 举报 
易飞系统常用SQL集.rar （88个子文件） 
T_SQL 
超期未交齐销售订单明细_SQL.docx 14KB
刷读条码_条码打印查询_SQL.txt 716B 更新维护交易单价金额.docx 13KB
更改错误供应商_SQL.sql 1020B 查询系统锁表_SQL.docx 14KB 纠正供应商代码的系列单据_SQL.docx 13KB SQL with 语句-多阶BOM展阶.sql 2KB 包含更新未过账借方或贷方_SQL.docx 18KB 导出供应商与料件关系清单.txt 379B GradeSQL.txt 122B 易飞会计科目表自己外挂表构造语句_SQL.docx 14KB 采购发票查询状况_SQL.txt 684B 易飞应付对账单_SQL.txt 6KB 查询当月工单完工但无对应的入库单_SQL.docx 13KB 生产中的工单状况_SQL.docx 14KB 校对分录底稿与销货订单数据.txt 194B 更新采购请款单的金额同步问题_SQL.docx 12KB 存货与总账对账查询_SQL.txt 2KB 易飞跟踪SQL的步骤及练习题.docx 2.61MB 采购单价金额分析状况_SQL.txt 802B 销售出货单_生成检验记录_外挂程序无法带出销货单的.txt 2KB 易飞系统重新开帐执行_SQL.sql 6KB 更新入库单别与单号的_SQL.docx 14KB 跨数据库平台实现数据访问_SQL.docx 124KB 批量更新录入采购发票单价_SQL.docx 18KB
易飞ERP表结构.xlsx 1.75MB 更新工单实际完工日期_SQL.docx 12KB 更新生产入库单身批号等单头批号_SQL.docx 12KB 易飞ERP查账SQL.txt 11KB 更新维护采购信息之用_SQL.docx 15KB SQLQuery1.sql 4KB 校对补录生产入库单查询_SQL.txt 320B 检查未过账借方或贷方查询_SQL.docx 15KB 库龄01.txt 7KB 易飞财务成本计价_SQL.sql 3KB 进销存与分录底稿检查校验_SQL.txt 626B 工单上已生产量与实际入库总量对比照_SQL.docx 14KB 超过6天未结束的订单明细_SQL.docx 15KB 生产入库单（阴腐蚀化成）_外挂程序查找对应的工单.txt 1KB 易飞指定客户所有产品最新报价明细_SQL.docx 15KB 查询采购发票与应付调汇单身档_SQL.docx 17KB 根据品号及批号导出等级_SQL.txt 120B 定期更新工单上已发料套数_SQL.docx 13KB 复制数据至EXCEL后填充方法_SQL.txt 452B 查询科目余额表_SQL.docx 14KB 采购订单提醒集_SQL.docx 16KB 校验生产入库单表头与表体不匹配_SQL.docx 13KB 校验进货与暂估_SQL.txt 2KB 易飞工单几个SQL.docx 14KB 未能产生分录底稿检查语句.docx 13KB 进货发票与分录对比_SQL.sql 1KB 易飞常用SQL.txt 37KB 分录底稿发票与采购发票明细_SQL.txt 1KB 期末余额转期初金额_SQL.txt 400B 校验进货单价与采购发票之间差异后更新_SQL.docx 13KB 取品号(不考虑供应商)最后一次单位进价_SQL.docx 14KB 销货单已下单未审核超期2天_SQL.docx 14KB BOM更新是否长周期.txt 865B 低阶码更新语句.docx 13KB 工作中心成本计算后人工与机器工时.txt 149B 更改品号信息库存金额_SQL.sql 1KB 展开多阶BOM表的_SQL.docx 14KB 导出BOM产品结构清单_SQL.txt 399B 已出货未开票检查表_SQL.docx 14KB CheckSQL.txt 1KB 库龄分析表.txt 8KB 会计月结科目余额试算不平.docx 14KB 易飞系统在制明细对账_SQL.sql 11KB 清除YF单据数据SQL9.0最新版本.txt 22KB 成品对应工单领料单位成本及入库批号_SQL.txt 887B 批量性更新采购发票单头档_SQL.docx 17KB 未发料有入库的生产中工单明细_SQL.docx 15KB 查找SQL_ROWID排序方法.txt 89B 多阶BOM明细表脚本.sql 1KB 呆滞物料分析报表查询_SQL.txt 1KB 更新录入进货单单价_SQL.docx 13KB 易飞LRP相关SQL.txt 2KB SQLQuery2.sql 2KB 月结库存异动数量与重算数量差异_SQL.txt 8KB 生产退料单_外挂程序查找到对应工单_SQL.txt 10KB 批次勾选所有程序异动触发记录_SQL.docx 12KB 品质检验_腐蚀_外挂程序查找对应的入库单.txt 1KB 业务单据需自动切换库位代码_SQL.txt 5KB 易飞进销存与总账科目余额查询_SQL.docx 21KB 查询最近出入库日与结存_SQL.docx 12KB 检查易飞关联单据正确性的_SQL.docx 20KB 生产入库单（涂布）_外挂程序查找对应的工单.txt 2KB 易飞ERP自定义报表之采购价格分析功能表_SQL.docx 21KB
weixin_40793636
- 粉丝: 3
- 资源: 25
最新资源
- 基于pygame实现的烟花代码
- mcu-printf关于51单片机使用printf函数进行串口调试的方法
- MySQL和数据表操作
- 微信小程序面试题.pdf
- 基于matlab实现电力系统仿真计算软件包，包括潮流计算，最优潮流计算等.rar
- 基于matlab实现电力系统各种故障波形仿真，单相接地故障，两相间短路，两相接地短路，三相短路等.rar
- 基于matlab实现电动汽车动力性，爬坡性，续驶里程等性能仿真.rar
- Python动态烟花代码.pdf
- 基于matlab实现串口发送接收数据 可配置端口，波特率等 发送可选择ASCII方式或HEX方式
- matlab基于BP神经网络手写字母识别（单一）.zip代码9
资源上传下载、课程学习等过程中有任何疑问或建议，欢迎提出宝贵意见哦~我们会及时处理！
点击此处反馈
评论5