所有单据日期和审核日期不一致的影响库存的单据
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 distinct fromSELECT 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 = '20041130'
SELECT
CASE SUBSTRING(MQ003,1,1)
WHEN 1 THEN '1：库存'
WHEN 2 THEN '2：订单'
WHEN 3 THEN '3：采购'
WHEN 4 THEN '4:BOM'
WHEN 5 THEN '5：工单'
END
AS 系统别，单别，单据名称,
CASE MQ008
WHEN 1 THEN '1：入'
WHEN 2 THEN '2：销'
WHEN 3 THEN '3：领'
WHEN 4 THEN '4：转'
WHEN 5 THEN '5：调'
END
AS 交易类别，数量，金额，金额材料，金额人工,
金额制费，金额加工 FROM
(SELECT LA006 as 单别,MQ002 as 单据名称,SUM(LA011*LA005) AS 数量,SUM(LA013*LA005) as 金额,SUM(LA017*LA005) as 金额材料, SUM(LA018*LA005) as 金额人工,SUM(LA019*LA005) as 金额制费,SUM(LA020*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
) AS X
LEFT JOIN CMSMQ ON MQ001=单别
ORDER BY MQ003,MQ008，单别
单别异动明细
declare @x char(8) ,@y char(8),@z char(4)
select @x = '20041101' ,@y = '20041130'
SELECT LA006 as 单别,MQ002 as 单据名称,LA007 AS 单号,LA008 AS 序号,LA001 AS 品号,(LA011*LA005) AS 数量,(LA013*LA005) as 金额 ,(LA017*LA005) as 金额材料, (LA018*LA005) as 金额人工,(LA019*LA005) as 金额制费,(LA020*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
ORDER BY LA006,MQ002,LA007,LA008
类别异动统计
declare @x char(8) ,@y char(8)
select @x = '20041101' ,@y = '20041130'
SELECT MB005 AS 品号类别,MA003 AS 品号类别名称,
CASE SUBSTRING(MQ003,1,1)
WHEN 1 THEN '1：库存'
WHEN 2 THEN '2：订单'
WHEN 3 THEN '3：采购'
WHEN 4 THEN '4:BOM'
WHEN 5 THEN '5：工单'
END
AS 系统别,
LA006 AS 单别,MQ002 AS 单据名称,
CASE LA014
WHEN 1 THEN '1：入'
WHEN 2 THEN '2：销'
WHEN 3 THEN '3：领'
WHEN 4 THEN '4：转'
WHEN 5 THEN '5：调'
END
AS 异动类别,
SUM(LA011*LA005) AS 数量,SUM(LA013*LA005) as 金额 ,SUM(LA017*LA005) as 金额材料, SUM(LA018*LA005) as 金额人工,SUM(LA019*LA005) as 金额制费,SUM(LA020*LA005) as 金额加工 FROM INVLA
INNER JOIN INVMB ON LA001=MB001
INNER JOIN INVMA ON MB005=MA002
INNER JOIN CMSMC ON LA009=MC001
INNER JOIN CMSMQ ON LA006=MQ001
WHERE LA004>=@x AND LA004<=@y
AND MC004=1
GROUP BY MB005,MA003,MQ003,LA006,MQ002,LA014
ORDER BY MB005
品号异动统计
declare @x char(8) ,@y char(8)
select @x = '20041101' ,@y = '20041130'
SELECT MB005 AS 品号类别,MA003 AS 品号类别名称,MB001 AS 品号,
MB002 AS 品名,MB003 AS 规格,
CASE SUBSTRING(MQ003,1,1)
WHEN 1 THEN '1：库存'
WHEN 2 THEN '2：订单'
WHEN 3 THEN '3：采购'
WHEN 4 THEN '4:BOM'
WHEN 5 THEN '5：工单'
END
AS 系统别,
LA006 AS 单别,MQ002 AS 单据名称,
CASE LA014
WHEN 1 THEN '1：入'
WHEN 2 THEN '2：销'
WHEN 3 THEN '3：领'
WHEN 4 THEN '4：转'
WHEN 5 THEN '5：调'
END
AS 异动类别,
SUM(LA011*LA005) AS 数量,SUM(LA013*LA005) as 金额 ,SUM(LA017*LA005) as 金额材料, SUM(L
A018*LA005) as 金额人工,SUM(LA019*LA005) as 金额制费,SUM(LA020*LA005) as 金额加工 FROM INVLA
INNER JOIN INVMB ON LA001=MB001
INNER JOIN INVMA ON MB005=MA002
INNER JOIN CMSMC ON LA009=MC001
INNER JOIN CMSMQ ON LA006=MQ001
WHERE LA004>=@x AND LA004<=@y
AND MC004=1
GROUP BY MB005,MA003,MB001,MB002,MB003,MQ003,LA006,MQ002,LA014
ORDER BY MB005
销货
暂估
--销货暂估
SELECT INVLA.* FROM INVLA
INNER JOIN (
SELECT COPTH.* FROM COPTG
LEFT JOIN COPTH ON TG001=TH001 AND TG002=TH002
WHERE TG003>='20041001' AND TG003<='20041031' AND TG023='Y'
AND TH026='N') AS X
ON LA006=X.TH001 AND LA007=X.TH002 AND LA008=X.TH003
UNION
--销退暂估
SELECT INVLA.* FROM INVLA
INNER JOIN (
SELECT COPTJ.* FROM COPTI
LEFT JOIN COPTJ ON TI001=TJ001 AND TI002=TJ002
WHERE TI003>='20041001' AND TI003<='20041031' AND TI019='Y'
AND TJ024='N') AS X
ON LA006=X.TJ001 AND LA007=X.TJ002 AND LA008=X.TJ003
销退暂估
SELECT INVLA.* FROM INVLA
INNER JOIN (
SELECT COPTJ.* FROM COPTI
LEFT JOIN COPTJ ON TI001=TJ001 AND TI002=TJ002
WHERE TI003>='20041001' AND TI003<='20041031' AND TI019='Y'
AND TJ024='N') AS X
ON LA006=X.TJ001 AND LA007=X.TJ002 AND LA008=X.TJ003
期初销货转出销货成本
SELECT ACRTB.*,TH004,TH005,TH006,TH012 FROM ACRTB
LEFT JOIN ACRTA ON TA001=TB001 AND TA002=TB002
LEFT JOIN COPTH ON TB005=TH001 AND TB006=TH002 AND TB007=TH003
WHERE TA003>='20041001' AND TA003<='20041031' AND TA025='Y'
AND TB005='2309'
销货结账数量0
declare @y char(8)
select @y = '20041130'
SELECT MA002 AS 品号类别,MA003 AS 类别编号,LA001 AS 品号,MB002 AS 品名,MB003 AS 规格,LA004 AS 日期,LA006 AS 单别,LA007 AS 单号,LA008 AS 序号,LA011*LA005 AS 交易数量,LA012 AS 单位成本,LA005*LA013 AS 金额  FROM COPTG
LEFT JOIN COPTH ON TG001=TH001 AND TG002=TH002
LEFT JOIN INVLA ON LA006=TH001 AND LA007=TH002 AND LA008=TH003
LEFT JOIN INVMB ON LA001=MB001
LEFT JOIN INVMA ON MB005=MA002
WHERE  TG003<=@y AND TG023='Y'
AND TH026='N' AND TH042=0 AND MA001='1'
销货结账数量不为0
declare @y char(8)
select @y = '20041130'
SELECT MA002 AS 品号类别 ,MA003 AS 类别名称 ,LA001 AS 品号,MB002 AS 品名,MB003 AS 规格,LA004 AS 日期,LA006 AS 单别,LA007 AS 单号,LA008 AS 序号,LA011*LA005 AS 交易数量,LA011*LA005+TH042 AS 未结数量,TH042 AS 结账数量, LA012 AS 单位成本,(LA011*LA005+TH042)*LA012 AS 金额  FROM COPTG
LEFT JOIN COPTH ON TG001=TH001 AND TG002=TH002
LEFT JOIN INVLA ON LA006=TH001 AND LA007=TH002 AND LA008=TH003
LEFT JOIN INVMB ON MB001=LA001
LEFT JOIN INVMA ON MB005=MA002
WHERE TG003<=@y AND TG023='Y'
AND TH026='N' AND TH042<>0 AND MA001='1'
销退结账数量0
declare @y char(8)
select @y = '20041130'
SELECT MA002 AS 品号类别 ,MA003 AS 类别名称 ,LA001 AS 品号,MB002 AS 品名,MB003 AS 规格,LA004 AS 日期,LA006 AS 单别,LA007 AS 单号,LA008 AS 序号,LA011*LA005 AS 交易数量,LA012 AS 单位成本,LA005*LA013 AS 金额 FROM COPTI
LEFT JOIN COPTJ ON TI001=TJ001 AND TI002=TJ002
LEFT JOIN INVLA ON LA006=TJ001 AND LA007=TJ002 AND LA008=TJ003
LEFT JOIN INVMB ON MB001=LA001
LEFT JOIN INVMA ON MB005=MA002
WHERE  TI003<=@y AND TI019='Y'
AND TJ024='N' AND TJ037=0
成本投入产出明细
declare @x char(6)
select @x = '

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。