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:
0
Best Answer
-
mosheg
Administrator
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)1
Leave a Comment
Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.
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
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)...
...