Search code examples
sqldatetimeforecast

how to get actual records till today's month and forecast after today's month

above the query result I'm getting and this is the script below. I want to have a control of today's month so I can get before current month and after I think my approach is wrong in the beginning I couldn't figure it out

DECLARE @ActualRecords TABLE (
[FiscalYear] INT,
[Dec] NUMERIC(19,0),
[Jan] NUMERIC(19,0),
[Feb] NUMERIC(19,0),
[Mar] NUMERIC(19,0),
[Apr] NUMERIC(19,0),
[May] NUMERIC(19,0),
[Jun] NUMERIC(19,0),
[Jul] NUMERIC(19,0),
[Aug] NUMERIC(19,0),
[Sep] NUMERIC(19,0),
[Oct] NUMERIC(19,0),
[Nov] NUMERIC(19,0));
    
DECLARE @ForecastRecords TABLE (
[FiscalYear] INT,
[Dec] NUMERIC(19,0),
[Jan] NUMERIC(19,0),
[Feb] NUMERIC(19,0),
[Mar] NUMERIC(19,0),
[Apr] NUMERIC(19,0),
[May] NUMERIC(19,0),
[Jun] NUMERIC(19,0),
[Jul] NUMERIC(19,0),
[Aug] NUMERIC(19,0),
[Sep] NUMERIC(19,0),
[Oct] NUMERIC(19,0),
[Nov] NUMERIC(19,0));
    
INSERT INTO @ForecastRecords VALUES ('2022', 120,110,100,90,80,70,60,50,40,30,20,10);
INSERT INTO @ForecastRecords VALUES ('2023', 110,100,90,80,70,60,50,40,30,20,10,120);
INSERT INTO @ForecastRecords VALUES ('2024', 110,100,90,80,70,60,50,40,30,20,10,10);
INSERT INTO @ForecastRecords VALUES ('2025', 100,90,80,70,60,50,40,30,20,10,10,10);
INSERT INTO @ForecastRecords VALUES ('2026', 130,120,100,90,80,70,60,50,40,30,20,10);
INSERT INTO @ForecastRecords VALUES ('2027', 150,140,100,90,80,70,60,50,40,30,20,10);
INSERT INTO @ActualRecords VALUES ('2022', 0,0,0,0,0,0,0,0,0,0,0,0);
INSERT INTO @ActualRecords VALUES ('2023', 0,0,0,0,0,0,0,0,0,0,0,0);
INSERT INTO @ActualRecords VALUES ('2024', 0,0,0,0,0,0,0,0,0,0,0,0);
INSERT INTO @ActualRecords VALUES ('2025', 0,0,0,0,0,0,0,0,0,0,0,0);
INSERT INTO @ActualRecords VALUES ('2026', 0,0,0,0,0,0,0,0,0,0,0,0);
INSERT INTO @ActualRecords VALUES ('2027', 0,0,0,0,0,0,0,0,0,0,0,0);
    
SELECT * FROM @ActualRecords A 
WHERE FiscalYear < 2023 
UNION
SELECT * FROM @ForecastRecords F
WHERE FiscalYear >= 2023
Solution

  • If normalized version of tables proposed by @NIC is OK, but you want to keep forecast data and actual values in separate tables you can do this:

    select fiscalyear, [1] Jan, [2] Feb, [3] Mar, [4] Apr, [5] May, [6] Jun,
       [7] Jul, [8] Aug, [9] Sep, [10] Oct, [11] Nov, [12] Dec
from (
  select FiscalYear, Month, Val from actualrecords 
  where datefromparts (fiscalyear, month, 1) < getdate() 
  union all
  select FiscalYear, Month, Val from forecastrecords 
  where datefromparts (fiscalyear, month, 1) >= getdate() ) u
pivot (max(val)  
  for month IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12])) pv

    dbfiddle demo

    So first make union of both tables filtering months according to current date. Then pivot rows and name them. If you want December as first move it in select list. Tested in SQL Server, I did not use temporary tables, because it is not comfortable to work with them in dbfiddle.