How to find the week days between two dates using Vertica?

sachin · September 2023

Hi,
I have a ticket_createddate and ticket_Resolveddate in a table. I want to know the number of working days between these two days. How can I find it?

Eg:
ticket_createddate - 01-Sep-2023 (Friday)
ticket_Resolveddate - 04-Sep-2023 (Monday)

I want the Output as 2 because Only Friday and Monday has to be counted and Sat/Sun has to be excluded.

marcothesane · September 2023

-- create a calendar with a week-day counter that is 0 on the week-end
DROP TABLE IF EXISTS cal;
DROP TABLE
CREATE TABLE cal AS
  SELECT
    tm::DATE as dt
  , CASE DAYOFWEEK(dt)
      WHEN 1 THEN 0
      WHEN 7 THEN 0
      ELSE 1
    END AS wkdaycounter
  FROM (
               SELECT TIMESTAMP '2023-01-01'
     UNION ALL SELECT TIMESTAMP '2023-12-31'
  ) limits(ts)
  TIMESERIES tm AS '1 DAY' OVER(ORDER BY ts)
;
CREATE TABLE
-- select from it...
SELECT
  SUM(wkdaycounter)
FROM cal
WHERE dt BETWEEN DATE '2023-09-01' AND DATE '2023-09-04'
 SUM 
-----
   2

sachin · September 2023

Thanks much..but the example i gave is just a single record. I have thousands of records in the table. So, if i have two date columns, then how can i find the week days difference between these columns...which has plenty of records..thanks in advance..

marcothesane · September 2023

Then, you need to create a distinct "calendar" between creation and resolution date for each ticket:
1. UNION SELECT ticket id and start date with ticket id and resolution date, to get a limits table
2. apply the TIMESERIES to this limits table, adding the weekday counter with a CASE WHEN expression
3. join the base tickets table with the time series query, sum the week day counters, and group by the base columns of the ticket table you want in the report.

WITH 
-- your base table .... not part of the end query ..
ticket(tkid,tkname,cre_dt,res_dt) AS (
            SELECT 1,'3rd ticket', DATE '2023-09-01', DATE '2023-09-04'
  UNION ALL SELECT 2,'2nd ticket', DATE '2023-08-01', DATE '2023-08-04'
  UNION ALL SELECT 3,'1st ticket', DATE '2023-07-01', DATE '2023-07-04'
)
-- real query starts here, replace following comma wiht "WITH"  ...
,
limits (tkid,ts) AS (
  SELECT
   tkid
  ,cre_dt::TIMESTAMP
  FROM ticket
  UNION ALL 
  SELECT
   tkid
  ,res_dt::TIMESTAMP
  FROM ticket
)
,
tstab AS (
  SELECT
    tkid
  , tm::DATE as dt
  , CASE DAYOFWEEK(tm)
      WHEN 1 THEN 0
      WHEN 7 THEN 0
      ELSE 1
    END AS wkdaycounter
  FROM limits
  TIMESERIES tm AS '1 DAY' OVER(PARTITION BY tkid ORDER BY ts::TIMESTAMP)
)
SELECT
  ticket.tkid
, ticket.tkname
, ticket.cre_dt
, ticket.res_dt
, SUM(tstab.wkdaycounter) AS wkdays_elapsed
FROM ticket
JOIN tstab
  ON ticket.tkid=tstab.tkid
 AND dt BETWEEN cre_dt AND res_dt
GROUP BY 1,2,3,4
ORDER BY 1,2,3,4;
 tkid |   tkname   |   cre_dt   |   res_dt   | wkdays_elapsed 
------+------------+------------+------------+----------------
    1 | 3rd ticket | 2023-09-01 | 2023-09-04 |              2
    2 | 2nd ticket | 2023-08-01 | 2023-08-04 |              4
    3 | 1st ticket | 2023-07-01 | 2023-07-04 |              2

VValdar · September 2023

Marco is right (like as always), it's much better to implement a calendar table into your systems, those questions become much more easier to answer.

