| Author |
 Topic  |
|
|
zorban
Starting Member
USA
2 Posts |
 Posted - 08/11/2012 : 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
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 08/11/2012 : 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
ITEMAlternatively, 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
India
47040 Posts |
Posted - 08/11/2012 : 10:42:29
|
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
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
zorban
Starting Member
USA
2 Posts |
Posted - 08/12/2012 : 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
India
47040 Posts |
Posted - 08/12/2012 : 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
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 08/12/2012 : 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 hereIn the second code, put the exclusion condition as a WHERE clause in the inner query:FROM
SALESHIS
WHERE
YourExclusionConditions here |
|
|
| |
Topic |
|
|
|
Finding latest records
