Summation of most recent invoice

Transact-SQL
1

Hi folks,

Haven't been able to figure how to compose the required query. This is the data showing 2 clients, their invoices and whether these invoices are valid, the SKUs contained in the invoices and price of each SKU.

Client Invoice Valid SKU Price
1 1 1 1000 100
1 2 1 2000 200
1 2 1 4000 500
1 3 0 3000 250
2 1 1 2000 200
2 1 1 1000 100
2 2 1 4000 500
2 2 1 3000 250

I want the result of the query to show the price summation of the most recent valid invoice for each client. Therefore the results would be:

Client Invoice Price
1 2 700
2 2 750

I thought that something like the following might work but I'm off track:
WITH CTE AS (SELECT Client, Invoice, rn = row_number() OVER (partition BY Client ORDER BY Invoice DESC), sum(Price) as SumPrice FROM MyTable where Valid = 1 Group by Client, Invoice)
Select * from CTE where rn = 1

Any assistance would be greatly appreciated! Thanks!!!

2

Figured it out:

WITH CTE AS (SELECT *, rn = row_number() OVER (partition BY Client ORDER BY Invoice DESC) FROM MyTable WHERE Valid = 1)
Select Client, Invoice, Sum (Price) as Amount from MyTable
where Invoice IN (Select Invoice from CTE where rn = 1) group by Client, Invoice

Thanks to all who took time to read the post. Hopefully, this will be useful to someone else in the future.

3

I would have written the query like this:

select top(1) with ties
       client
      ,invoice
      ,sum(price) as sumprice
  from mytable
 where valid=1
 group by client
         ,invoice
 order by row_number() over(partition by client
                                order by invoice desc
                           )
;
4

Thanks bitsmed, but that query only provides the top 1 record in the entire table whereas I need the top 1 valid record for each client. Thanks again though.

In any event, I jumped the gun last night when I posted what I though was the answer (brain gets pretty fried after 18 hours of coding :no_mouth:). However, I think that I managed to figure it out this morning. This is what I've got:

WITH CTE1 AS (SELECT *, rn = row_number() OVER (partition BY Client ORDER BY Invoice DESC) FROM MyTable WHERE Valid = 1),
CTE2 AS (Select Client, Invoice, Sum (Price) as Amount from MyTable group by Client, Invoice)
Select * from cte2 t1 join (Select * from cte1 where rn=1) t2 on T1.Client = t2.Client and T1.Invoice= t2.Invoice

Thanks everyone!

5

I'm guessing you haven't tried it? Please do and be surprised

6

Thanks bitsmed,

You're absolutely correct. I think that I forgot to put the "With ties" earlier when I did the test, so resulting in only the first record being provided. Your query yields the same results as mine, all the while probably being better optimized. Great stuff! Thanks!!!