Listing all the months within a quarter using the Year and Quarter column-CodePudding

I have put together a code which checks whether the string begins with Q1, Q2, Q3 or Q4, and if found it should display the start of that quarter.


CREATE TABLE #Test (
  name VARCHAR (20) NOT NULL,
);

INSERT INTO #Test
    (name) 
VALUES 
    ('Q324'),
    ('Q224')

SELECT 
    name 
    ,[Year] = '20'   RIGHT(name,2)
    ,[Quarter] = CASE 
                    WHEN LEFT(name,2) = 'Q1' THEN '01'
                    WHEN LEFT(name,2) = 'Q2' THEN '04'
                    WHEN LEFT(name,2) = 'Q3' THEN '07'
                    WHEN LEFT(name,2) = 'Q4' THEN '10'
                    ELSE NULL
                END
from #Test

Output table:

How do I now go about displaying all the months within those quarters?

Expected Output:

Name  Year   Quarter  MonthYear
Q324  2024     03      Jul-24
Q324  2024     03      Aug-24
Q324  2024     03      Sep-24

CodePudding user response：

One method would be to use a VALUES table construct to add 0-2 months to the date, and then format it into the value you want:

WITH CTE AS(
    SELECT name 
          ,[Year] = '20'   RIGHT(name,2)
          ,[Quarter] = CASE 
                          WHEN LEFT(name,2) = 'Q1' THEN '01'
                          WHEN LEFT(name,2) = 'Q2' THEN '04'
                          WHEN LEFT(name,2) = 'Q3' THEN '07'
                          WHEN LEFT(name,2) = 'Q4' THEN '10'
                          ELSE NULL
                      END
    from #Test)
SELECT C.[Name],
       C.[Year],
       C.[Quarter],
       CONCAT(LEFT(DATENAME(MONTH,DATEFROMPARTS(C.[Year], C.[Quarter]   V.I,1)),3),'-',Year) AS MonthYear
FROM CTE C
     CROSS APPLY (VALUES(0),(1),(2))V(I)
ORDER BY C.Year,
         C.Quarter,
         V.I;

Note that this could very likely be made more succinct, but I incorporated your existing code, rather than completely rewriting it.

CodePudding user response：

You can join it with an inline virtual table of numbers to add

SELECT 
  name,
  v1.Year,
  [Quarter] = FORMAT(v1.Month, '00'),
  MonthYear = FORMAT(DATEFROMPARTS(v1.Year, v1.Month   v2.MonthsToAdd, 1), 'MMM-yy')
from #Test t
CROSS APPLY (VALUES(
    '20'   RIGHT(name,2),
    CASE LEFT(name,2)
        WHEN 'Q1' THEN 1
        WHEN 'Q2' THEN 4
        WHEN 'Q3' THEN 7
        WHEN 'Q4' THEN 10
    END
)) v1(Year, Month)
CROSS APPLY (VALUES
    (0),(1),(2)
) v2(MonthsToAdd);

db<>fiddle

Note the use of CROSS APPLY to be able to access the calculated values