CODEWITHSUNDEEP
snowflake-cloud-data-platform
Jimpats
Jimpats

Reputation: 61

Calculate number of specific days between dates in Snowflake

I am looking for some guidance on finding number of occurrences of lets say Monday and Wednesday between two dates Date1 and Date2 inclusive of the two dates in Snowflake. Any Suggestions?

Upvotes: 6

Views: 1300

Answers (2)

Lukasz Szozda
Lukasz Szozda

Reputation: 175716

A standard practice is to build a calendar table: either as pernament table or inlined view.

CREATE TABLE calendar
AS
SELECT DATEADD(day, ROW_NUMBER() OVER(ORDER BY seq8()), '1999-12-31'::DATE) AS d,
      DAYOFWEEK(d) AS day_of_week,
      DAYNAME(d) AS day_name
      -- month/quarter/year/...
FROM TABLE(GENERATOR(ROWCOUNT => 365*100));

Then:

SELECT c.day_name, COUNT(*) AS cnt
FROM calendar c
WHERE c.d BETWEEN '<date_1>' AND '<date_2>'
  AND c.day_of_week IN (1,3)
GROUP BY c.day_name;

Notes: day of week depeneds on parameter WEEK_START.

Upvotes: 7

Eric Lin
Eric Lin

Reputation: 1510

Lukasz's table solution is quite neat, but I will try the JS version:

// not sure the best name to have here
create or replace function num_of_days_in_between(
  day_nums varchar(255), 
  start_date varchar(10), 
  end_date varchar(10)
) 
RETURNS string
LANGUAGE JAVASCRIPT 
AS $$
  // perform some validations first on parameters
  
  var s = new Date(START_DATE);
  var e = new Date(END_DATE);

  var split = DAY_NUMS.split(",");

  var count = 0;
  var d = s;

  // go through each day and check if the 
  // current day is in the days asked
  while (d.getTime() <= e.getTime()) {
    // split array contains strings, so we need to
    // force getDay() value to be string as well,
    // otherwise includes() will not find it
    if(split.includes(d.getDay()+"")) {
      count++;
    }

    // advance to the next day
    d = new Date(d.getTime() + 86400000);
  }

  return count;
$$;

-- Monday and Wednesday
select num_of_days_in_between('1,3', '2021-10-01', '2021-11-01'); -- 9
-- Tuesday
select num_of_days_in_between('2', '2021-10-01', '2021-11-01'); -- 4

Note that in JS, days start from Sunday with index of 0, refer to Date.getDay().

Upvotes: 5

Related Questions