TSQL I can not add a proper filter on date/recid

Question

In the query below you will see an Item: 4500520400500

In this query I would like to list only the rule of this item with the latest date 10-10-2016 in this case. OR with the latest RecID

I can not get this to work with select in select querys. Tried before In this case I get all the items, but I only want the item with the last recid.

I tried with the WHERE Select MAX( RECID ) but that only gives on record back from the whole table and not 1 record whithin the already made selection.

SELECT  A.DATAAREAID, A.ITEMID
, B.ACTIVATIONDATE, B.PRICE, B.CREATEDDATETIME, B.RECID
, 'DEFAULTORDERTYPE' = CASE
    WHEN C.DEFAULTORDERTYPE = 0 THEN 'Purch'
    WHEN C.DEFAULTORDERTYPE = 1 THEN 'Production'
    WHEN C.DEFAULTORDERTYPE = 2 THEN 'Transfer'
    WHEN C.DEFAULTORDERTYPE = 3 THEN 'Kanban'
    ELSE 'Bestaat niet'
    END
, 'PRODUCTTYPE' = CASE
    WHEN D.PRODUCTTYPE = 1 THEN 'Item'
    WHEN D.PRODUCTTYPE = 2 THEN 'Service'
    ELSE 'Bestaat niet'
    END
, E.MODELGROUPID AS 'Item Model Group'
FROM INVENTTABLE AS A
LEFT JOIN INVENTITEMPRICE AS B ON A.ITEMID = B.ITEMID AND A.PARTITION = B.PARTITION AND A.DATAAREAID = B.DATAAREAID
LEFT JOIN INVENTITEMSETUPSUPPLYTYPE AS C ON A.ITEMID = C.ITEMID AND A.DATAAREAID = C.ITEMDATAAREAID AND A.PARTITION = C.PARTITION
LEFT JOIN ECORESPRODUCT AS D ON A.PRODUCT = D.RECID AND A.PARTITION = D.PARTITION
LEFT JOIN INVENTMODELGROUPITEM AS E ON A.ITEMID = E.ITEMID AND A.DATAAREAID = E.ITEMDATAAREAID
WHERE A.KNK_ITEMHIDDEN = 0  
AND (B.PRICE = 0 OR B.price IS NULL) 
ORDER BY A.DATAAREAID, A.ITEMID, B.ACTIVATIONDATE

score 0 · Accepted Answer · edited May 23 '17 at 11:52

You could use a window function to get the max B.CREATEDDATETIME value for each ITEMID and then only return records where the B.CREATEDDATETIME value matches that maximum:

SELECT  records.DATAAREAID,
        records.ITEMID,
        records.ACTIVATIONDATE,
        records.PRICE
        records.CREATEDDATETIME,
        records.RECID,
        records.DEFAULTORDERTYPE,
        records.PRODUCTTYPE,
        records.[Item Model Group]
FROM
(
    SELECT  A.DATAAREAID, 
            A.ITEMID, 
            B.ACTIVATIONDATE, 
            B.PRICE, 
            B.CREATEDDATETIME,
            MAX(B.CREATEDDATETIME) OVER (PARTITION BY A.ITEMID) MAXCREATEDDATETIME,
            B.RECID,
            'DEFAULTORDERTYPE' = CASE   WHEN C.DEFAULTORDERTYPE = 0 THEN 'Purch'
                                        WHEN C.DEFAULTORDERTYPE = 1 THEN 'Production'
                                        WHEN C.DEFAULTORDERTYPE = 2 THEN 'Transfer'
                                        WHEN C.DEFAULTORDERTYPE = 3 THEN 'Kanban'
                                        ELSE 'Bestaat niet'
                                 END, 
            'PRODUCTTYPE' = CASE    WHEN D.PRODUCTTYPE = 1 THEN 'Item'
                                    WHEN D.PRODUCTTYPE = 2 THEN 'Service'
                                    ELSE 'Bestaat niet'
                                    END, 
            E.MODELGROUPID AS 'Item Model Group'
    FROM INVENTTABLE AS A
    LEFT JOIN INVENTITEMPRICE AS B ON A.ITEMID = B.ITEMID 
                                  AND A.PARTITION = B.PARTITION 
                                  AND A.DATAAREAID = B.DATAAREAID
    LEFT JOIN INVENTITEMSETUPSUPPLYTYPE AS C ON A.ITEMID = C.ITEMID 
                                            AND A.DATAAREAID = C.ITEMDATAAREAID 
                                            AND A.PARTITION = C.PARTITION
    LEFT JOIN ECORESPRODUCT AS D ON A.PRODUCT = D.RECID 
                                AND A.PARTITION = D.PARTITION
    LEFT JOIN INVENTMODELGROUPITEM AS E ON A.ITEMID = E.ITEMID 
                                       AND A.DATAAREAID = E.ITEMDATAAREAID
    WHERE A.KNK_ITEMHIDDEN = 0
    AND (B.PRICE = 0 OR B.price IS NULL) 
) records
WHERE records.CREATEDDATETIME = records.MAXCREATEDDATETIME
ORDER BY records.DATAAREAID, 
         records.ITEMID, 
         records.ACTIVATIONDATE

This is similar to the answer given here.

TSQL I can not add a proper filter on date/recid

1 Answers1