42 lines
1.2 KiB
SQL
42 lines
1.2 KiB
SQL
WITH
|
|
----------------------------------stack the new and old prices, then sort by stamp----------------------------------------------------------------
|
|
stack AS (
|
|
---new price log
|
|
SELECT 'new' flag, t8part part, t8unit unit, t8plcd plcd, t8date cdate, t8time ctime, t8voll voll, t8pric pric FROM lgdat.iprcctn
|
|
UNION ALL
|
|
---old price log
|
|
SELECT 'old' flag, t7part part, t7unit unit, t7plcd plcd, t7date cdate, t7time ctime, t7voll voll, t7pric pric FROM lgdat.iprccto
|
|
)
|
|
--------------------------------link in iprctt to get user and program----------------------------------------------------------------------------
|
|
,join_user AS (
|
|
SELECT
|
|
s.*
|
|
,t.TAUSER
|
|
,t.TAPROG
|
|
FROM
|
|
stack s
|
|
LEFT OUTER JOIN lgdat.iprcct t ON
|
|
t.taplcd = s.plcd
|
|
AND t.tapart = s.part
|
|
AND t.TAUNIT = s.unit
|
|
AND t.tadate = s.cdate
|
|
AND t.tatime = s.ctime
|
|
)
|
|
----------------------------------seek a single part number----------------------------------------------------------------------------------------
|
|
SELECT
|
|
*
|
|
FROM
|
|
join_user
|
|
WHERE
|
|
part = 'AZM09000E24C054'
|
|
and PLCD = 'GUAU'
|
|
AND flag = 'new'
|
|
ORDER BY
|
|
part
|
|
,unit
|
|
,plcd
|
|
,voll
|
|
,cdate ASC
|
|
,ctime ASC
|
|
,flag desc
|