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

|improve this question

closed as too broad by Erik Darling, mustaccio, dezso, Michael Green, McNets Oct 6 '17 at 8:45

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 1
    What "should be handled by a DBA" will vary greatly. If it falls outside the set of skills required for the job, or if it will interfere with you completing your normal duties, then it may not be unreasonable to at least note that to your boss. Personally, if I have time (or if the need is high enough to supersede my normal responsibilities), I'd generally be willing to provide whatever support I can. Of course, if you actually want guidance on this, you should ask at the workplace SE site. – RDFozz Oct 5 '17 at 18:15
  • What kind of result set does the function return? About how many rows will it return? About how many seconds does the old function take to run? – Joe Obbish Oct 5 '17 at 19:56
  • As the functions' output will be recordset this reduces to comparing two tables. Voting to close as a duplicate. – Michael Green Oct 6 '17 at 8:43
  • Possible duplicate of Quick way to validate two tables against each other – Michael Green Oct 6 '17 at 8:43
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
|improve this answer
  • Is this going to evaluate the date range by itself or do I need to set StartDate and EndDate each time. – Feivel Oct 5 '17 at 18:08
  • It will spit out result sets for every month between the dates you specify on the first line, for the counter variable. With that in mind, be careful, as I don't know what your function does, it may have a big effect on your server since if you run it for the 20 year time period at once, it's going to cycle through the loop 249 times. – Padwan Oct 5 '17 at 18:47

Not the answer you're looking for? Browse other questions tagged or ask your own question.