Generate data upto a certain time using SQL Server and implementing in Mondrian cube
In case of some scenarios in which the data is very huge i.e. it is having data of upto last 10-20 years. Now the user wants only to see those data which are very recent lets say for example, the user wants to see the Sales information only for last 3 months from the current date.
Example of generating data for the last 3 months from current date
Table Name: Sales
Date Column: datecolumn
Query:
Now the above would generate the data for the last months of data from the current date/last transaction date. The DATEADD(datepart,number,date) Function returns the date with the specified number added to the date part of the date column. The DATEDIFF(datapart,startdate,enddate) returns the count (signed integer) of the specified datepart boundaries crossed between the specified startdate and enddate. Here startdate is the datecolumn present in the Sales table and the end date is the systemdate. The result would give you data exactly for last 3 months.
Alternatively, you can change it to yearwise and also on day basis. This helps in analytics purposes where the user doesnt want to see the whole table and requires data only for certain timeframe.
Use the above snippet and have fun with altering the data.
Regards,
Nitish Kumar Mishra
Best Open Source Business Intelligence Software Helical Insight is Here
