Find all instances of a date in a date range - SQL Server

Question

I need to find the price for an item for each financial year end date in a date range. In this case the financial year is e.g. 31 March

The table I have for example:

ItemID	Value	DateFrom	DateTo
1	10	'2019/01/01'	'2021/02/28'
1	11	'2021/03/01'	'2021/05/01'

SQL Fiddle

The SQL would thus result in the above table to be:

ItemID	Value	DateFrom	DateTo
1	10	'2019/01/01'	'2019/03/30'
1	10	'2020/03/31'	'2021/02/28'
1	11	'2020/03/01'	'2021/03/30'
1	11	'2020/03/31'	'2021/05/01'

Answer 1

You can solve it, but a prerequisite is the creation of a table called financial_years and filling it with data. This would be the structure of the table:

financial_years(id, DateFrom, DateTo)

Now that you have this table, you can do something like this:

select ItemID, Value, financial_years.DateFrom, financial_years.DateTo
from items
join financial_years
on (items.DateFrom between financial_years.DateFrom and financial_years.DateTo) or
   (items.DateTo between financial_years.DateFrom and financial_years.DateTo)
order by financial_years.DateFrom;