SQLTeam.com | Weblogs | Forums

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

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.
image

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';

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;

image

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

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

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'.

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   )

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.

My dear friend :expressionless: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

:scream::scream::cold_face::cold_face: sorry, I am blind!