Link to home
Start Free TrialLog in
Avatar of saratcm
saratcmFlag for India

asked on

Creating view on a SQL script that returns data in temp table

Here is the SQL script that return over 200k records stored in the temporary table called ##Result1. I want to create a view on this script so that view will return the data from the temp table. so in the end it must be only one select statement from the view that would return all the data.

SQL Script:
Create Table ##Temp1(ID INT Identity(1,1),Date DATETIME)
Create Table ##Result1(Date DATETIME,BranchID INT,Planning INT,Scenario INT,TypeofSale INT,LoanStatus INT,TotalLoans INT,TotalBalance MONEY)
Insert into ##Temp1
SELECT TOP 10 Date FROM DimTime
WHERE Date BETWEEN (SELECT MIN(CalDate) FROM LoanInstance) AND (SELECT MAX(CalDate) FROM LoanInstance) ORDER BY Date DESC
Declare @Counter INT
SET @Counter = 1
WHILE (@Counter <= (SELECT MAX(ID) FROM ##Temp1))
BEGIN
INSERT INTO ##Result1
SELECT (SELECT DATE FROM ##Temp1 WHERE ID = @Counter) AS Date,
L1.BranchID, L1.Planning, L1.Scenario, L1.TypeofSale, L1.LoanStatus,
COUNT(loaninstanceid) AS [TotalLoans], SUM(CurrentBalance) AS [TotalBalance]
FROM LoanInstance L1
WHERE  L1.LOANSTATUS IN (5,6,7,8,9) AND CALDATE =
(SELECT MAX(CALDATE) FROM LoanInstance L2 WHERE L2.Loaninstanceid = L1.LoanInstanceid  
AND L2.CalDate <= (SELECT Date FROM ##Temp1 WHERE ID = @Counter) )
GROUP BY L1.BranchID, L1.Planning, L1.Scenario, L1.TypeofSale, L1.LoanStatus
SET @Counter = @Counter + 1
END
SELECT * FROM ##Result1 ORDER BY Date
DROP TABLE ##Temp1
DROP TABLE ##Result1

Thanks. This is real urgent if somebody can help us out real quick. thanks alot.
ASKER CERTIFIED SOLUTION
Avatar of Reza Rad
Reza Rad
Flag of New Zealand image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of saratcm

ASKER

Thanks!!