I have created a database view like this:
CREATE VIEW as View1
Select FUNCTION1(Dept1), FUNCTION1(Dept2), FUNCTION2(Total age)
Here FUNCTION1() calculates age of any given department and FUNCTION2() calculates age of all departments.
If I query following then it calculates correct values and return results in seconds and total of FUNCTION1(dept1) and FUNCTION1(dept2) equals to FUNCTION2(total Depts age) time.
Select * from View1 where Id='123'
Obviously requirement is to calculate the data for past 1 year which are almost more than 0.1 million records and it takes around 50 minutes to calculate all the row values.
So I decided to put all the data in a table from DB view so whenever user extract report it should return data from table not from view to avoid calculations at runtime.
The issue comes here when job takes 50 minutes to insert data into table from view then there is difference of 25 minutes in each row. Sum of both calculated columns is 25 minutes more than total Dept age.
In functions I use GETDATE() function whenever I need current datetime.