Turn quarterly data into monthly by repeating the quarterly rows by 3-CodePudding

I'm wondering how to repeat each of these rows 3 times to get them from Quarters into months.

I need to repeat the same values in the first 2 columns but depending on the quarter in the third column I would need the other months in that quarter, i.e for the first row '31/01/2021' and '28/02/2021'

So desired output would look like:

CodePudding user response：

WITH TABLE_DATA(CODE,VAL,DATED)AS
(
  SELECT 'R01',777,'2021-03-31' UNION ALL
  SELECT 'R01',833,'2021-06-30' UNION ALL
  SELECT 'R01',882,'2021-09-30'
)
SELECT D.CODE,D.VAL,CAST(DATEADD(MONTH,-X.PLACEHOLDER,D.DATED)AS DATE)AS DATED,X.PLACEHOLDER
FROM TABLE_DATA AS D
CROSS JOIN
(
  SELECT 0 AS PLACEHOLDER
    UNION ALL
  SELECT 1
   UNION ALL
  SELECT 2
)X
ORDER BY D.CODE,DATED;

Could you please check if this query is suitable for you. TABLE_DATA is an example of data you have provided

CodePudding user response：

Another option is via a CROSS APPLY

Select A.Code
      ,A.Value 
      ,B.Date
 From YourTable A
 Cross Apply ( values (EOMonth(dateadd(MONTH,-2,A.Date)))
                     ,(EOMonth(dateadd(MONTH,-1,A.Date)))
                     ,(EOMonth(dateadd(MONTH,-0,A.Date)))
             ) B(Date)

Results