sql split yearly record into 12 monthly records-CodePudding

I am trying to use common table expression to split an yearly record into 12 monthly records. I have to do it for next 20 years records . That means 20 rows into 600 rows (20*12=600 records). What is the best way to do it. Can anyone help with an efficient way to do it. Using a single table as shown below. Year 0 means current year so it should split into remaining months and year=1 means next year onward it should split into 12 (months) records

id  year value
1   0   3155174.87
1   1   30423037.3
1   2   35339631.25

CodePudding user response：

You may want to consider a CROSS JOIN and a Numbers/Tally Table (in this case an ad-hoc Tally Table.

Example

Declare @YourTable Table ([id] int,[year] int,[value] decimal(15,2))  
Insert Into @YourTable Values 
 (1,0,3155174.87)
,(1,1,30423037.3)
,(1,2,35339631.25)
 
Select A.*
      ,B.Mnth
      ,Portion = Value/12 
 From @YourTable A
 Cross Join (
                Select Top 12 Mnth=Row_Number() Over (Order By (Select NULL)) 
                 From  master..spt_values n1
            ) B
 Where Year>0
       or (year=0 and Mnth>=month(getdate()))

Results

CodePudding user response：

You can simply join onto a list of months, and then use a bit of arithmetic to split the Value

SELECT
  t.Id,
  t.Year,
  v.Month,
  Value = t.Value / CASE WHEN t.Year = 0 THEN 13 - MONTH(GETDATE()) ELSE 12 END
FROM YourTable t
JOIN (VALUES
  (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)
) v(Month) ON t.year > 0 OR v.Month >= MONTH(GETDATE());

db<>fiddle