38 lines
985 B
SQL
38 lines
985 B
SQL
WITH
|
|
--ship customer list
|
|
scl AS (
|
|
SELECT DISTINCT
|
|
bill_cust
|
|
,ship_cust
|
|
FROM
|
|
rlarp.osm
|
|
WHERE
|
|
oseas >= 2021
|
|
AND calc_status <> 'CANCELED'
|
|
AND version = 'ACTUALS'
|
|
AND ship_cust <> ''
|
|
AND part <> ''
|
|
AND gldc <> 'SAM'
|
|
AND substring(glec,1,1) <= '2'
|
|
)
|
|
,rslt AS (
|
|
SELECT
|
|
scl.bill_cust
|
|
,bc.cclass bill_class
|
|
,CASE WHEN bc.dba = '' THEN bc.descr ELSE bc.dba END bill_descr
|
|
,bc.ctype bill_type
|
|
,bc.pricing bill_pricing
|
|
,scl.ship_cust
|
|
,bc.cclass ship_class
|
|
,CASE WHEN sc.dba = '' THEN sc.descr ELSE sc.dba END ship_descr
|
|
,sc.ctype ship_type
|
|
,sc.pricing ship_pricing
|
|
FROM
|
|
scl
|
|
LEFT OUTER JOIN rlarp.cust bc ON
|
|
bc.code = scl.bill_cust
|
|
LEFT OUTER JOIN rlarp.cust sc ON
|
|
sc.code = scl.ship_cust
|
|
)
|
|
SELECT *, CASE WHEN bill_cust = ship_cust THEN 'same' ELSE '' END flag FROM rslt --WHERE bill_cust <> ship_cust AND ship_pricing <> ''
|