Max weekof by month

slc1axj

I have a table that pulls employee data by week. I need to pull the maximum week_of for each month for this and last year along with all the applicable data related to that week. Can anyone help me with the Vertica query?

Jim_Knicely

Maybe something like this?

dbadmin=> SELECT * FROM ld ORDER BY 1;
     c
------------
 1984-12-21
 1984-12-31
 1987-08-30
 1987-08-31
 2020-04-28
 2020-04-28
 2020-04-29
 2020-04-30
 2021-01-01
 2021-08-30
 2021-09-04
 2021-09-28
 2021-09-30
 2021-10-24
 2021-12-13
 2021-12-23
 2021-12-24
 2021-12-30
 2021-12-31
 2022-01-01
(20 rows)

dbadmin=> SELECT * FROM ld WHERE (c BETWEEN LAST_DAY(c) - 6 AND LAST_DAY(c)) AND (EXTRACT(YEAR FROM c) IN (EXTRACT(YEAR FROM sysdate), EXTRACT(YEAR FROM sysdate)-1)) ORDER BY 1;
     c
------------
 2020-04-28
 2020-04-28
 2020-04-29
 2020-04-30
 2021-08-30
 2021-09-28
 2021-09-30
 2021-12-30
 2021-12-31
(9 rows)

Jim_Knicely

Maybe something like this?

dbadmin=> SELECT * FROM ld ORDER BY 1;
     c
------------
 1984-12-21
 1984-12-31
 1987-08-30
 1987-08-31
 2020-04-28
 2020-04-28
 2020-04-29
 2020-04-30
 2021-01-01
 2021-08-30
 2021-09-04
 2021-09-28
 2021-09-30
 2021-10-24
 2021-12-13
 2021-12-23
 2021-12-24
 2021-12-30
 2021-12-31
 2022-01-01
(20 rows)

dbadmin=> SELECT * FROM ld WHERE (c BETWEEN LAST_DAY(c) - 6 AND LAST_DAY(c)) AND (EXTRACT(YEAR FROM c) IN (EXTRACT(YEAR FROM sysdate), EXTRACT(YEAR FROM sysdate)-1)) ORDER BY 1;
     c
------------
 2020-04-28
 2020-04-28
 2020-04-29
 2020-04-30
 2021-08-30
 2021-09-28
 2021-09-30
 2021-12-30
 2021-12-31
(9 rows)

slc1axj

I figured out this will work too:

select e.* from map_employee e
inner join(select max(week_of) dt, month(week_of) mth,year(week_of) yr from WFMGMT_PRD.map_employee
group by month(week_of),year(week_of)
)
b on e.week_of = b.dt
where week_of>'2020-10-01'