I have a Microsoft SQL stored procedure which creates and returns a result set, which includes numeric totals from a database.? The definitions for 3 of the totals need to be changed per the client's specifications.
## Deliverables
The attached SQL file needs to be revised per the details listed below.? I’ve also attached an Excel file which documents the ProLaw database structure; the SQL developer will need that documentation to do the revisions; specifically, the developer will need to review the worksheets named “Billing_a_statement??, “Pay ??" In Full??, and “Pay ??" Partial?? in [login to view URL], as well as the worksheet named "General" in the Matters.xls.
?
Essentially, the only things in the attached SQL file which need to be changed are the definitions for the columns for PersonalProduction, Originations, and BillingsUnderManagement in the dc_Results table.? The definitions for those 3 columns should be changed as follows:
·? ? ? ? ? ? ? ? **PersonalProduction (lines 148 - 160 in the SQL file):**
o? ? ? For lines 147-154 from the SQL file:
o? ? ? Instead of StmnUnits, this needs to be changed to revenues (i.e., payments).? To change the code to get payments, the Transactions table can be removed from the above UPDATE syntax, and the following syntax should be added to the WHERE clause:
and [login to view URL] = [login to view URL] and [login to view URL] = 'P'
In addition, the clause for [login to view URL] BETWEEN … should be changed to [login to view URL] BETWEEN …
o? ? ? Based on payments for area of law <> ‘administrative’, ‘contingent’, and matter type <> ‘pro bono’.
o? ? ? Based on paid date.
o? ? ? Statements dated prior to 9/1/09 should be excluded from the total.
o? ? ? For contingent matters (lines 157 ??" 160), sum the worked value (i.e., [login to view URL] column) for the time period based on the transaction date (i.e., [login to view URL]).
·? ? ? ? ? ? ? ? **Originations (lines 162 - 178 in the SQL file):**
o? ? ? Instead of [login to view URL], this needs to be changed to revenues (i.e., payments, [login to view URL]).
o? ? ? Based on payments for area of law <> ‘administrative’, ‘contingent’, and matter type <> ‘pro bono’.
o? ? ? Based on paid date.
o? ? ? Statements dated prior to 9/1/09 should be excluded from the total.
o? ? ? For contingent matters (lines 172 ??" 178), sum the worked value (i.e., [login to view URL] column) for the time period based on the transaction date (i.e., [login to view URL]).
·? ? ? ? ? ? ? ? **BillingsUnderManagement (lines 180 - 192 in the SQL file):**
o? ? ? Instead of [login to view URL], this needs to be changed to revenues (i.e., payments, [login to view URL]).
o? ? ? Based on payments for area of law <> ‘administrative’, ‘contingent’, and matter type <> ‘pro bono’.
o? ? ? Based on paid date.
o? ? ? Statements dated prior to 9/1/09 should be excluded from the total.
o? ? ? For contingent matters (lines 188 ??" 192), sum the worked value (i.e., [login to view URL] column) for the time period based on the transaction date (i.e., [login to view URL]).