CODEWITHSUNDEEP
sqlt-sqlsql-server-2008
LocustHorde
LocustHorde

Reputation: 6399

Query to select last order (entry) of every product belonging to user that's not returned

I am stuck with rather confusing query.

Assume I have a ProductLending table that tracks what product each user has borrowed, when it was renewed, was it returned or not etc.. Given a user, I want to be able to select, all unique products that are still with the user.

table example: 

userid    DateRenewed    ProductId    isReturned
````````````````````````````````````````````````
1        2011-07-21      15           0
1        2011-08-20      16           0
1        2011-09-21      15           1
2        2011-09-21      17           0
1        2011-09-21      15           0

This is a mock up so sorry if it's not accurate.

Now, given userId = 1, I want to select just unique productId that are NOT returned, but are with the user. So this should give me 15, 16 as result, as even though 15 was returned, it was re-borrowed. If we delete the last row, then the result would just be 16, since user has only 16 with him.

I tried ordering by dateRenewed and selecting top 1 but it did totally something else.. how do I construct a query for this please?

Upvotes: 0

Views: 273

Answers (3)

rabudde
rabudde

Reputation: 7722

If product is not returned by user, then the sum of bought products must be larger than sum of returned products

SELECT userid,ProductId FROM <table>
GROUP BY userid,ProductId HAVING SUM(CASE CAST(isReturned AS INT) WHEN 0 THEN 1 ELSE 0 END)-SUM(CAST(isReturned AS INT))>0

Upvotes: 2

alexm
alexm

Reputation: 6882

select distinct ProductId
from TABLE_NAME t1
where UserId= @UserId 
and IsReturned = 0
and not exists
(
   select *
   from TABLE_NAME t2
   where t2.UserId =  t1.UserId
   and  t2.ProductId = t1.ProductId
   and t2.IsReturned = 1 
   and t2.DateRenewed > t1.DateRenewed
)

Upvotes: 0

Chandu
Chandu

Reputation: 82923

Try this:

;WITH qry AS
(
  SELECT *, 
         ROW_NUMBER() OVER(PARTITION BY userID, ProductID ORDER BY DateRenewed DESC) rn
    FROM YourTable
)
SELECT * 
  FROM qry
WHERE rn = 1 AND isReturned = 0;

Upvotes: 1

Related Questions