[SOLVED] Join table in Snowflake using between in where clause

Issue

I have Week table –

Week,   Friday_dates,   Start_date, End_date
1,      2021-07-16,     2021-07-12, 2021-07-18
2,      2021-07-23,     2021-07-19, 2021-07-25

and so on..

I have another table with list of details by date

Date,        To_do_list
20220-01-02, Text 
20220-01-03, Call
20220-01-03, Text
20220-01-04, Call
20220-01-05, Call

I want count no of to_do_list where it says Call and want join this table with date table for specific dates falling under start_date and end_date.

Can anyone help me please?

Solution

So given your data (I am using a CTE so I don’t have to create the tables)

WITH Weeks(Week, Friday_dates, Start_date, End_date) as (
    SELECT * FROM VALUES
    (1, '2021-07-16'::date, '2021-07-12'::date, '2021-07-18'::date),
    (2, '2021-07-23'::date, '2021-07-19'::date, '2021-07-25'::date)
), Activity(Date, To_do_list) as (
    SELECT * FROM VALUES
    ('20220-01-02'::date, 'Text'),
    ('20220-01-03'::date, 'Call'),
    ('20220-01-03'::date, 'Text'),
    ('20220-01-04'::date, 'Call'),
    ('20220-01-05'::date, 'Call')
)

To join the activity to the weeks and limit the count to just the call’s, which will be counted per week, you would:

SELECT w.Friday_dates
    ,count(*) as call_count
FROM Activity AS a
JOIN Weeks AS w
    on a.DATE between w.Start_date and w.End_date
WHERE a.To_do_list = 'Call'
GROUP BY 1
ORDER BY 1;

which gives no results as none of your weeks overlap the activity time ranges.

So if we change the data to be overlapping:

WITH Weeks(Week, Friday_dates, Start_date, End_date) as (
    SELECT * FROM VALUES
    (1, '2021-07-16'::date, '2021-07-12'::date, '2021-07-18'::date),
    (2, '2021-07-23'::date, '2021-07-19'::date, '2021-07-25'::date)
), Activity(Date, To_do_list) as (
    SELECT * FROM VALUES
    ('2021-07-18'::date, 'Text'),
    ('2021-07-18'::date, 'Call'),
    ('2021-07-18'::date, 'Text'),
    ('2021-07-19'::date, 'Call'),
    ('2021-07-20'::date, 'Call')
)

we now get:

FRIDAY_DATES CALL_COUNT
2021-07-16 1
2021-07-23 2

But if we take you SQL from the comment and make it valid:

select * from Weeks a 
left join (
    select Date, 
        count(*) from Activity 
    where To_do_list like '%Call%' 
    group by DATE
) b 
    on b.DATE between a.Start_date and a.End_date 
ORDER BY 1;

we get..

WEEK FRIDAY_DATES START_DATE END_DATE DATE COUNT(*)
1 2021-07-16 2021-07-12 2021-07-18 2021-07-18 1
2 2021-07-23 2021-07-19 2021-07-25 2021-07-19 1
2 2021-07-23 2021-07-19 2021-07-25 2021-07-20 1

Answered By – Simeon Pilgrim

Answer Checked By – Pedro (BugsFixing Volunteer)

Leave a Reply

Your email address will not be published.