Hi all. I am trying to create a view using to three queries below and I get the error message Views or functions are not allowed on temporary tables. Is there a way to do that or is there a way to combine the three queries below so I don't have to use a temp table so I create a view?

Thanks!

--Query 1

SELECT * INTO #MOVEMENTS FROM [GW_DW].[dbo].[DimStatusHistory] dWHERE TransferFromToProgram<>'' AND d.Status=12;

There is some major flawed logic in query 2. You do LEFT JOIN against #Movement table, but doesn't filter nor display any columns from that table.Hence, there is absolutely no need to do the LEFT JOIN at all!

I would guess that these two functions (ECMS.dbo.FN_PRIOR_EFFECT_DT_FOR_STATUS and ECMS.dbo.FN_NEXT_EFFECT_DT_FOR_STATUS) are the ones that slows the code down.Try to comment these two lines out.Or hardwire two arbitrary dates as PRIOR_EFFECT_DT and NEXT_EFFECT_DT, to see what happens. My estimate is that the code will run MUCH faster.

The reason for this is that scalar functions doesn't run that well in SQL Server.