Need to add column with max value placed in the max date of each weekending

slc1axjslc1axj Vertica Customer

I am partitioning my data by WeekEnd and have created a cumulative column adding up my hours.

SQL: Sum(WorkHours) OVER (partition BY WkEnd, UserName ORDER BY ReportDt, UserName ASC ROWS UNBOUNDED PRECEDING) cumhours

I need to create a column (Wkhours) to bring in only my max cumhours on the max day of the week by UserName

e.g.

Tagged:

Best Answer

  • moshegmosheg Vertica Employee Administrator
    Answer ✓
    SET DATESTYLE TO DMY;
    \set StartDate '''01-Jan-2022'''
    \set EndDate   '''01-FEB-2022'''
    
    CREATE TABLE t30 AS
    SELECT
            'A' || CHR(RANDOMINT(5) + 97) || '12345' AS UserName
    ,       DAYOFWEEK(dt)                           AS DayNumberInWeek
    ,       RANDOMINT(50)                           AS cumhours
    ,       dt                                      AS ReportDt
    ,       WEEK(dt)                                AS week_nbr
    ,       YEAR(dt)                                AS year_id
    ,       date(dt) + (7 - DAYOFWEEK(dt))          AS WkEnd
    ,       YEAR(dt)*10000+MONTH(dt)*100+DAY(dt)    AS date_id
    ,       TO_CHAR(dt,'Day')                       AS weekday_name
    FROM ( SELECT dt::DATE
           FROM ( SELECT DATE :StartDate
                  UNION ALL SELECT DATE :EndDate
                ) startsel(startdt)
           TIMESERIES dt AS '1 DAY' OVER(ORDER BY startdt::TIMESTAMP)
    ) datelist;
    
    WITH dlist AS (SELECT UserName,week_nbr,year_id, MAX(cumhours) as c_max FROM t30 GROUP BY UserName,week_nbr,year_id)
    SELECT t.UserName, t.date_id, t.week_nbr, t.year_id, t.ReportDt, t.WkEnd, t.cumhours,
    case when t.cumhours=d.c_max and t.UserName=d.UserName and t.week_nbr=d.week_nbr and t.year_id=d.year_id
    then t.cumhours end
    FROM t30 t
    LEFT OUTER JOIN dlist d on (t.cumhours=d.c_max and t.UserName=d.UserName and t.week_nbr=d.week_nbr and t.year_id=d.year_id)
    GROUP BY 1,2,3,4,5,6,7,8
    ORDER BY t.date_id;
    
     UserName | date_id  | week_nbr | year_id |  ReportDt  |   WkEnd    | cumhours | case
    ----------+----------+----------+---------+------------+------------+----------+------
     Aa12345  | 20220101 |        1 |    2022 | 2022-01-01 | 2022-01-01 |       14 |   14
     Ac12345  | 20220102 |        2 |    2022 | 2022-01-02 | 2022-01-08 |       10 |
     Aa12345  | 20220103 |        2 |    2022 | 2022-01-03 | 2022-01-08 |       45 |   45
     Ac12345  | 20220104 |        2 |    2022 | 2022-01-04 | 2022-01-08 |       45 |   45
     Aa12345  | 20220105 |        2 |    2022 | 2022-01-05 | 2022-01-08 |       12 |
     Ad12345  | 20220106 |        2 |    2022 | 2022-01-06 | 2022-01-08 |       34 |   34
     Aa12345  | 20220107 |        2 |    2022 | 2022-01-07 | 2022-01-08 |       45 |   45
     Ac12345  | 20220108 |        2 |    2022 | 2022-01-08 | 2022-01-08 |       43 |
     Ac12345  | 20220109 |        3 |    2022 | 2022-01-09 | 2022-01-15 |       49 |   49
     Ae12345  | 20220110 |        3 |    2022 | 2022-01-10 | 2022-01-15 |       40 |   40
     Ae12345  | 20220111 |        3 |    2022 | 2022-01-11 | 2022-01-15 |        0 |
     Ac12345  | 20220112 |        3 |    2022 | 2022-01-12 | 2022-01-15 |       14 |
     Ac12345  | 20220113 |        3 |    2022 | 2022-01-13 | 2022-01-15 |       49 |   49
     Ac12345  | 20220114 |        3 |    2022 | 2022-01-14 | 2022-01-15 |       26 |
     Ac12345  | 20220115 |        3 |    2022 | 2022-01-15 | 2022-01-15 |       10 |
     Aa12345  | 20220116 |        4 |    2022 | 2022-01-16 | 2022-01-22 |       19 |   19
     Ac12345  | 20220117 |        4 |    2022 | 2022-01-17 | 2022-01-22 |       32 |   32
     Ad12345  | 20220118 |        4 |    2022 | 2022-01-18 | 2022-01-22 |       19 |
     Ad12345  | 20220119 |        4 |    2022 | 2022-01-19 | 2022-01-22 |       41 |   41
     Ab12345  | 20220120 |        4 |    2022 | 2022-01-20 | 2022-01-22 |       40 |   40
     Ac12345  | 20220121 |        4 |    2022 | 2022-01-21 | 2022-01-22 |       20 |
     Aa12345  | 20220122 |        4 |    2022 | 2022-01-22 | 2022-01-22 |        4 |
     Ab12345  | 20220123 |        5 |    2022 | 2022-01-23 | 2022-01-29 |        4 |
     Ab12345  | 20220124 |        5 |    2022 | 2022-01-24 | 2022-01-29 |       16 |
     Ad12345  | 20220125 |        5 |    2022 | 2022-01-25 | 2022-01-29 |       11 |   11
     Ab12345  | 20220126 |        5 |    2022 | 2022-01-26 | 2022-01-29 |       41 |   41
     Ae12345  | 20220127 |        5 |    2022 | 2022-01-27 | 2022-01-29 |       37 |   37
     Aa12345  | 20220128 |        5 |    2022 | 2022-01-28 | 2022-01-29 |        2 |    2
     Ae12345  | 20220129 |        5 |    2022 | 2022-01-29 | 2022-01-29 |        5 |
     Ab12345  | 20220130 |        6 |    2022 | 2022-01-30 | 2022-02-05 |       24 |   24
     Ae12345  | 20220131 |        6 |    2022 | 2022-01-31 | 2022-02-05 |       37 |   37
     Ac12345  | 20220201 |        6 |    2022 | 2022-02-01 | 2022-02-05 |        1 |    1
    (32 rows)
    

