Finding latest records - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Finding latest records

Author

Topic

zorban
Starting Member

2 Posts

Posted - 2012-08-11 : 07:45:31

Hello,

I am trying to find the latest price and qty of an item sorted by the customer.

My table is SAlESHIS
The fields are: ORDATE,CUSTOMER,ITEM,ITEMDESC1,ITEMDESC2,QTY,PRICE

I tried:

SELECT DISTINCT ITEM, CUSTOMER, ITEMDESC1,ITEMDESC2, PRICE, QTY, MAX(DISTINCT ORDATE)
FROM SALESHIS AS T
WHERE CUSTOMER =
(SELECT MAX(CUSTOMER)
FROM SALESHIS WHERE ITEM = T.ITEM)
GROUP BY ITEM, CUSTOMER, ITEMDESC1, ITEMDESC2, PRICE, QTY
ORDER BY ITEM

by I am still getting duplicate items for the same customer...

Could someone point me to the right direction?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-11 : 08:20:57

Based on your description, what you need is to have ORDDATE in the WHERE clause rather than CUSTOMER, like this:

SELECT DISTINCT ITEM,
       CUSTOMER,
       ITEMDESC1,
       ITEMDESC2,
       PRICE,
       QTY,
       ORDATE
FROM   SALESHIS AS T
WHERE  ORDDATE = 
		(
           SELECT MAX(T2.ORDDATE)
           FROM   SALESHIS T2
           WHERE  T2.ITEM = T.ITEM AND T2.CUSTOMER = T.CUSTOMER
       )
ORDER BY
       ITEM

Alternatively, you could use the row_number function like shown below if you are on SQL 2005 or later. In the query above, if you had two orders for the same customer for the same item on the latest date, both would show up. The one below avoids that (by picking the one with the highest price)

SELECT
	ITEM, CUSTOMER, ITEMDESC1, ITEMDESC2, PRICE, QTY, ORDATE
FROM
(
	SELECT 
		ITEM, CUSTOMER, ITEMDESC1, ITEMDESC2, PRICE, QTY, ORDATE
		,ROW_NUMBER() OVER 
			(PARTITION BY CUSTOMER, ITEM ORDER BY ORDDATE DESC, PRICE DESC) AS RN
	FROM
		SALESHIS
) s
WHERE
	RN = 1
ORDER BY
       ITEM

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-11 : 10:42:29

[code]
SELECT
ITEM, CUSTOMER, ITEMDESC1, ITEMDESC2, PRICE, QTY, ORDATE
FROM SALESHIS s
OUTER APPLY (SELECT COUNT(1) AS Cnt
FROM SALESHIS
WHERE CUSTOMER = s.CUSTOMER
AND ITEM = s.ITEM
AND ORDDATE > s.ORDDATE
OR (ORDDATE = s.ORDDATE AND PRICE > s.PRICE)
)s1
WHERE COALESCE(Cnt,0) =0
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

zorban
Starting Member

2 Posts

Posted - 2012-08-12 : 07:13:37

Thanks for the help!

@ sunitabeck

In the second statement, where can I can I put the values of ITEM that I need to exclude:

I tried this, and received an error:

SELECT
ITEM, CUSTOMER, ITEMDESC1, ITEMDESC2, PRICE, QTY, ORDATE
FROM
(
SELECT
ITEM, CUSTOMER, ITEMDESC1, ITEMDESC2, PRICE, QTY, ORDATE
,ROW_NUMBER() OVER
(PARTITION BY CUSTOMER, ITEM ORDER BY ORDDATE DESC, PRICE DESC) AS RN
FROM
SALESHIS
) s
WHERE
RN = 1 AND QTY > '0' AND ITEM <> 'ITEM1' AND ITEM <> 'ITEM2'
ORDER BY
ITEM

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-12 : 12:05:46

if you want to use my suggestion just add it to current WHERE clause using AND

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-12 : 21:32:53

quote:
Originally posted by zorban

Thanks for the help!

@ sunitabeck

In the second statement, where can I can I put the values of ITEM that I need to exclude:

I tried this, and received an error:

SELECT
ITEM, CUSTOMER, ITEMDESC1, ITEMDESC2, PRICE, QTY, ORDATE
FROM
(
SELECT
ITEM, CUSTOMER, ITEMDESC1, ITEMDESC2, PRICE, QTY, ORDATE
,ROW_NUMBER() OVER
(PARTITION BY CUSTOMER, ITEM ORDER BY ORDDATE DESC, PRICE DESC) AS RN
FROM
SALESHIS
) s
WHERE
RN = 1 AND QTY > '0' AND ITEM <> 'ITEM1' AND ITEM <> 'ITEM2'
ORDER BY
ITEM

What is the error message? Are you on SQL 2000? If you are, the row_number function will not work.

You can put the conditions to exclude items in the first query as an AND condition after the existing where clause.


WHERE  ORDDATE = 
		(
           SELECT MAX(T2.ORDDATE)
           FROM   SALESHIS T2
           WHERE  T2.ITEM = T.ITEM AND T2.CUSTOMER = T.CUSTOMER
       )
       AND YourExclusionConditions here

In the second code, put the exclusion condition as a WHERE clause in the inner query:

FROM
     SALESHIS
WHERE
    YourExclusionConditions here

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources