As a follow up to yesterday's post, I've been converting a web based c# application to SQL.
So there are 3 ways to view the data, A, B and C.
Each time any of those are called, it can be run for Weekly or Monthly.
And for Pending or Closed sales.
And each of of those calls the database twice, once for the legacy data and one for the latest data.
And each one of those calls the database 5 times, one for every day of the week or all the weeks of the month.
So as you can see, I estimate there's potential for 60 database calls.
On a single report, the database gets 20 calls per page refresh.
And there's a timer built into the app to refresh the report automatically every so often.
I was able to deduce all this into 2 queries.
Which pull back all the possible combinations.
And the new code pulls every Sales Person, not just a specific group.
And it runs in about 4 seconds.
However, I've only done half the job so far.
As there's also more calls to another database which I'll begin working on now.
Converting the business logic from c# to SQL is a tedious job.
However, I pulled both reports and matched every single total for every single sales person.
Now onto part 2.
Here's the post from yesterday in case you missed it: