Average Last available days start of the month and Forecast Average value till end of month

Sameer · August 20, 2022, 2:46pm

Hi Experts!
Your support required to get the forecast values based on average of last available month values and continues forecasting till end of the month. Suppose i have 3days data available of the month the sql make an average of last three days and keep the average till last date of the month. If the data inserted on 4day of the month then take last 4days of average and continues till end of the month.

My try I am able to get the averages of last 3days ,
Required sqlquery to look every time first date of the month and calculate forcasts for next days till end of the month .
attaching current results and required results

Current Results from sample data and Sqlquery.

Required results in table we have last 4days avaiable next dates are foracast dates and values.

 DROP TABLE IF EXISTS #Table;
GO	
 SELECT  StartDate = CONVERT(DATETIME     ,v.StartDate)
        ,ItemName  = CONVERT(VARCHAR(1000),v.ItemName)
        ,Data1     = CONVERT(FLOAT        ,v.Data1)
        ,Data2     = CONVERT(FLOAT        ,v.Data2)
   INTO #Table
   FROM (VALUES
         ('1-Oct-2021','ItemName1',1336.9 ,0.85623)
        ,('1-Oct-2021','ItemName2',318.62 ,0.31913)
        ,('1-Oct-2021','ItemName3',40     ,50     )  
		,('2-Oct-2021','ItemName1',10     ,20     )
        ,('2-Oct-2021','ItemName2',11     ,21     )
        ,('2-Oct-2021','ItemName3',12     ,22     )  
		,('3-Oct-2021','ItemName1',10     ,20     )
        ,('3-Oct-2021','ItemName2',11     ,21     )
        ,('3-Oct-2021','ItemName3',12     ,22     )
		,('4-Oct-2021','ItemName1',100     ,220     )
        ,('4-Oct-2021','ItemName2',112     ,221     )
        ,('4-Oct-2021','ItemName3',124     ,422     )
        )v(StartDate,ItemName,Data1,Data2)


SELECT * FROM #Table 
order by StartDate

--declare @avgdays as int 
--set @avgdays =3

SELECT *,
       [Data1AvgLast3days] =  avg(Data1) OVER(PARTITION BY ItemName ORDER BY StartDate ROWS BETWEEN 3 PRECEDING AND CURRENT ROW),

	   [Data2AvgLast3days] =  avg(Data2) OVER(PARTITION BY ItemName ORDER BY StartDate ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)

FROM #Table where ItemName='ItemName1';

Sameer · August 22, 2022, 2:30pm

