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
 group by customer and item higher sale price

Author  Topic 

mjimenezh
Yak Posting Veteran

81 Posts

Posted - 2015-03-05 : 11:59:55
Hi everyone,

I'm developing and sql 2008 view that shows me the last month of sales of every customer and every item sold, the problem is that I need group by customer with the higher Sale Price of every item of the customer, example:

1.If We sold the same item to one customer then it must show just the higher sale price of every item

2.If the same item was sold to the same customer at the same price then it must show just the last sold date record of that item

I hope You can help Me


This is I get :

OrderDate |DebNr|DebName|ItemCode|Descript|Qty|CostPrice|SalePrice|
2015-03-05|0001 |Deb0001|Item-001|Item 001| 3 | 223.10 | 289.00 |
2015-02-25|0001 |Deb0001|Item-001|Item 001| 2 | 220.00 | 286.00 |
2015-03-05|0001 |Deb0001|Item-002|Item 002| 1 | 75.00 | 110.00 |
2015-03-02|0002 |Deb0002|Item-001|Item 001| 1 | 218.00 | 265.00 |
2015-02-27|0002 |Deb0002|Item-001|Item 001| 1 | 218.00 | 265.00 |

This is I need :

OrderDate |DebNr|DebName|ItemCode|Descript|Qty|CostPrice|SalePrice|
2015-03-05|0001 |Deb0001|Item-001|Item 001| 3 | 223.10 | 289.00 |
2015-03-05|0001 |Deb0001|Item-002|Item 002| 1 | 75.00 | 110.00 |
2015-03-02|0002 |Deb0002|Item-001|Item 001| 1 | 218.00 | 265.00 |

This is my code :

SELECT     TOP (100) PERCENT OrderDate, DebtorNr, DebtorName, ItemCode, Description, Qty, CostPrice, SalePrice
FROM dbo.VK_SALE_ORDERS
WHERE (OrderDate >= DATEADD(MM, - 1, GETDATE()))
ORDER BY DebtorNr, ItemCode


Thanks in advance

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2015-03-05 : 13:21:24
Maybe something like:
select top (100) percent
orderdate
,debtornr
,debtorname
,itemcode
,[description]
,qty
,costprice
,saleprice
from (select *
,row_number() over(partition by debtornr
,itemcode
order by saleprice desc
,orderdate
) as rn
from vk_sale_orders
where orderdate>=dateadd(mm,-1,getdate())
) as a
where rn=1
order by debtornr
,itemcode
Go to Top of Page

mjimenezh
Yak Posting Veteran

81 Posts

Posted - 2015-03-10 : 16:02:45
Thank You so much Bitmed, it works perfect.


Regards...
Go to Top of Page
   

- Advertisement -