CODEWITHSUNDEEP
google-bigquery
wizkids121
wizkids121

Reputation: 656

Extracting Business Days and Business Day of Current Month in BigQuery

I'm looking to do two things - I'm trying to a.) only extract business days within a month and b.) what business day it is of the month its in.

So I know the business day portion can be done by using the DAYOFWEEK function and getting rid of all days that equal 1 or 7, but I'm a bit stumped on how to solve the business day of month portion. For example, today would be the 21st business day of this month, but the 21st business day of another month might not be the 29th, so it wouldn't make sense to compare that sort of way. It could be a weekend. For the purposes of making this simple, we don't need to worry about holidays that fall on a typical business day (i.e Memorial Day).

So my goal is basically to have an output that gives you -

Column 1: ID (lets just say this is some three character randomly generated alphanumeric ID)

Column 2: start_date

Column 3: Day of week

Column 4: business day within start_date month

Anyone have any advice?

Upvotes: 0

Views: 3453

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173046

Try below 

SELECT
  start_date, 
  business_day, 
  DAYOFWEEK(business_day) AS day_of_week,
  ROW_NUMBER() OVER(PARTITION BY start_date ORDER BY business_day) AS business_day_number
FROM (
  SELECT 
    start_date, DATE(DATE_ADD(TIMESTAMP(start_date), pos - 1, "DAY")) AS business_day
  FROM (
    SELECT start_date, ROW_NUMBER() OVER() AS pos
    FROM (FLATTEN((
      SELECT start_date, SPLIT(RPAD('', 1 + DATEDIFF(TIMESTAMP(end_date), TIMESTAMP(start_date)), '.'),'') AS h
      FROM 
        (SELECT '2016-01-01' AS start_date, '2016-01-31' AS end_date),
        (SELECT '2016-03-01' AS start_date, '2016-03-31' AS end_date),
        (SELECT '2016-06-01' AS start_date, '2016-06-30' AS end_date)
    ), h
  )))
  HAVING DAYOFWEEK(business_day) NOT IN (1,7)

)

in my mind - you changed original question - so I felt it is appropriate to provide separate answer instead of adding to my original one

where would the table name go?

assuming YourTable looks like below: 

start_date  end_date     
2016-01-01  2016-01-31   
2016-03-01  2016-03-31   
2016-06-01  2016-06-30

the query will look like 

SELECT
  start_date, 
  business_day, 
  DAYOFWEEK(business_day) AS day_of_week,
  ROW_NUMBER() OVER(PARTITION BY start_date ORDER BY business_day) AS business_day_number
FROM (
  SELECT 
    start_date, DATE(DATE_ADD(TIMESTAMP(start_date), pos - 1, "DAY")) AS business_day
  FROM (
    SELECT start_date, ROW_NUMBER() OVER() AS pos
    FROM (FLATTEN((
      SELECT start_date, SPLIT(RPAD('', 1 + DATEDIFF(TIMESTAMP(end_date), TIMESTAMP(start_date)), '.'),'') AS h
      FROM YourTable), h
  )))
  HAVING DAYOFWEEK(business_day) NOT IN (1,7)

)

Upvotes: 1

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173046

Extracting Business Days between Start and End Date

In below example Start Date is '2016-06-01' and End Date is '2016-06-30'

SELECT 
  DATE(DATE_ADD(TIMESTAMP('2016-06-01'), pos - 1, "DAY")) AS business_day
FROM (
     SELECT ROW_NUMBER() OVER() AS pos, *
     FROM (FLATTEN((
     SELECT SPLIT(RPAD('', 1 + DATEDIFF(TIMESTAMP('2016-06-30'), TIMESTAMP('2016-06-01')), '.'),'') AS h
     FROM (SELECT NULL)),h
)))
HAVING DAYOFWEEK(business_day) NOT IN (1,7)

Finding Nth Business Day between Start and End Date

In below example Start Date is '2016-06-01' and End Date is '2016-06-30' and N = 21

SELECT 
  business_day
FROM (
  SELECT
    business_day,
    ROW_NUMBER() OVER(ORDER BY business_day) AS number
  FROM (
    SELECT 
      DATE(DATE_ADD(TIMESTAMP('2016-06-01'), pos - 1, "DAY")) AS business_day
    FROM (
         SELECT ROW_NUMBER() OVER() AS pos, *
         FROM (FLATTEN((
         SELECT SPLIT(RPAD('', 1 + DATEDIFF(TIMESTAMP('2016-06-30'), TIMESTAMP('2016-06-01')), '.'),'') AS h
         FROM (SELECT NULL)),h
    )))
    HAVING DAYOFWEEK(business_day) NOT IN (1,7)
  )
)
WHERE number = 21

Upvotes: 1

Related Questions