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

slc1axjslc1axj Vertica Customer ✭
in General Discussion

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
Format
SpoilerCodeQuote
Heading 2Heading 1
Emoji
:):D:(;):/:o:s:p:'(:|B):#:*<3o:)>:)
Url
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file
Home • General DiscussionComment As ...