group by customer and item higher sale price

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

mjimenezh
Yak Posting Veteran

81 Posts

Posted - 2015-03-10 : 16:02:45

Thank You so much Bitmed, it works perfect.

Regards...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources