Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

Max weekof by month

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?

Best Answer

  • Jim_KnicelyJim_Knicely Administrator
    Answer ✓

    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)
    
    

Answers

  • Jim_KnicelyJim_Knicely Administrator

    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)
    
    
  • edited December 2021

    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'

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.