How can I add values from a table using a date as a parameter?

Asked

Viewed 36 times

0

I am trying to add some values of a table taking a date into consideration in sql. I want to add up the values until it reaches a date and a value greater than or equal to the initial value.

An investment company wants to calculate its clients' Payback when it discovers which month the accumulated operations of the enterprise are equivalent to or superior to the initial investment. For example, Lucas invested $1,000 and only in the third month obtained his Payback, as the sum of all his operations was greater than his investment. On the other hand, the client Daniel failed to reach his Payback since his investment was $500 and the sum of all his operations was $230. You need to show the name of the client, the initial investment, the Payback month and the value of the return (accumulated value - initial investment value). Also, you should show the ordered result from the highest to the lowest return.

Structure of the two tables :

 CREATE TABLE clients (
    id INTEGER PRIMARY KEY,
    name VARCHAR(50),
    investment NUMERIC
);

CREATE TABLE operations (
    id INTEGER PRIMARY KEY,
    client_id INTEGER REFERENCES clients(id),
    month INTEGER,
    profit NUMERIC
);

INSERT INTO clients (id, name, investment) values
(1, 'Daniel', 500 ),
(2, 'Oliveira', 2000),
(3, 'Lucas', 1000);

INSERT INTO operations (id, client_id, month, profit) values
( 1,    1,  1,  230 ),
( 2,    2,  1,  1000),
( 3,    2,  2,  1000),
( 4,    3,  1,  100 ),
( 5,    3,  2,  300 ),
( 6,    3,  3,  900 ),
( 7,    3,  4,  400 );

Please ask for help, I tried to do more I could not.

Right, I got to this select :

select c.name,
       c.investment,
       CEILING((c.investment / AVG(DISTINCT o.profit))) AS month_of_payback,
       SUM(o.profit)- c.investment as "return"
  from clients c
 inner join operations o on o.client_id = c.id  
 group by c.name,c.investment

However, it is returning the value 700 instead of 300 in the user Lucas.

  • "I tried to do more I couldn’t" And what did Matheus try? edit and ask the question, here at Sopt we do not develop code or do school work, but we can help you get there and ask questions

  • Edited Ricardo, thank you.

  • Search by window functions (Analytic Function) if exercise allows.

  • I took a look at your query, and technically a single select cannot do a limited "sum" based on another column. Your problem would be something like "sum the files, but only until you reach the Internet, and return what the Month". Adding up to a limit is possible by using having, but return which month the sum is that the problem, would have to do a subquery. If I find a time here I try to do an example

No answers

Browser other questions tagged

You are not signed in. Login or sign up in order to post.