3

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/1/1997 – 1/31/2997
  2. 2/1/1997 – 2/28/1997
  3. 3/1/1997 – 3/31/1997
  4. ….
  5. 1/1/1998 – 1/31/1998
  6. 2/1/1988 – 2/28/1998
  7. …
  8. Through 9/30/2017

Thanks

Feivel
  • 507
  • 8
  • 15

1 Answers1

1

I believe this should help you. First I create a counter variable that's equal to the total months you need to evaluate, in this case you specified January 1997 to September 2017. Next I create the start and end dates based on that counter. Doing it this way allowed me to work around having special logic for the last day of every month (and leap years). We throw it in a while loop based on the counter and use except logic to get any results that don't match.

Edit: I wanted to improve this so that there weren't four places where dates are hard coded, but since we know the dates for this particular question, I wanted to keep it as clean as possible and not add more date manipulation functions.

declare @StartDate date, @EndDate date, @counter int = (select datediff(mm, 
'1/1/1997', '9/30/2017'));
while @counter >= 0
begin
    set @StartDate = dateadd(mm, @counter, '1/1/1997');
    set @EndDate = dateadd(mm, @counter, '1/31/1997');

    --Finding results in the original that are not in the new
    /*
    Select x, y, z
    FROM report.fnReportDealCore_original(@StartDate, @EndDate, -1)
    EXCEPT
    Select x, y, z
    FROM report.fnReportDealCore(@StartDate, @EndDate, -1)
    */

    --Finding results in the new that are not in the original
    /*
    Select x, y, z
    FROM report.fnReportDealCore(@StartDate, @EndDate, -1)
    EXCEPT
    Select x, y, z
    FROM report.fnReportDealCore_original(@StartDate, @EndDate, -1)
    */

    set @counter -= 1;
end
Padwan
  • 331
  • 1
  • 9