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

Question

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

Please don’t link to images, add all information to your question as editable text — NickW, Jun 30 '23 at 16:22

score 1 · Answer 1 · answered Jun 30 '23 at 19:10

1

Is your table structure already given? If not, what about

  DECLARE @AnyRecords TABLE (
    [FiscalYear] INT,
    [Month] NUMERIC(2,0),
    [Type] VARCHAR(2),
    [Val] NUMERIC(19,0));

where type might by 'a' for actual and 'f' for forecast. The corrected entries would be selected like

select * from @AnyRecords
where (year < 2023 or (year = 2023 and month < 6) and 'a' = type) or (year > 2023 or (year = 2023 and month >= 6) and 'f' = type)

Then you would need to translate your rows to columns as described here: MySQL - Rows to Columns

answered Jun 30 '23 at 19:10

NIC

69
3

nothing specific was given. I was creating something on my own. I will try this structure you have given now. I'm working on t-sql, is this equivalent to pivot in ssms ? – brickanalyst Jun 30 '23 at 20:53
FY DEC JAN FEB MAR AP MAY JUN JUL AUG SEP OCT NOV 2022 0 0 0 0 0 0 0 0 0 0 0 0 2023 0 0 0 0 0 0 10 20 30 30 40 40 2024 40 50 50 60 70 70 80 90 100 110 150 200 I couldn't add photo here, it's really hard to explain by texting but, considering what I want to see is : till august 2023 (june included because today is in june) all records should come from actuals ( to see easily i made them 0 ) and august and later months would be projections ( to see easily i gave them random numbers ) @NIC – brickanalyst Jun 30 '23 at 21:11

score 0 · Accepted Answer · answered Jun 30 '23 at 22:42

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.

NIC thank you your approach made me try this pivoting step by step and I come up with a solution ***** ( I will share here ) @PonderStibbons also thank you I see how easy when using pivot - i will look code again to understand better and I really like the link you shared, you can test online - its 'perfect — brickanalyst, Jul 03 '23 at 19:17

brickanalyst · Answer 3 · 2023-07-12T17:49:37.123

--We need two tables, one for actual, one for forecast records
DECLARE @ActualRecords TABLE (
[FiscalYear] INT,
[Month] NUMERIC(2,0),
[Type] VARCHAR(10),
[Value] NUMERIC(19,0)); 
    
DECLARE @ForecastRecords TABLE (
[FiscalYear] INT,
[Month] NUMERIC(2,0),
[Type] VARCHAR(10),
[Value] NUMERIC(19,0));
    
INSERT INTO @ForecastRecords VALUES ('2022', 12,'forecast',120);
INSERT INTO @ForecastRecords VALUES ('2023', 1,'forecast',110);
INSERT INTO @ForecastRecords VALUES ('2023', 2,'forecast',100);
INSERT INTO @ForecastRecords VALUES ('2023', 3,'forecast',90);
INSERT INTO @ForecastRecords VALUES ('2023', 4,'forecast',80);
INSERT INTO @ForecastRecords VALUES ('2023', 5,'forecast',70);
INSERT INTO @ForecastRecords VALUES ('2023', 6,'forecast',70);
INSERT INTO @ForecastRecords VALUES ('2023', 7,'forecast',60);
INSERT INTO @ForecastRecords VALUES ('2023', 8,'forecast',50);
INSERT INTO @ForecastRecords VALUES ('2023', 9,'forecast',40);
INSERT INTO @ForecastRecords VALUES ('2023', 10,'forecast',80);
INSERT INTO @ForecastRecords VALUES ('2023', 11,'forecast',70);
INSERT INTO @ForecastRecords VALUES ('2023', 12,'forecast',60);
INSERT INTO @ForecastRecords VALUES ('2024', 1,'forecast',50);
INSERT INTO @ForecastRecords VALUES ('2024', 2,'forecast',40);
INSERT INTO @ForecastRecords VALUES ('2024', 3,'forecast',90);
INSERT INTO @ForecastRecords VALUES ('2024', 4,'forecast',80);
INSERT INTO @ForecastRecords VALUES ('2024', 5,'forecast',80);
INSERT INTO @ForecastRecords VALUES ('2024', 6,'forecast',70);
INSERT INTO @ForecastRecords VALUES ('2024', 7,'forecast',60);
INSERT INTO @ForecastRecords VALUES ('2024', 8,'forecast',50);
INSERT INTO @ForecastRecords VALUES ('2024', 9,'forecast',40);
INSERT INTO @ForecastRecords VALUES ('2024', 10,'forecast',80);
INSERT INTO @ForecastRecords VALUES ('2024', 11,'forecast',70);
INSERT INTO @ForecastRecords VALUES ('2024', 12,'forecast',60);

