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

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

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 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

  • 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 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)
    
  • edited February 23

    ...

  • edited February 23

    ...

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.