Umut K Umut K - 4 months ago 11
SQL Question

two queries merging (SQL)

i have two queries but i dont know hot to merge them. i am trying to achieve to insert the latest price into the list...

first one is :

SELECT
ITM.CODE
,itm.NAME
,min(isnull(price,0)) [min price]
,max(isnull(price,0)) [max]
,SUM(total)/SUM(amount) as [avr]
FROM LG_092_ITEMS ITM
INNER JOIN LG_092_01_STLINE STL
ON ITM.LOGICALREF = STL.STOCKREF
WHERE PRICE > 0
AND TRCODE = 1
AND CANCELLED = 0
AND INVOICEREF > 0
GROUP BY ITM.name , itm.code
ORDER BY code


the second one is :

SELECT
ITM.CODE, itm.NAME, max (stl.date_) [latest date] , price as [latest price]

FROM LG_092_ITEMS ITM
INNER JOIN LG_092_01_STLINE STL
ON ITM.LOGICALREF = STL.STOCKREF
WHERE PRICE > 0
AND TRCODE = 1
AND CANCELLED = 0
AND INVOICEREF > 0


group by code, name, stl.DATE_ , price

Answer

Not the most elegant, but i think it will work :)

with CTE AS (
    SELECT ITM.CODE
        ,itm.NAME
        ,STL.price AS [latestprice]
        ,ROW_NUMBER() OVER (PARTITION BY ITM.CODE, itm.NAME ORDER BY stl.date_ desc) AS RowN
    FROM LG_092_ITEMS AS ITM
    JOIN LG_092_01_STLINE AS STL ON ITM.LOGICALREF = STL.STOCKREF
    WHERE STL.price > 0
        AND TRCODE = 1
        AND CANCELLED = 0
        AND INVOICEREF > 0
)
SELECT ITM.CODE
    ,itm.NAME
    ,min(isnull(STL.price, 0)) [min price]
    ,max(isnull(STL.price, 0)) [max]
    ,SUM(total) / SUM(amount) AS [avr]
    ,max(CTE.latestprice) AS latestprice
FROM LG_092_ITEMS AS ITM
JOIN LG_092_01_STLINE AS STL ON ITM.LOGICALREF = STL.STOCKREF
JOIN CTE ON CTE.CODE = itm.code AND CTE.name = ITM.name AND CTE.RowN = 1
WHERE STL.price > 0
    AND TRCODE = 1
    AND CANCELLED = 0
    AND INVOICEREF > 0
GROUP BY ITM.NAME,itm.code
ORDER BY itm.code 

For more info on CTE see: https://msdn.microsoft.com/en-us/library/ms175972.aspx

Comments