Produce a grouping based on if a period occurs between a start and end date

Question

There are many examples coming up on StackOverflow, but they all seem to slightly different questions - or at least the ones I can fine.

I have a table of items with a start date and and end date occurring sometime in the next 10 years. The end result I am trying to achieve is which of these items occur in which quarter?

Here's my source

item   |StartDate    |  EndDate     |
Item#1 | 2022-01-04  |  2022-07-01  |
Item#2 | 2022-04-01  |  2022-06-20  |

So extracting the quarter is easy with

DATEPART(QUARTER, StartDate)
DATEPART(QUARTER, FinishDate)

and I have a table of all the quarters, as in a quarter is defined by a start date and an end date.

QuarterLabel | Start      |  End      |
Quarter 1    | 2022-01-01 | 2022-03-31|
Quarter 2    | 2022-04-01 | 2022-08-01|
etc

But how do I determine whether that item is just in quarter 1? or maybe spans quarter 1 and 2?

My goal is to create something like this:

Client       |  Quarter 1   | Quarter 2        | Quarter 3 | ....
-----------------------------------------------------------------------
CustomerA    | Item#1       |  Item#1          |           |
CustomerB    |              |  Item#1, Item#2  |  Item#2   |

My best guess, which got me nowhere:

SELECT 
Item,
CAST(DATEPART(YEAR, StartDate) as float) +
CASE
    WHEN DATEPART(QUARTER, StartDate) <3 THEN cast((DATEPART(QUARTER, StartDate) + 2) as float) /10
    WHEN DATEPART(QUARTER, StartDate) >2 THEN cast((DATEPART(QUARTER, StartDate) - 2) as float) /10
    ELSE 0
END as StartQuarterNumber,

CAST(DATEPART(YEAR, FinishDate) as float) +
CASE
    WHEN DATEPART(QUARTER, FinishDate) <3 THEN cast((DATEPART(QUARTER, FinishDate) + 2) as float) /10
    WHEN DATEPART(QUARTER, FinishDate) >2 THEN cast((DATEPART(QUARTER, FinishDate) - 2) as float) /10
    ELSE 0
END as FinishQuarterNumber
FROM table1

MS SQL Server 11.0.7

Answer 1

Here we use a recursive CTE to make a list of years from year(min(start_date)) to year(max(end_date)). We then join this list to the table events and group by year, using string_aggto list the projects active during each quarter of that year.
I have prefered to present the 4 quarters as columns and the years as row for 2 reasons :

• It seems much clearer as presentation. If the data extends of several years you could have dozens of quarters.
• It avoids having a dynamique number of columns.

You speak of clients but their are no clients in your sample data. We could easily add client in the list of fields selected and in the group by, which would create a line per client per year that they have a project.

CREATE TABLE events (
  ID VARCHAR(10),
  start_date DATE,
  end_date DATE);
INSERT INTO events VALUES
('item#1','20190101','20201231'),
('item#2','20200315','20200601'),
('item#3','20200706','20200802'),
('item#4','20201201','20201230'),
('item#5','20200601','20210120');
GO

5 rows affected

with years as
  (select 
     year(max(end_date)) fyear,
     year(min(start_date)) syear
   from events
  union all
   select
     fyear,
     syear + 1
   from years
   where syear < fyear
  )
select
  y.syear "Year" ,
   string_agg( case when start_date <= concat(syear,'0331') 
                and end_date>= concat(syear,'0101')
                then e.id end , ',') Q1,
   string_agg( case when start_date <= concat(syear,'0630') 
                and end_date>= concat(syear,'0401')
                then e.id end , ',') Q2,
   string_agg( case when start_date <= concat(syear,'0930') 
                and end_date>= concat(syear,'0701')
                then e.id end , ',') Q3,
   string_agg( case when start_date <= concat(syear,'1231') 
                and end_date>= concat(syear,'1001')
                then e.id end , ',') Q4                
from
  years y
join events e
  on year(end_date) >= syear
  and year(start_date) <= syear
group by syear

GO

Year | Q1            | Q2                   | Q3                   | Q4                  
---: | :------------ | :------------------- | :------------------- | :-------------------
2019 | item#1        | item#1               | item#1               | item#1              
2020 | item#1,item#2 | item#1,item#2,item#5 | item#1,item#3,item#5 | item#1,item#4,item#5
2021 | item#5        | null                 | null                 | null                

db<>fiddle here