Retrieve field from table B based on date in table A

Question

I have table A which contains tasks and completion dates and table B that contains the Fiscal Year, and production cycles. This is the structure of table B:

FY	Cycle	StartDate	EndDate
2021	2	2021-03-31	2021-06-30
2021	3	2021-07-01	2021-31-12
2022	1	2022-01-01	2022-03-31
2022	2	2022-03-31	2022-06-30

What I want to do is to retrieve Cycle based on whether my date in Table A falls between a StartDate and EndDate.

My resultset would be my date and then the cycle. Eg.

Task with date 2022-02-22 08:43:44.002000 falls between 2022-01-01 and 2022-03-31 so I want to retrieve cycle 1 in FY 2022.

Since I cannot join this table directly and check it with a BETWEEN I tried to create a CTE containing the entire table of B but then I kind of got stuck with the next steps. There must be a better approach than the code below (which doesn't work).

    WITH cte AS (
    SELECT * FROM B
)
CASE WHEN task_date >= (SELECT StartDate FROM cte) AND task_date < (SELECT EndDate FROM CTE)

Answer 1

Could i perhaps point you to a question i've asked in the past where i was trying to link data from 2 tables in separate databases where there was no link between them. The answer from Squirrel was able to give me exactly what i needed:

How To Insert Data Into Temp Table From Different Databases

It basically involves adding a Row Number to each table and joining on that Row Number. You might be able to use that logic to get what you need.