INSERT INTO @ActualRecords VALUES ('2022', 12,'actual',0);
INSERT INTO @ActualRecords VALUES ('2023', 1,'actual',0);
INSERT INTO @ActualRecords VALUES ('2023', 2,'actual',0);
INSERT INTO @ActualRecords VALUES ('2023', 3,'actual',0);
INSERT INTO @ActualRecords VALUES ('2023', 4,'actual',0);
INSERT INTO @ActualRecords VALUES ('2023', 5,'actual',0);
INSERT INTO @ActualRecords VALUES ('2023', 6,'actual',0);
INSERT INTO @ActualRecords VALUES ('2023', 7,'actual',0);
INSERT INTO @ActualRecords VALUES ('2023', 8,'actual',0);
INSERT INTO @ActualRecords VALUES ('2023', 9,'actual',0);
INSERT INTO @ActualRecords VALUES ('2023', 10,'actual',0);
INSERT INTO @ActualRecords VALUES ('2023', 11,'actual',0);
INSERT INTO @ActualRecords VALUES ('2023', 12,'actual',0);
INSERT INTO @ActualRecords VALUES ('2024', 1,'actual',0);
INSERT INTO @ActualRecords VALUES ('2024', 2,'actual',0);
INSERT INTO @ActualRecords VALUES ('2024', 3,'actual',0);
INSERT INTO @ActualRecords VALUES ('2024', 4,'actual',0);
INSERT INTO @ActualRecords VALUES ('2024', 5,'actual',0);
INSERT INTO @ActualRecords VALUES ('2024', 6,'actual',0);
INSERT INTO @ActualRecords VALUES ('2024', 7,'actual',0);
INSERT INTO @ActualRecords VALUES ('2024', 8,'actual',0);
INSERT INTO @ActualRecords VALUES ('2024', 9,'actual',0);
INSERT INTO @ActualRecords VALUES ('2024', 10,'actual',0);
INSERT INTO @ActualRecords VALUES ('2024', 11,'actual',0);
INSERT INTO @ActualRecords VALUES ('2024', 12,'actual',0);

--we have inserted dummy values above
DECLARE @currentMonth INT = CAST(MONTH(GETDATE()) AS INT);
DECLARE @currentYear INT = CAST(YEAR(GETDATE()) AS INT);
--we create variables to capture date part

WITH firstStage ([FiscalYear],[Month],[Type],[Value])
AS
(
SELECT * FROM @ActualRecords
WHERE FiscalYear < @currentYear OR (FiscalYear = @currentYear AND Month <= @currentMonth)
UNION
SELECT * FROM @ForecastRecords
WHERE FiscalYear > @currentYear OR (FiscalYear = @currentYear AND Month > @currentMonth)
) 
SELECT * 
INTO ##TEMP
FROM firstStage;
--SELECT * FROM firstStage;

SELECT * FROM 
(
    SELECT * FROM ##TEMP
) t

WITH secondStage AS (
    SELECT *,
        CASE WHEN Month = 12 THEN [Value] END AS 'December',
        CASE WHEN Month = 1 THEN [Value] END AS 'January',
        CASE WHEN Month = 2 THEN [Value] END AS 'February',
        CASE WHEN Month = 3 THEN [Value] END AS 'March',
        CASE WHEN Month = 4 THEN [Value] END AS 'April',
        CASE WHEN Month = 5 THEN [Value] END AS 'May',
        CASE WHEN Month = 6 THEN [Value] END AS 'June',
        CASE WHEN Month = 7 THEN [Value] END AS 'July',
        CASE WHEN Month = 8 THEN [Value] END AS 'August',
        CASE WHEN Month = 9 THEN [Value] END AS 'September',
        CASE WHEN Month = 10 THEN [Value] END AS 'October',
        CASE WHEN Month = 11 THEN [Value] END AS 'November'
    FROM ##TEMP
) 
SELECT * INTO ##TEMP1
FROM secondStage

SELECT * FROM ##TEMP1

WITH thirdStage AS (
    SELECT
        [FiscalYear],
        SUM([January]) as [Jan], SUM([February]) as [Feb], SUM([March]) as [Mar], SUM([April]) as [Apr],
        SUM([May]) as [May], SUM([June]) as [Jun], SUM([July]) as [Jul], SUM([August]) as [Aug], 
        SUM([September]) as [Sep], SUM([October]) as [Oct], SUM([November]) as [Nov], SUM([December]) as [Dec]
    FROM ##TEMP1
    GROUP BY [FiscalYear]
) SELECT * INTO ##TEMP2
FROM thirdStage

--DROP TABLE IF EXISTS ##TEMP2
SELECT * FROM ##TEMP2

WITH finalStage AS (
    SELECT
        [FiscalYear],
        COALESCE([Jan], 0) as [Jan],
        COALESCE([Feb], 0) as [Feb],
        COALESCE([Mar], 0) as [Mar],
        COALESCE([Apr], 0) as [Apr],
        COALESCE([May], 0) as [May],
        COALESCE([Jun], 0) as [Jun],
        COALESCE([Jul], 0) as [Jul],
        COALESCE([Aug], 0) as [Aug],
        COALESCE([Sep], 0) as [Sep],
        COALESCE([Oct], 0) as [Oct],
        COALESCE([Nov], 0) as [Nov],
        COALESCE([Dec], 0) as [Dec]
    FROM ##TEMP2
) SELECT * FROM finalStage
--in the end we can see actuals till currentmont and year
--and we can see projections for future date

Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). — Community, Jul 07 '23 at 08:12

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

3 Answers3