Here is a piece of code to implement a teradata-like calendar table (I've adjusted some columns).
On my system, it's very low storage cost, only 250 bytes / year / node.
You can adjust the starting and ending dates:

create table public.calendar
( calendar_date     date    not null                                                              constraint pk_calendar            primary key                                 enabled encoding COMMONDELTA_COMP
, day_of_week       integer not null default ( extract(isodow  from calendar_date)              ) constraint c_chk_day_of_week      check (day_of_week      between 1 and    7) enabled encoding COMMONDELTA_COMP
, day_of_month      integer not null default ( extract(day     from calendar_date)              ) constraint c_chk_day_of_month     check (day_of_month     between 1 and   31) enabled encoding COMMONDELTA_COMP
, day_of_quarter    integer not null default ( extract(doq     from calendar_date)              ) constraint c_chk_day_of_quarter   check (day_of_quarter   between 1 and   92) enabled encoding COMMONDELTA_COMP
, day_of_year       integer not null default ( extract(doy     from calendar_date)              ) constraint c_chk_day_of_year      check (day_of_year      between 1 and  366) enabled encoding COMMONDELTA_COMP
, week_of_year      integer not null default ( extract(week    from calendar_date)              ) constraint c_chk_week_of_year     check (week_of_year     between 1 and   54) enabled encoding RLE
, month_of_quarter  integer not null default ((extract(month   from calendar_date) - 1) % 3 + 1 ) constraint c_chk_month_of_quarter check (month_of_quarter between 1 and    3) enabled encoding RLE
, month_of_year     integer not null default ( extract(month   from calendar_date)              ) constraint c_chk_month_of_year    check (month_of_year    between 1 and   12) enabled encoding RLE
, quarter_of_year   integer not null default ( extract(quarter from calendar_date)              ) constraint c_chk_quarter_of_year  check (quarter_of_year  between 1 and    4) enabled encoding RLE
, year_of_calendar  integer not null default ( extract(year    from calendar_date)              ) constraint c_chk_year_of_calendar check (year_of_calendar between 1 and 9999) enabled encoding COMMONDELTA_COMP
, iso_year_week     char(7) not null default ( to_char(calendar_date, 'iyyy"W"iw')              )                                                                                       encoding RLE
)
order by calendar_date
unsegmented all nodes;

alter table public.calendar set immutable rows; -- If you're on v12+

insert into public.calendar (calendar_date)
select tm::date
  from ( select timestamp '0001-01-01' union all
         select timestamp '9999-12-31'
       ) as limits (ts)
 timeseries tm as interval '1 day' over(order by ts);

select analyze_statistics('public.calendar');

Once you have this table, the query to solve your question is quite simple:

with cte_ticket (tkid, tkname, cre_dt, res_dt) as
(
select 1, '3rd ticket', date '2023-09-01', date '2023-09-04' union all
select 2, '2nd ticket', date '2023-08-01', date '2023-08-04' union all
select 3, '1st ticket', date '2023-07-01', date '2023-07-04' union all
select 4, '4th ticket', date '2023-09-09', date '2023-09-10'
)
    select tkid, tkname, cre_dt, res_dt
         , count(calendar_date)
      from cte_ticket
 left join public.calendar on calendar_date >= cre_dt 
                          and calendar_date <= res_dt
                          and day_of_week   <= 5
group by 1, 2, 3, 4
order by 1;

tkid  tkname      cre_dt      res_dt      count
----  ----------  ----------  ----------  -----
   1  3rd ticket  2023-09-01  2023-09-04      2
   2  2nd ticket  2023-08-01  2023-08-04      4
   3  1st ticket  2023-07-01  2023-07-04      2
   4  4th ticket  2023-09-09  2023-09-10      0

marcothesane · September 2023

Or, with the existing calendar from my preceding answer - just a complex join with the calendar:

WITH
ticket(tkid,tkname,cre_dt,res_dt) AS (
            SELECT 1,'3rd ticket', DATE '2023-09-01', DATE '2023-09-04'
  UNION ALL SELECT 2,'2nd ticket', DATE '2023-08-01', DATE '2023-08-04'
  UNION ALL SELECT 3,'1st ticket', DATE '2023-07-01', DATE '2023-07-04'
)
SELECT
  tkid
, tkname
, cre_dt
, res_dt
, SUM(wkdaycounter)
FROM ticket
JOIN cal ON dt BETWEEN cre_dt AND res_dt
GROUP BY
  tkid
, tkname
, cre_dt
, res_dt
ORDER BY
  tkid
, tkname
, cre_dt
, res_dt
;
 tkid |   tkname   |   cre_dt   |   res_dt   | SUM 
------+------------+------------+------------+-----
    1 | 3rd ticket | 2023-09-01 | 2023-09-04 |   2
    2 | 2nd ticket | 2023-08-01 | 2023-08-04 |   4
    3 | 1st ticket | 2023-07-01 | 2023-07-04 |   2

sachin · September 2023

Excellent ! it worked...thanks a ton VValdar and Marco!!!! really appreciated!!!

How to find the week days between two dates using Vertica?

Answers

Leave a Comment