102 lines
2.5 KiB
SQL
102 lines
2.5 KiB
SQL
CREATE OR REPLACE VIEW rlarp.list_revisions AS
|
|
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----------------------------------------------------------------------------------------
|
|
,newload AS (
|
|
SELECT
|
|
*
|
|
FROM
|
|
join_user
|
|
WHERE
|
|
1 = 1
|
|
--AND part = 'TFT225G0G18B020'
|
|
--and PLCD >= 'NUE'
|
|
AND plcd IN ('GUAU','GUBU','GCAU','GCBU','GCEU','GCWU','NUEU','NUFU','NUGU','NUWU','NUYU','NCEU','NCWU','FUAU','FUBU','FUCU','FCAC','FCBC','FCCC')
|
|
AND flag = 'new'
|
|
ORDER BY
|
|
part
|
|
,unit
|
|
,plcd
|
|
,voll
|
|
,cdate ASC
|
|
,ctime ASC
|
|
,flag desc
|
|
)
|
|
--SELECT * FROM newload
|
|
,unqdate AS (
|
|
SELECT
|
|
part
|
|
,unit
|
|
,plcd
|
|
,voll
|
|
,pric
|
|
,cdate
|
|
,ctime
|
|
,ROW_NUMBER() OVER (PARTITION BY PART, unit, plcd, voll ORDER BY cdate, pric) rn
|
|
FROM
|
|
newload
|
|
)
|
|
,changes AS (
|
|
SELECT
|
|
u.PART
|
|
,u.unit
|
|
,u.plcd
|
|
,u.voll
|
|
,u.pric new_price
|
|
,u.cdate new_date
|
|
,u.ctime new_time
|
|
,prev.pric prev_price
|
|
,CASE WHEN u.voll = max(u.voll) OVER (PARTITION BY u.PART, u.unit, u.plcd, u.cdate) THEN 'X' ELSE '' END bestprice
|
|
FROM
|
|
unqdate u
|
|
LEFT OUTER JOIN unqdate prev ON
|
|
prev.PART = u.PART
|
|
AND prev.unit = u.unit
|
|
AND prev.plcd = u.plcd
|
|
AND prev.voll = u.voll
|
|
AND prev.rn = u.rn - 1
|
|
WHERE
|
|
1=1
|
|
AND u.pric <> COALESCE(prev.pric,0)
|
|
)
|
|
SELECT
|
|
u.PART
|
|
,u.unit
|
|
,u.plcd
|
|
,u.voll
|
|
,u.new_price
|
|
,u.new_date
|
|
,u.new_time
|
|
,u.prev_price
|
|
,u.bestprice
|
|
,i.majsd
|
|
,CASE WHEN COALESCE(m.base_part,'') = '' THEN m.part_group ELSE m.base_part END || ' - ' || m.descr part_groupd
|
|
,m.priceg
|
|
FROM
|
|
changes u
|
|
LEFT OUTER JOIN "CMS.CUSLG".itemm i ON
|
|
i.item = u.PART
|
|
LEFT OUTER JOIN rlarp.price_map m ON
|
|
m.part_group = i.stlc |