Hi Experts !
I Manage to get my requirement working fine for me below is the test data, have new added data for date 5th ,6th and 7th to test ( if keep the values null on date 5th then the previous row average filling the data1 and Data2 fields from previous day which is OK). Every time what ever date passed in stored procedure the data start to retrieve from 1st date of the month.
Only remaining thing support required the last available inserted values keep repeating on next days till end of the month ( since we don't have rows available in database after 7-OCT-2022 thats why the data is not repeating can some one add calender function to get the dates from calender table or function? ) . We can reach to end of the month by using @EndMth declaration comment out area to change StartDate<=@ProcEndDate to StartDate<=@EndMth but issue is no rows avaiable in the database after 7-Oct-2021.

declare @ProcEndDate datetime='7-oct-2021'   --Main Passing date
declare @ProcSDate datetime=DATEADD(month, DATEDIFF(month, 0,  @ProcEndDate), 0)  --Start date of the month ( Passing Parameter   @ProcEndDate to pass any date of the month it reaches to 1st date of the month)
----declare @EndMth datetime=DATEADD(d, -1, DATEADD(m, DATEDIFF(m, 0, @ProcEndDate) + 1, 0)) ---End date of Month
declare @vNoOfRows int = right('0' + cast (datepart(day,@ProcEndDate) as nvarchar),2)   ---Converting Main passed @ProcEndDate to integer for the usage in Below Average Sql statement
declare @SqlScript nvarchar(max)
set @SqlScript = 'Select *,
                      [AvgData1]=avg(Data1) over(partition by ItemName order by StartDate rows between ' + convert(nvarchar(max), @vNoOfRows) + ' preceding and current row), 
                      [AvgData2]=avg(Data2) over(partition by ItemName order by StartDate rows between ' + convert(nvarchar(max), @vNoOfRows) + ' preceding and current row)
 from #Table
 where ItemName=''ItemName1''  
 and StartDate>=@ProcSDate and  StartDate<=@ProcEndDate
 order by ItemName,StartDate

 OPTION (OPTIMIZE FOR ( @ProcSDate UNKNOWN,  @ProcEndDate UNKNOWN
					  )
		)'
EXECUTE sp_executesql @SqlScript, N' @ProcSDate datetime,@ProcEndDate datetime', @ProcSDate,@ProcEndDate  with RECOMPILE;

yosiasz · August 22, 2022, 6:34pm

very confusing post. could you please provide sample data and the expected result?

create table #sample
insert into #sample

expected result

create table #expected
insert into #expected

Sameer · August 22, 2022, 7:05pm

I think sample data every thing provided also excepted result too.. did you go through that?

yosiasz · August 22, 2022, 7:13pm

sure did. I ran the code you provided and I get the following error.

Msg 208, Level 16, State 0, Line 3
Invalid object name '#Table'.

Sameer · August 23, 2022, 12:42am

Same above post 1 code copied and paste in my SSMS working fine having no issues...

for retrieve the same data result in Post 2 till 7-oct-2021 please add the below in the insert statement.

,('5-Oct-2021','ItemName1',Null     ,  null   )
,('6-Oct-2021','ItemName1',50     ,  45   )|
,('7-Oct-2021','ItemName1',null     ,  null   )

yosiasz · August 23, 2022, 12:44am

Yeah works for you because you have that temp table, we dont have it. It is an image of test data and we cant work with that. Please post that DDL and DML.

Sameer · August 23, 2022, 1:00am

My dear friend every thing is available at post no 1 i am re- pasting from it for you from Post 1.
Required results in Post1 having screenshot.
I manage to get my requirement stored procedure in Post 2.

DROP TABLE IF EXISTS #Table;
GO	
 SELECT  StartDate = CONVERT(DATETIME     ,v.StartDate)
        ,ItemName  = CONVERT(VARCHAR(1000),v.ItemName)
        ,Data1     = CONVERT(FLOAT        ,v.Data1)
        ,Data2     = CONVERT(FLOAT        ,v.Data2)
   INTO #Table
   FROM (VALUES
         ('1-Oct-2021','ItemName1',1336.9 ,0.85623)
        ,('1-Oct-2021','ItemName2',318.62 ,0.31913)
        ,('1-Oct-2021','ItemName3',40     ,50     )  
		,('2-Oct-2021','ItemName1',10     ,20     )
        ,('2-Oct-2021','ItemName2',11     ,21     )
        ,('2-Oct-2021','ItemName3',12     ,22     )  
		,('3-Oct-2021','ItemName1',10     ,20     )
        ,('3-Oct-2021','ItemName2',11     ,21     )
        ,('3-Oct-2021','ItemName3',12     ,22     )
		,('4-Oct-2021','ItemName1',100     ,220     )
        ,('4-Oct-2021','ItemName2',112     ,221     )
        ,('4-Oct-2021','ItemName3',124     ,422     )
        ,('5-Oct-2021','ItemName1',Null     ,  null   )   --- added in post 2
        ,('6-Oct-2021','ItemName1',50     ,  45   )      --- added in post 2
        ,('7-Oct-2021','ItemName1',null     ,  null   )    --- added in post 2
        )v(StartDate,ItemName,Data1,Data2)


SELECT * FROM #Table 
order by StartDate

yosiasz · August 23, 2022, 1:13am

sorry, I am blind!