MartinusP MartinusP - 3 months ago 9
SQL Question

sql task of reporting

I have a sql query but I would like to improve them.

customer number | amount | bill | sum1 | sum2 | date | .... (other components)

1001 -130 F/001 0 -70 2016-01-01

1001 -80 F/002 -10 0 2016-05-01


I would like to have as show below.

the amount in the report is chosen as the most recent of the dates for the account, but with the components sum1 and sum2 in report

customer number | amount | sum1 | sum2 | date | .... (other components)

1001 -80 0 -70 2016-05-01

1001 -80 -10 0 2016-05-01


This subquery is owned by the company, I can not show it.

amendment might look just like this

SELECT
customer_number,
amount,
sum1,
sum2,
date,
(other components)
FROM (
(content of the report)
) AS raport
WHERE ....


I tried with:

SELECT
customer_number,
amount,
sum1,
sum2,
date,
(other components)
FROM (
(content of the report)
) AS raport
QUALIFY ROW NUMBER() OVER (PARTITION customer_number, amount ORDER BY date DESC) = 1


but I failed to.

I would ask for some suggestions.

Answer

I don't quite understand your requirement, but perhaps even so this will help. In your original post you flipped the dates, but not the amounts shown in the SUM1 and SUM2 columns. That makes no sense, clearly you made a mistake but I don't know what you wanted there. Anyway, the query below uses the FIRST_VALUE() analytic function, that is probably what you are looking for. If you don't need the ORDER BY clause at the very end, just delete/ignore it.

with
     inputs ( customer_number, amount, bill, sum1, sum2, dt ) as (
       select 1001, -130, 'F/001',   0, -70, date '2016-01-01' from dual union all
       select 1001,  -80, 'F/002', -10,   0, date '2016-05-01' from dual
     )
select customer_number, 
       first_value(amount) over (partition by customer_number order by dt desc) as amount,
       bill, sum1, sum2, dt
from   inputs
order by dt desc
;

Output:

CUSTOMER_NUMBER     AMOUNT BILL        SUM1       SUM2 DT
--------------- ---------- ----- ---------- ---------- ----------
           1001        -80 F/002        -10          0 2016-05-01
           1001        -80 F/001          0        -70 2016-01-01
Comments