CODEWITHSUNDEEP
sqlgoogle-bigquery
Sophie
Sophie

Reputation: 1

How do I get a date that is a number of business days after a certain date in Big Query using SQL?

I made a CTE where I have a date table joined with a holidays table so that I can mark business days (days that are not weekends nor holidays) like this:

dateholidays AS (
    SELECT 
      datetable.date, 
    CASE 
      WHEN datetable.date = holidays.date OR EXTRACT(DAYOFWEEK FROM datetable.date) IN (1, 7)
        THEN False
        ELSE True
    END AS businessday 
    FROM datetable left join holidays ON datetable.date = holidays.date
)

I then joined this date table to my main table on 'date', where I have columns like 'ticket id', 'priority', 'created date' and 'update date'.

When I joined the tables, since each row represents one ticket, there are multiple rows with the same date (since multiple tickets can be filed in one day) which looks something like this (minus the rest of the columns)

Date Ticket ID
July 23 237738
July 23 237737

I want to get the 'update deadline' based on the 'priority'. For example, if the priority is "low", then the deadline is in 5 business days ('updated' column + 5 non-weekend and non-holiday days), etc.

I tried using COUNTIF and Partitions, but it ended up giving me nothing useful. Partitions just counted the number of 'date's that are the same. Using COUNTIF on the business days also only gave me '1' per row.

Upvotes: 0

Views: 59

Answers (1)

Deendayal Singh
Deendayal Singh

Reputation: 1

WITH RECURSIVE DateSeries AS ( SELECT DATE('2023-01-01') AS current_date, 1 AS day_count UNION ALL SELECT DATE_ADD(current_date, INTERVAL 1 DAY), day_count + 1 FROM DateSeries WHERE day_count < 100 ), BusinessDays AS ( SELECT current_date FROM DateSeries WHERE EXTRACT(DAYOFWEEK FROM current_date) NOT IN (1, 7) -- Exclude Saturdays (1) and Sundays (7) ) SELECT MIN(current_date) AS target_date FROM BusinessDays WHERE current_date > DATE('2023-01-01') -- Start date LIMIT 5 -- Number of business days

Upvotes: -1

Related Questions