订单全周期查询的不断完善过程-Access

发布于 2021-05-14 06:01

#全周期查询-0SELECT a.日期 AS 订单日期, a.单据编号, a.供应商, a.物料名称, a.规格型号, a.数量,        b.批号, b.实收数量, b.日期 AS 入库日期,        c.调拨量, c.日期 AS 出库日期, c.验收, c.调入仓库FROM (采购订单序时簿 AS a      LEFT JOIN 外购入库序时簿 AS b ON (a.物料名称=b.物料名称) AND (a.供应商=b.供应商) AND (b.源单单号=a.单据编号))      LEFT JOIN (SELECT 日期, 物料名称,规格型号, 批号,round(调拨数量,2) as 调拨量,验收,调入仓库 from `仓库调拨序时簿`                      WHERE 调入仓库 not in ('辅料合格','试剂合格')               UNION all               SELECT 日期,物料名称,规格型号,批号,round(-调拨数量,2),验收,调入仓库 from `仓库调拨序时簿`                  WHERE 调入仓库 in ('辅料合格','试剂合格')) AS c ON b.批号=c.批号     WHERE a.供应商 in (select 供应商 from 欠款 where right(类别,1)='易')                               and a.物料名称 in (select 物料名称 from 易制毒化学品目录 )                                 and a.日期>#2021-1-1#;#全周期查询-1SELECT a.日期 AS 订单日期, a.单据编号, a.供应商, a.物料名称, a.规格型号, a.订单数量,        b.批号, b.实收数量, b.日期 AS 入库日期,        c.调拨量, c.日期 AS 出库日期, c.验收, c.调入仓库FROM ((SELECT 日期, 单据编号, 供应商, 物料名称, 规格型号, sum(数量) AS 订单数量        FROM 采购订单序时簿 WHERE 供应商 in (         select 供应商 from 欠款 where right(类别,1)='易'             and 物料名称 in (select 物料名称 from 易制毒化学品目录 )              and 日期>=#2021-1-1#        GROUP BY 日期, 单据编号, 供应商, 物料名称, 规格型号)  AS a     LEFT JOIN 外购入库序时簿 AS b ON (a.物料名称=b.物料名称) AND (a.供应商=b.供应商) AND (b.源单单号=a.单据编号))     LEFT JOIN (SELECT 日期, 物料名称,规格型号, 批号,round(调拨数量,2) as 调拨量,验收,调入仓库 from `仓库调拨序时簿`                     WHERE 调入仓库 not in ('辅料合格','试剂合格')               UNION all                SELECT 日期,物料名称,规格型号,批号,round(-调拨数量,2),验收,调入仓库 from `仓库调拨序时簿`                     WHERE 调入仓库 in ('辅料合格','试剂合格'))  AS c                ON b.批号=c.批号;#全周期查询-2SELECT a.日期 AS 订单日期, a.单据编号, a.供应商, a.物料名称, a.规格型号, a.订单数量,        b.批号, b.实收数量, b.日期 AS 入库日期,        c.调拨量, c.日期 AS 出库日期, c.验收, c.调入仓库FROM ((SELECT 日期, 单据编号, 供应商, 物料名称, 规格型号, sum(数量) AS 订单数量        FROM 采购订单序时簿 WHERE 供应商 in (select 供应商 from 欠款 where right(类别,1)='易'            and 物料名称 in (select 物料名称 from 易制毒化学品目录 ) and 日期>=#2021-1-1#        GROUP BY 日期, 单据编号, 供应商, 物料名称, 规格型号)  AS a      LEFT JOIN 外购入库序时簿 AS b ON (a.物料名称=b.物料名称) AND (a.供应商=b.供应商) AND (b.源单单号=a.单据编号))      LEFT JOIN (SELECT 日期, 物料名称,规格型号, 批号,round(调拨数量,2) as 调拨量,验收,调入仓库 from `仓库调拨序时簿`                      WHERE 调入仓库 not in ('辅料合格','试剂合格')               UNION all              (SELECT 日期,物料名称,规格型号,批号,round(-调拨数量,2) as 调拨量,验收,调入仓库 from `仓库调拨序时簿`                      WHERE 调入仓库 in ('辅料合格','试剂合格'))              UNION all              SELECT 日期, 物料名称,规格型号, 批号,round(调拨数量,2) as 调拨量,验收,调入仓库 from `仓库调拨序时簿`                       WHERE 调出仓库='辅料合格' and 调入仓库='试剂合格')  AS c               ON b.批号=c.批号;#全周期查询-3SELECT one.*, two.调拨总量,        round(IIf((one.实收数量-two.调拨总量) Is Null,0,one.实收数量-two.调拨总量),2AS 即时库存FROM (SELECT a.日期 AS 订单日期, a.单据编号, a.供应商, a.物料名称, a.规格型号, a.订单数量,              b.批号, b.实收数量, b.日期 AS 入库日期,              c.调拨量, c.日期 AS 出库日期, c.验收, c.调入仓库       FROM ((SELECT 日期, 单据编号, 供应商, 物料名称, 规格型号, sum(数量) AS 订单数量             FROM 采购订单序时簿 WHERE 供应商 in (select 供应商 from 欠款 where right(类别,1)='易')                           and 物料名称 in (select 物料名称 from 易制毒化学品目录 )                 and 日期>=#2020-05-1#             GROUP BY 日期, 单据编号, 供应商, 物料名称, 规格型号)  AS a             LEFT JOIN 外购入库序时簿 AS b ON (b.源单单号=a.单据编号) AND (a.供应商=b.供应商) AND (a.物料名称=b.物料名称))             LEFT JOIN (SELECT 日期, 物料名称,规格型号, 批号,round(调拨数量,2) as 调拨量,验收,调入仓库 from `仓库调拨序时簿`                            WHERE 调入仓库 not in ('辅料合格','试剂合格')                      UNION all                       (SELECT 日期,物料名称,规格型号,批号,round(-调拨数量,2) as 调拨量,验收,调入仓库 from `仓库调拨序时簿`                            WHERE 调入仓库 in ('辅料合格','试剂合格'))                      UNION all                       SELECT 日期, 物料名称,规格型号, 批号,round(调拨数量,2) as 调拨量,验收,调入仓库 from `仓库调拨序时簿`                            WHERE 调出仓库='辅料合格' and 调入仓库='试剂合格')  AS c ON b.批号=c.批号) AS one      LEFT JOIN (SELECT a.物料名称, a.单据编号, b.批号,                      IIf(sum(调拨量) Is Null,0,sum(调拨量)) AS 调拨总量                 FROM ((SELECT 日期, 单据编号, 供应商, 物料名称, 规格型号, sum(数量) AS 订单数量                        FROM 采购订单序时簿 WHERE 供应商 in (select 供应商 from 欠款 where right(类别,1)='易')                                 and 物料名称 in (select 物料名称 from 易制毒化学品目录 )                             and 日期>=#2020-05-1#                        GROUP BY 日期, 单据编号, 供应商, 物料名称, 规格型号) AS a                        LEFT JOIN 外购入库序时簿 AS b ON (b.源单单号=a.单据编号) AND (a.供应商=b.供应商) AND (a.物料名称=b.物料名称))                        LEFT JOIN (SELECT 日期, 物料名称,规格型号, 批号,round(调拨数量,2) as 调拨量,验收,调入仓库 from `仓库调拨序时簿`                                       WHERE 调入仓库 not in ('辅料合格','试剂合格'                                 UNION all                                 (SELECT 日期,物料名称,规格型号,批号,round(-调拨数量,2) as 调拨量,验收,调入仓库 from `仓库调拨序时簿`                                        WHERE 调入仓库 in ('辅料合格','试剂合格'))                                 UNION all                                 SELECT 日期, 物料名称,规格型号, 批号,round(调拨数量,2) as 调拨量,验收,调入仓库 from `仓库调拨序时簿`                                         WHERE 调出仓库='辅料合格' and 调入仓库='试剂合格') AS c                                  ON b.批号=c.批号                         GROUP BY a.物料名称, a.单据编号, b.批号)  AS two      ON (one.批号=two.批号) AND (one.单据编号=two.单据编号);

本文来自网络或网友投稿,如有侵犯您的权益请联系邮箱:wyl860211@qq.com,我们将第一时间删除。

相关素材