Max date on a date range

1

Hello. I am trying to order a selection by part number and only show the part number with the largest date (most recent transaction). My mind is about burnt at this point and any help would be appreciated.

` Select DISTINCT
Products.ProductCode,
Stock_History.New_Stock,
Stock_History.LastModified

FROM Stock_History LEFT JOIN Products ON Stock_History.ProductID = Products.ProductID

WHERE (Stock_History.LastModified >= "01/01/2012 00:00:00") AND (Stock_History.LastModified <= "01/31/2015 11:59:59") AND (Products.ProductCode <> "")

GROUP BY Products.ProductCode, Stock_History.New_Stock, Stock_History.LastModified

ORDER BY Products.ProductCode    `

I attached a copy of the spreadsheet that is produced by the query. I need the unique product code and then the new stock quantity based on the latest lastmodified date. Thank you all in advance.

2

Try this:

with cte
  as (select p.productcode
            ,h.new_stock
            ,h.lastmodified
            ,row_number() over(partition by p.productcode
                               order by h.lastmodified desc
                              )
             as rn
        from stock_history as h
             inner join products as p
                     on p.productid=h.productid
                    and p.oroductcode<>''
       where h.lastmodified>=cast('2012-01-01 00:00:00' as datetime)
         and h.lastmodified <cast('2015-02-01 00:00:00' as datetime)
     )
select productcode
      ,new_stock
      ,lastmodified
  from cte
 where rn=1
 order by productcode
;
1 Like
3

WOW. Is there a secret to getting to your level of understanding? You are awesome. I really appreciate it.

How do I rate you?

4

Haha practice practice practice practice (shouted out like Steve Balmer -> developers developers developers developers) :smiley:

You already rated me with your like -> thank you :slight_smile:

5

Can I use DENSE_RANK here instead of ROW_Number?