We're Moving!

The Vertica Forum is moving to a new OpenText Analytics Database (Vertica) Community.

Join us there to post discussion topics, learn about

product releases, share tips, access the blog, and much more.

Create My New Community Account Now


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

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