本文主要是介绍未完工数据和系统数据对比分析，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！
select
*
FROM
(
select
a.`db_close_systime` AS a_db_close_systime, -- 订单关闭时间
u.`db_close_systime` AS u_db_close_systime, -- 订单关闭时间
COALESCE( u.`db_close_systime`,a.`db_close_systime`) AS db_close_systime_coalesced ,-- 取非空值的订单关闭时间
a.`inv_code` AS a_inv_code, -- 存货编码
u.`inv_code` AS u_inv_code, -- 存货编码
a.`inv_so_type1` AS a_inv_so_type1, -- 销售一级归类
u.`inv_so_type1` AS u_inv_so_type1, -- 销售一级归类
CASE WHEN a.inv_so_type1 <> u.inv_so_type1 THEN 'Different' ELSE 'Same' END AS inv_so_type1_difference,
a.`order_type` AS a_order_type, -- 销售订单状态
u.`order_type` AS u_order_type, -- 销售订单状态
COALESCE(u.`order_type`, a.`order_type`) AS order_type_coalesced,
a.`so_code_row` AS a_so_code_row, -- 销售子订单编号
u.`so_code_row` AS u_so_code_row, -- 销售子订单编号
a.`so_code` AS a_so_code, -- 销售订单编号
u.`so_code` AS u_so_code, -- 销售订单编号
a.`so_region` AS a_so_region, -- 销售区域
u.`so_region` AS u_so_region, -- 销售区域
CASE
WHEN a.so_region <> u.so_region THEN 'Different'
ELSE 'Same'
END AS region_difference,
a.so_num AS a_so_num,
u.so_num AS u_so_num,
CASE WHEN a.so_num <> u.so_num THEN 'Different' ELSE 'Same' END AS so_num_difference,
a.unfinished_num AS a_unfinished_num, -- 未完工数量
u.unfinished_num AS u_unfinished_num,
CASE WHEN a.unfinished_num <> u.unfinished_num THEN 'Different' ELSE 'Same' END AS unfinished_num_difference,
a.unsold_num AS a_unsold_num,-- 未投单存货数量
u.unsold_num AS u_unsold_num,
CASE WHEN a.unsold_num <> u.unsold_num THEN 'Different' ELSE 'Same' END AS unsold_num_difference,
a.in_num AS a_in_num,-- 累计入库存货数量
u.in_num AS u_in_num,
CASE WHEN a.in_num <> u.in_num THEN 'Different' ELSE 'Same' END AS in_num_difference,
a.in_process AS a_in_process, -- 在制数量
u.in_process AS u_in_process,
CASE WHEN a.in_process <> u.in_process THEN 'Different' ELSE 'Same' END AS in_process_difference,
a.verify_date AS a_verify_date,
u.verify_date AS u_verify_date,
CASE WHEN a.verify_date <> u.verify_date THEN 'Different' ELSE 'Same' END AS verify_date_difference
from
(
SELECT
o.so_code, -- 销售订单号
o.so_code_row,-- 销售子订单号
o.so_region, -- 销售区域
o.pre_month_bt, -- 要货月份
o.verify_date, -- 审核日期
o.st_code, -- 销售类型编码
o.st_name, -- 销售类型名称
o.region, -- 大区
o.order_date, -- 单据日期
o.pre_date, -- 预发货日期
o.inv_so_type1, -- 销售一级归类
o.cus_code, -- 客户编码
o.cus_name, -- 客户名称
o.project, --项目
o.inv_code, -- 存货编码
o.inv_name, -- 存货名称
o.inv_std, -- 产品规格
SUM(COALESCE(o.quantity, 0)) AS so_num, -- 销售订单存货数量
SUM(COALESCE(o.quantity, 0)) - SUM(COALESCE(r.in_num, 0)) AS unfinished_num, -- 未完工数量
SUM(COALESCE(m.in_process, 0)) AS in_process, -- 在制数量
SUM(COALESCE(o.quantity, 0)) - SUM(COALESCE(r.in_num, 0)) - SUM(COALESCE(m.in_process, 0)) AS unsold_num, -- 未投单存货数量
SUM(COALESCE(r.in_num, 0)) AS in_num , -- 累计入库存货数量
case when o.define23 is null and o.db_close_systime is null then '正常'
when o.define23 is null and o.db_close_systime is not null then '异常' else o.define23 end as order_type,
o.db_close_systime,
'2024-06-17' as dt
FROM
(select * from warehouse.dwd_sa_order_details_df where dt = '2024-06-17' and inv_code LIKE '3%'
-- ,'订单取消'
-- 不要备料，取'2022-06-01'，不要库存发货
and st_code not in ('10') and substr(cast(verify_date as string),1,10) > '2022-06-01'
and substr(cast(so_code as string),1,2) <> 'BL'
)o
LEFT JOIN
(select so_code,inv_code,so_seq,sum(in_qty) as in_num from warehouse.dwd_wh_inv_in_df
where dt='2024-06-17'
and inv_code like '3%'
and mo_code NOT LIKE '%CX%'
AND mo_code NOT LIKE '%FG%'
group by so_code,inv_code ,so_seq
)r
ON cast(o.so_code as string)=cast(r.so_code as string) and o.inv_code = r.inv_code and so_seq = row_no
left join
(select so_order_detail_id,inv_code,sum(incomplete_num) AS in_process
from warehouse.dwd_pro_order_details_df
where dt = '2024-06-17' and status = 3 and inv_code LIKE '3%'
group by
so_order_detail_id,inv_code
)m
on cast(o.auto_id as string) = cast(m.so_order_detail_id as string) and o.inv_code = m.inv_code
-- where o.db_close_systime is null or o.define23 like '临时失效'
-- OR r.in_num is not null
group by
o.so_code, -- 销售订单号
o.so_code_row,-- 销售子订单号
o.so_region, -- 销售区域
o.pre_month_bt, -- 要货月份
o.verify_date, -- 审核日期
o.st_code, -- 销售类型编码
o.st_name, -- 销售类型名称
o.region, -- 大区
o.order_date, -- 单据日期
o.pre_date, -- 预发货日期
o.inv_so_type1, -- 销售一级归类
o.cus_code, -- 客户编码
o.cus_name, -- 客户名称
o.project, --项目
o.inv_code, -- 存货编码
o.inv_name, -- 存货名称
o.define23 ,
o.db_close_systime,
o.inv_std -- 产品规格
)a
left join warehouse.dws_sa_unfinished_order_amend_df u
on a.so_code_row = u.so_code_row
-- where a.db_close_systime is null and
-- a.unfinished_num >0 and a.so_code is not null and a.so_code_row is not null and a.inv_code is not null
where u.unfinished_num >0 and u.so_code is not null and u.so_code_row is not null and u.inv_code is not null
)T
where T.a_unfinished_num >0 and T.order_type_coalesced = '正常'
and u_order_type is not null
select
distinct verify_state
from warehouse.dwd_sa_order_details_df
这篇关于未完工数据和系统数据对比分析的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
