Recently, we've done some optimizations to the functions and now the BI Manager has tasked me with a request to compare the result of the 2 functions as below making sure the row counts match, along with the exact values of each field. In the same request he is expecting this to be a while loop that will loop all the YTD ranges dating back 20 years. (not sure if this is really practical in order to test the validity). However, the while loop is not a mandate for the same. Is there a way to compare the result of the 2 functions without hard coding the ranges and automating the same like for e.g declaring a variable and then based on the date range calculating the next date range etc without the user intervention.
Last but not the least a script that would validate there are no differences in the results of both the functions which means that the optimization did work. I am thinking of using except clause but I am not sure if it would work with functions. Also, if that won't work what else would be a possible way of doing it.
I would really appreciate for all the help. It is a big impact for the company if I could complete this task. However, I am not even sure if this is something that should be even handled by a DBA.
Functions Original Function: report.fnReportDealCore_original(@StartDate, @EndDate, -1)
New Optimized Function: report.fnReportDealCore(@StartDate, @EndDate, -1)
Example of Run Dates dating back 20 years
- 1/1/1997 – 1/31/2997
- 2/1/1997 – 2/28/1997
- 3/1/1997 – 3/31/1997
- 1/1/1998 – 1/31/1998
- 2/1/1988 – 2/28/1998
- Through 9/30/2017