x

My table looks like following.

  create table transfers (
       sender varchar not null,
       recipient varchar not null,
       date date not null,
       amount integer not null
   );     

                
  sender     | recipient  | date       | amount
   ------------+------------+------------+--------
    Smith      | Williams   | 2000-01-01 | 200
    Smith      | Taylor     | 2002-09-27 | 1024
    Smith      | Johnson    | 2005-06-26 | 512
    Williams   | Johnson    | 2010-12-17 | 100
    Williams   | Johnson    | 2004-03-22 | 10
    Brown      | Johnson    | 2013-03-20 | 500
    Johnson    | Williams   | 2007-06-02 | 400
    Johnson    | Williams   | 2005-06-26 | 400
    Johnson    | Williams   | 2005-06-26 | 200

Output should look like :

 recipient
 ------------
 johnson
 taylor

Taylor is returned because amount is 1024 and johnson becuase any three sum of amount values gives count 1024.Williams is not returned as any 3 sum of amount doesn't give 1024.

more ▼

asked Jul 15 at 12:21 PM in Default

avatar image

atchyuth
20 1

which 3 amounts for Johnson are you summing to make 1024?

Jul 18 at 09:01 AM Kev Riley ♦♦
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

It looks like any distinct recipient for which the highest three amount values total at least 1024 should be listed.

I would start of by numbering the rows for each distinct recipient from largest amount to smallest amount. Then sum the amounts for all rows numbered up to 3 grouped by recipient and filter out anything with a sum of amount less than 1024...

 -- number each distinct recipient value from largest amount to smallest amount
 with numbered_rows as (
     select 
           row_number() over(partition by recipient order by amount desc) as row_num
         , recipient
         , amount
     from 
         #transfers 
 )
 select 
       recipient
     , sum(amount) as amount_sum
 from 
     numbered_rows
 -- to qualify for the resultset, any *three* recipient rows must have a minimum sum(amount) value
 -- if the sum of the largest 3 amounts dont qualify, no other combination of three recipient rows will either
 where 
     row_num <= 3
 group by 
     recipient
 -- minimum qualifying sum(amount) is 1024
 having 
     sum(amount) >= 1024
 ;
more ▼

answered Jul 19 at 01:46 PM

avatar image

KenJ
25k 3 10 20

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1089

asked: Jul 15 at 12:21 PM

Seen: 45 times

Last Updated: Jul 19 at 01:47 PM

Copyright 2017 Redgate Software. Privacy Policy