Choose a value by year and the max week-CodePudding

I have a sales field which I want to display for the past number of years in the table by the maximum week number as that week has the final sales figure for each year, just looking for how to do a select statement i understand upto the point doing a select sum(sales) and grouping by the year then getting stumped by how to implement the week number

table

id        sales person       sales     week number        year
1           john.d           2000          1              2020
2           john.d           4500          2              2020
3           john.d           7000          3              2020
...
52          john.d           40000         52             2020
53          john.d           3000          1              2021
54          john.d           6000          2              2021
55          john.d           9000          3              2021
...
104         john.d           50000         52             2021
105         john.d           5000          1              2022
106         john.d           10000         2              2022
...
140         john.d           56000         36             2022

what i would like to show is the following

sales_person           week_number       year       sales
john.d                     52            2020       40000
john.d                     52            2021       50000
john.d                     36            2022       56000

Edit: Query I have so far

select
    id,
    sales_person,
    sales,
    week_number,
    year
from
    table
where week_number = (
    select max(week_number)
    from table
    where year = (
        select max(year)
        from table
    )
)
and year = (
    select max(year)
    from table
)
and sales_person = 'john.d'
union
select
    id,
    sales_person,
    sales,
    week_number,
    year
from
    table
where week_number = (
    select max(week_number)
    from table
    where year = (
        select min(year)
        from table
    )
)
and year = (
    select min(year)
    from table
)
and sales_person = 'john.d'

this returns close to the result i am looking for also it is very long if anyone has a shorter way to write this

id     sales_person     sales     week_number     year
52        john.d         40000          52         2020
140       john.d         56000          36         2022

CodePudding user response：

with rownumber with partition of year and week number , you can get the result

CREATE TABLE finweek 
(
    id  int,
    salesperson varchar(300),
    sales   int,
    weeknumber  int,
    yr  int
);


insert all 
INTO finweek VALUES ('1', 'john.d', '2000', '1', '2020') 
INTO finweek VALUES ('2', 'john.d', '4500', '2', '2020') 
INTO finweek VALUES ('3', 'john.d', '7000', '3', '2020') 
  INTO finweek VALUES ('52', 'john.d', '40000', '53', '2020') 
INTO finweek VALUES ('52', 'john.d', '40000', '52', '2020') 
INTO finweek VALUES ('53', 'john.d', '3000', '1', '2021') 
INTO finweek VALUES ('54', 'john.d', '6000', '2', '2021') 
INTO finweek VALUES ('55', 'john.d', '9000', '3', '2021') 
INTO finweek VALUES ('104', 'john.d', '50000', '52', '2021') 
INTO finweek VALUES ('105', 'john.d', '5000', '1', '2022') 
INTO finweek VALUES ('106', 'john.d', '10000', '2', '2022') 
INTO finweek VALUES ('140', 'john.d', '56000', '36', '2022') 
SELECT 1 FROM dual;

select * from (select 
    id,
    salesperson,
    sales,
    weeknumber,
    yr,
  row_number() over(partition by yr order by yr,weeknumber desc) as rnum
from
    finweek
where 
 salesperson = 'john.d' 
) where rnum=1

OUTPUT:

ID  SALESPERSON     SALES   WEEKNUMBER  YR  RNUM
52  john.d  40000   53  2020    1
104     john.d  50000   52  2021    1
140     john.d  56000   36  2022    1