Hello everyone here is the following information

в соммы summa summa_som ntype_operation course_valname datecourse ---------- ------------ --------------- --------- ----------- 100 6900 BUY USD 15/03/2017 100 6990 SELL USD 15/03/2017 100 7400 BUY EUR 15/03/2017 100 7520 SELL EUR 15/03/2017

I want to calculate the profit if a person bought 100 dollars from me at the rate of 69 and the rate is 68 then I will get a profit of 100. Taking the value of purchases and sales from another table

Table t_course

 course_valname datecourse buy sell ------------- ---------- ---- ---- USD 15/03/2017 69 69.9 EUR 15/03/2017 74 75.2

I ran this code and got the following result.

  select summa_som, max(case when ntype_operation = 'BUY' then summa_som when ntype_operation = 'SELL' then summa_som - summa * get_rate2 (date_operation,course_valname,upper(substr(ntype_operation,1,1))) else 0 end) as income, course_valname,NTYPE_OPERATION from t_operation WHERE DATECOURSE = TO_DATE('3/15/2017','MM/DD/YYYY') and summa = 100 group by summa_som, course_valname,NTYPE_OPERATION; summa_som income course_valname ntype_operation ------------------------------------------------ 7392 7392 EUR BUY 7594 0 EUR SELL

Where zero should be the result of the addition of 7594 - 7392 = 202 in income and where buy 7392 income should in principle be zero and not at all display what this purchase is in theory, my exchanger makes a profit from what sells for more. I now need to withdraw the difference.

  • Show how you tried to count this amount yourself? - Viktorov
  • Added to the topic. - Smoke
  • For some reason you are trying to use group by and sum in your example - does this mean that you need to get this profit not for each line separately, but the total in a certain section. In addition, in the sample query, you use the summa_som column, which is not in the table description. And in the query for some reason there are no attempts to use the course from the second table. By the way, explain the structure of the course table, there are courses for each date, including weekends or only dates for a course change (when the exchange is working) - Mike
  • I filled courses in the course table using dbms_random.value, I don’t know why there is sum) I’ll add an example to the topic that I want to get as a result - Smok
  • The query should be approached consistently, first we include all the necessary tables in the query and ensure that each line of the initial data contains all the data needed for the calculations. then we start to count the necessary quantities and after that we already group. For the first phase, you need to glue the data table with the courses, and then you need to know if there is a course for all dates and decide what to do if there is no course for the desired date, if this is possible - Mike

0