Answers

  • slc1axjslc1axj Vertica Customer

    I'm seeing rows in the same weekending with cum hours. I only want cumhours on the maximum day of the week.

    e.g.
    Aa12345 | 20220103 | 2 | 2022 | 2022-01-03 | 2022-01-08 | 45 | 45
    Ac12345 | 20220104 | 2 | 2022 | 2022-01-04 | 2022-01-08 | 45 | 45

  • moshegmosheg Vertica Employee Administrator

    You see two rows for two different UserName(s)
    If you wish not to group by UserName then..

    SET DATESTYLE TO DMY;
    \set StartDate '''01-Jan-2022'''
    \set EndDate   '''01-FEB-2022'''
    
    CREATE TABLE t30 AS
    SELECT
            'A' || CHR(RANDOMINT(5) + 97) || '12345' AS UserName
    ,       DAYOFWEEK(dt)                           AS DayNumberInWeek
    ,       RANDOMINT(50)                           AS cumhours
    ,       dt                                      AS ReportDt
    ,       WEEK(dt)                                AS week_nbr
    ,       YEAR(dt)                                AS year_id
    ,       date(dt) + (7 - DAYOFWEEK(dt))          AS WkEnd
    ,       YEAR(dt)*10000+MONTH(dt)*100+DAY(dt)    AS date_id
    ,       TO_CHAR(dt,'Day')                       AS weekday_name
    FROM ( SELECT dt::DATE
           FROM ( SELECT DATE :StartDate
                  UNION ALL SELECT DATE :EndDate
                ) startsel(startdt)
           TIMESERIES dt AS '1 DAY' OVER(ORDER BY startdt::TIMESTAMP)
    ) datelist;
    
    WITH dlist AS (SELECT week_nbr,year_id, MAX(cumhours) as c_max FROM t30 GROUP BY week_nbr,year_id)
    SELECT t.UserName, t.date_id, t.week_nbr, t.year_id, t.ReportDt, t.WkEnd, t.cumhours,
    case when t.cumhours=d.c_max and t.week_nbr=d.week_nbr and t.year_id=d.year_id
    then t.cumhours end as WkHours
    FROM t30 t
    LEFT OUTER JOIN dlist d on (t.cumhours=d.c_max and t.week_nbr=d.week_nbr and t.year_id=d.year_id)
    GROUP BY 1,2,3,4,5,6,7,8
    ORDER BY t.date_id;
    
     UserName | date_id  | week_nbr | year_id |  ReportDt  |   WkEnd    | cumhours | WkHours
    ----------+----------+----------+---------+------------+------------+----------+---------
     Ae12345  | 20220101 |        1 |    2022 | 2022-01-01 | 2022-01-01 |       45 |      45
     Aa12345  | 20220102 |        2 |    2022 | 2022-01-02 | 2022-01-08 |       34 |
     Aa12345  | 20220103 |        2 |    2022 | 2022-01-03 | 2022-01-08 |        4 |
     Ac12345  | 20220104 |        2 |    2022 | 2022-01-04 | 2022-01-08 |       27 |
     Ac12345  | 20220105 |        2 |    2022 | 2022-01-05 | 2022-01-08 |        1 |
     Ae12345  | 20220106 |        2 |    2022 | 2022-01-06 | 2022-01-08 |       24 |
     Ae12345  | 20220107 |        2 |    2022 | 2022-01-07 | 2022-01-08 |       43 |      43
     Ad12345  | 20220108 |        2 |    2022 | 2022-01-08 | 2022-01-08 |       39 |
     Ab12345  | 20220109 |        3 |    2022 | 2022-01-09 | 2022-01-15 |       20 |
     Aa12345  | 20220110 |        3 |    2022 | 2022-01-10 | 2022-01-15 |       35 |
     Ae12345  | 20220111 |        3 |    2022 | 2022-01-11 | 2022-01-15 |        3 |
     Aa12345  | 20220112 |        3 |    2022 | 2022-01-12 | 2022-01-15 |       16 |
     Aa12345  | 20220113 |        3 |    2022 | 2022-01-13 | 2022-01-15 |       38 |
     Ab12345  | 20220114 |        3 |    2022 | 2022-01-14 | 2022-01-15 |        4 |
     Ae12345  | 20220115 |        3 |    2022 | 2022-01-15 | 2022-01-15 |       42 |      42
     Ad12345  | 20220116 |        4 |    2022 | 2022-01-16 | 2022-01-22 |       26 |
     Aa12345  | 20220117 |        4 |    2022 | 2022-01-17 | 2022-01-22 |       26 |
     Ad12345  | 20220118 |        4 |    2022 | 2022-01-18 | 2022-01-22 |       16 |
     Aa12345  | 20220119 |        4 |    2022 | 2022-01-19 | 2022-01-22 |       25 |
     Ac12345  | 20220120 |        4 |    2022 | 2022-01-20 | 2022-01-22 |        0 |
     Ae12345  | 20220121 |        4 |    2022 | 2022-01-21 | 2022-01-22 |       37 |      37
     Aa12345  | 20220122 |        4 |    2022 | 2022-01-22 | 2022-01-22 |       34 |
     Ad12345  | 20220123 |        5 |    2022 | 2022-01-23 | 2022-01-29 |        3 |
     Ac12345  | 20220124 |        5 |    2022 | 2022-01-24 | 2022-01-29 |       22 |
     Ab12345  | 20220125 |        5 |    2022 | 2022-01-25 | 2022-01-29 |       42 |
     Aa12345  | 20220126 |        5 |    2022 | 2022-01-26 | 2022-01-29 |       26 |
     Aa12345  | 20220127 |        5 |    2022 | 2022-01-27 | 2022-01-29 |       10 |
     Ac12345  | 20220128 |        5 |    2022 | 2022-01-28 | 2022-01-29 |       43 |      43
     Aa12345  | 20220129 |        5 |    2022 | 2022-01-29 | 2022-01-29 |       10 |
     Aa12345  | 20220130 |        6 |    2022 | 2022-01-30 | 2022-02-05 |        4 |
     Ac12345  | 20220131 |        6 |    2022 | 2022-01-31 | 2022-02-05 |       16 |      16
     Ac12345  | 20220201 |        6 |    2022 | 2022-02-01 | 2022-02-05 |       14 |
    (32 rows)
    
  • slc1axjslc1axj Vertica Customer
    edited February 2022

    ...

  • slc1axjslc1axj Vertica Customer
    edited February 2022

    ...

Leave a Comment

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