Select year and quarters between two date columns in multiple rows in a table

Question

I have the following two tables

Table QuarterIssue

id	Acq_Date	Dis_Date
1	2015-08-07	2016-12-31
2	2018-01-05	2022-12-31
3	2019-06-21	2022-12-31

Table QuarterYear

Year	Quarter
2014	1
2014	2
2014	3
2014	4
2015	1
2015	2
2015	3
2015	4
2016	1

The QuarterYear table is a master table which has all the years and quarters.

I have a custom requirement to write a query to get the quarters between two dates from these following two tables. I have to select all the years and quarters between Acq_Date and Dis_Date for all "ids" in the QuarterIssue table. Basically my result has to be,

YEAR	Quarter
2015	3
2015	4
2016	1
2016	2
2016	3
2016	4
2018	1
2018	2
2018	3
2018	4
2019	1
2019	2
2019	3
2019	4
2020	1
2020	2
2020	3
2020	4
2021	1
2021	2
2021	3
2021	4
2022	1
2022	2
2022	3
2022	4

This is what I have so far,

SELECT [YEAR], [Quarter], ([YEAR] + CAST([Quarter] AS DECIMAL)/10) A FROM QuarterYear QY 
WHERE ([YEAR] + CAST([Quarter] AS DECIMAL)/10) 
between 
(SELECT top 1 YEAR(Acq_Date) + CAST(DATEPART(qq, Acq_Date) AS DECIMAL)/10 as ACQ FROM 
QuarterIssue) 
and 
(SELECT top 1 YEAR(Dis_Date) + CAST(DATEPART(qq, Dis_Date) AS DECIMAL)/10 as DIS FROM 
QuarterIssue)

As you can see right now I'm able to get the desired result of one of the "id" at a time. Is there any way we can use join or subquery to return the results I'm expecting?

Not every one defines a quarter the same way. Therefore, the QuarterYear table really also ought to have an additional DateTime column to identify the first day of each quarter. — Joel Coehoorn
– Joel Coehoorn, Commented Feb 23, 2023 at 22:02
@JoelCoehoorn yes I understand. My actual application data has all the information. I just made this up for stackoverflow. — user1890098
– user1890098, Commented Feb 26, 2023 at 11:45

Aaron Bertrand · Accepted Answer · 2023-02-24 16:56:14Z

1

Here's a way to do it without the DISTINCT, if you're into that kind of thing (DISTINCT is often a very inefficient way - by means of a distinct sort - to hide the fact that a join returned too many rows because multiple rows matched).

Basically, we just convert each quarter from your master table into a start and end date, and then check for rows in the issue table that overlap (and I describe the overlap pattern here).

;WITH QuarterRange AS
(
  SELECT Year, Quarter, 
    RangeStart = DATEADD(MONTH, 3*(Quarter-1), 
                 DATEFROMPARTS(Year, 1, 1))
  FROM dbo.QuarterYear
)
SELECT Year, Quarter 
FROM QuarterRange AS qr
WHERE EXISTS
(
  SELECT 1 FROM dbo.QuarterIssue AS qi
    WHERE qi.Dis_Date >= qr.RangeStart
      AND qi.Acq_Date <  DATEADD(MONTH, 3, qr.RangeStart)
)
ORDER BY Year, Quarter;

Working db<>fiddle example

edited Feb 24, 2023 at 16:56

answered Feb 23, 2023 at 21:56

Aaron Bertrand

282k37 gold badges469 silver badges468 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Select year and quarters between two date columns in multiple rows in a table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related