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!!!
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.
I would have written the query like this:
select top(1) with ties
,sum(price) as sumprice
group by client
order by row_number() over(partition by client
order by invoice desc
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 ). 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
I'm guessing you haven't tried it? Please do and be surprised
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!!!