Performing calculations
-
I am trying to find a way to more efficiently run calculations other than through an asp script. I would like to have a dynamic stored procedure executed from an asp page that performs the calculations.
What I have the follwoing columns that are called "amounts", "plsummary" and "uid".
Based on the uid (which is the assigned id to a person) will display their information. I have different headings under "plsummary" like expenses, other expenses, etc and the amounts. I need something that will give me the sum for each plsummary, i.e.
sum(amount) as amount
where plsummary = 'expenses' and uid = uid
and so on for each plsummary...
Then I need various totals from these areas. For example, net contributions would be revenue - expenses. Then income available would be net contributions - general funds, net income available would be income available - other income and distributable income would be net income available - other expenses, which should always be 0.
The dollar amounts need to be rounded to the nearest dollar and formatted as currency in the display. I have tried several ways to no avail and am beginning to get frustrated. I'm sure that this is probably not very difficult, but cn't seem to see the solution.
Thanks in advance!
-
Few questions
Do you want the sums (revenue, expenses etc) as well as calculated totals?
Is there a fixed number (and name) of sums and totals?
Do you want the results in rows or columns (pivot)?
Are you rounding up, down or off? Is the rounding before or after summing?
Can you supply example input and how the output is to look
Far away is close at hand in the images of elsewhere.
Anon. -
The output would be:
Revenue Collections - Professional ($30,442) Refund Expense-Professional $174 Net Revenues 30268 <!--
Net Revenues -->
Expenses Salaries And Wages $2,702 Taxes And Benefits $698 Purchased Services $21 Supplies-Other $33 Malp Ins $2,625 Other Fees and Expenses $52 Iv Therapy Transfer ($43,310) Rent Allocation $2,552 Telephone And Pager $213 Equipment Usage $27 Transcription Alloc $813 Medical Records $200 Emr Alloc $490 Billing Alloc $2,043 Scheduling Alloc $117 Chart Label Alloc $15 Employer Paid Taxes And Benefits-Md $2,150 Total Expenses ($28,560) Net Professional Contributions 58828 General Funds Allocation CORP 25 PERCENT GF ALLOC $658 CORP 75 PERCENT GF ALLOC $3,420 Total General Funds Allocation $4,078 Professional Income Available To Physician(s) 54750 Other Income Directorships ($4,200) Total Other Income $4,078 Net Prof Income Available to Physician(s) -9864 Other Expenses Md Salary $58,950 Total Other Expenses $58,950 Distributable Income before Taxes 0 An example of the code would be and then it is the same for each plsummary:
<%
strSQL = "SELECT paystat_name, sum(amount) as amount FROM tbl_mdpayst_mymcc where plsummary = 'expenses' and uid = " + Replace(rsExpenses__MMColParam, "'", "''") + "group by paystat_name, paystat_nmbr order by paystat_name"
SET DbObj = Server.CreateObject("ADODB.Connection")
DbObj.Open "Provider=SQLOLEDB.1;server=servername;Database=dbname;uid=uid;pwd=pwd"
SET oRs = DbObj.Execute(strSQL)
%>
When doing overall totals:
<% = round(rsRevTotal.Fields("total")*-1) + round(rsExpTotal.Fields("total")*-1) %>
Basically, I would like to round off before summing. The numbers are not fixed as the data is updated once a month, however the plsummary names are the same. The user is able to click wach of the subcategories in each plsummary area for a drilldown view, which is working fine. The initial display where totals and overall totals is what is giving me problems, since I am working with positve and negative amounts.
-
I have been thinking about this and have not come up with a simple answer yet. I am still unsure of what you want. Your asp is showing the details already and the totals are as you calculate.
One thing to do is to put the sql you posted inside a procedure and if necessary include a total.
To produce the list would require several UNION or creation of a temp table to hold each line.
If you truly want to produce the list from the procedure, I suggest creating a template with several tables to define each group of data and the data that defines it.
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply