Let me just explain my problem with a simple example -
I have this table -
Now, this table is queried with a date range parameter. The result will be sum of value for each Type for that date range. Like if I query for From = "12/1/2016" and To = "12/2/2016", the result will be -
Now, Currently I am querying the table using via SSRS and then do the calculations IN SSRS and display. But this takes a lot of time since the dataset is very huge and there are lot of other calculations as well.
I want to have a materialized view which can have a snapshot of the data at midnight and pre-calculate all the aggregations, so that while querying using date range, the output will be much faster.
I can calculate the aggregations if only say for all the different Types and save in MV, But how do I do it for Dates?
Thanks in advance!