ColdFusion monthly math calculator

Now I know what I have here is UGLY. But this is the limit of my knowledge. So I'm asking you fine folks.

I have 5 tables, and six queries to get the totals for each month.

Question one, do I need 6 queries?
Question two, do I have to repeat this step for every month?

As of now I have 350 lines of code to get this working. Talk about inefficient.

Need your help.

Thank you.
<cfquery name="seppl" datasource="#request.dataSource#">
SELECT SUM(eod) AS eodt
FROM cash_flow
WHERE date >= '9/1/2010'
AND date <= '9/30/2010 11:59:00 PM'
</cfquery>
<cfquery name="sepin" datasource="#request.dataSource#">
SELECT SUM(invoice_amount) as inv
FROM invoice
WHERE invoice_date >= '9/1/2010'
AND invoice_date <= '9/30/2010'
</cfquery>
<cfquery name="seppay" datasource="#request.dataSource#">
SELECT SUM(check_amount) as pay
FROM payroll
WHERE start_date >= '9/1/2010'
AND start_date <= '9/30/2010'
</cfquery>
<cfquery name="sepexp" datasource="#request.dataSource#">
SELECT SUM(amount) as exp
FROM safe
WHERE source_id = '8' 
AND date >= '9/1/2010'
AND date <= '9/30/2010'
</cfquery>
<cfquery name="sepbill" datasource="#request.dataSource#">
SELECT SUM(bill_amount) as bill
FROM bills
WHERE bill_date >= '9/1/2010'
AND bill_date <= '9/30/2010'
</cfquery>
<cfquery name="septake" datasource="#request.dataSource#">
SELECT SUM(amount) as take
FROM safe
WHERE source_id IN (9,10) 
AND date >= '9/1/2010'
AND date <= '9/30/2010'
</cfquery>
<cfif #septake.take# eq ''>
<cfset stake = 0.00>
<cfelse>
<cfset stake = #septake.take#>
</cfif>

Open in new window

aka_FATCATAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

gdemariaCommented:
For question 1 the answer is "it depends.."   Is there a relationship between these tables that would benefit in joining the tables together?   It seems that you are summing amounts from all different locations that should stay seperate.

Keep in mind that there is no performance hit query them all seperately, joining them together may duplicate some values and message up your summations.  I suggest leaving them seperate.

As for month-to-month.  That is different, here is a sample of getting a year's worth of monthly data from one query.

So, I think you just need the six queries and can handle date ranges and date grouping within each query..

SELECT SUM(eod) AS eodt, month(date) as theMonth, year(date) as TheYear
FROM cash_flow
WHERE date between '1/1/2010' AND date <= '12/31/2010 11:59:00 PM'
group by year(date), month(date)
order by year(date), month(date)

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
gdemariaCommented:
One exception is your SAFE table, you are calling that table twice, you could merge those two queies together and just seperate the amount using a case statement

SELECT SUM(case when source_id = 8 then amount else 0 end) as take
     , SUM(case when source_id in (9,10) then amount else 0 end) as exp
     , year(date)
     , month(date)
FROM safe
WHERE source_id IN (8,9,10)
AND date between '9/1/2010' and date <= '9/30/2010'
group by year(date), month(date)

0
duncancummingCommented:
The ideal way to do this would be one stored procedure call, and let the DB do all the querying.  It would then return to you either one recordset with all the data you need, or 6 individual recordsets, one for each thing you're querying right now.  This assumes you're in a position to use stored procedures.  If not, then querying it yourself is your only option.

I'd say have the page take a parameter for the date, and make that a dynamic part of each query.  You could then either run it ad-hoc for any given month, or have this page cfincluded within a loop over a period of months.


<cfparam name="URL.month" default="#Month(Now())#">
<cfparam name="URL.year" default="#Year(Now())#">

<!--- create a start date --->
<cfset startDate = CreateDate(URL.year, URL.month, 1)>

<!--- how many days does that month have? ---?
<cfset daysInMonth = DaysInMonth(startDate)>

<!--- create an end date --->
<cfset endDate = CreateDate(URL.year, URL.month, daysInMonth)>


<cfquery name="sepin" datasource="#request.dataSource#">
	SELECT SUM(invoice_amount) as inv
	FROM invoice
	WHERE invoice_date >= <cfqueryparam value="#startDate#" CFSQLTYPE="CF_SQL_TIMESTAMP">
	AND invoice_date <= <cfqueryparam value="#endDate#" CFSQLTYPE="CF_SQL_TIMESTAMP">
</cfquery>

Open in new window

0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Pravin AsarPrincipal Systems EngineerCommented:
:gdemaria:

Little Syntax Error with Between

AND date between '9/1/2010' and date <= '9/30/2010'

should be

AND date between '9/1/2010' and  '9/30/2010'

0
gdemariaCommented:
Thanks pravinasar!


SELECT SUM(case when source_id = 8 then amount else 0 end) as take
     , SUM(case when source_id in (9,10) then amount else 0 end) as exp
     , year(date)
     , month(date)
FROM safe
WHERE source_id IN (8,9,10)
AND date between '9/1/2010' AND '12/31/2010 11:59:00 PM'
group by year(date), month(date)

0
tampatechtigerCommented:
An alternative (depending on which DB system you're using) would be to use a scalar subquery within your select statement (NOTE: depending on the volume of records in each of the tables this can be a bit burdensome from a performance standpoint even if the SQL statement is cleaner):

As follows:
<cfset startdate = createodbcdatetime("09/01/2010")>
<cfset enddate = createodbcdatetime("09/30/2010")>

<cfquery name="universal" datasource="#request.dataSource#">
      SELECT
            (SELECT SUM(eod)
            FROM cash_flow
            WHERE date BETWEEN #startdate# AND #enddate#) as eodt,

            (SELECT SUM(invoice_amount)
            FROM invoice
            WHERE invoice_date BETWEEN #startdate# AND #enddate#) as inv,

            (SELECT SUM(check_amount)
            FROM payroll
            WHERE start_date BETWEEN #startdate# AND #enddate#) as pay,

            (SELECT SUM(amount)
            FROM safe
            WHERE source_id = '8'
            AND date BETWEEN #startdate# AND #enddate#) as exp,
      
            (SELECT SUM(bill_amount)
            FROM bills
            WHERE bill_date BETWEEN #startdate# AND #enddate#) as bill,

            (SELECT SUM(amount)
            FROM safe
            WHERE source_id IN (9,10)
            AND date BETWEEN #startdate# AND #enddate#) as take
</cfquery>

<cfif #universal.take# eq ''>
      <cfset stake = 0.00>
<cfelse>
      <cfset stake = #universal.take#>
</cfif>

This will result in a query object called "universal" that will have the following fields:

universal.eodt (sum of eod from table cash_flow)
universal.inv (sum of invoice_amount from table invoice)
universal.pay (sum of check_amount from table payroll)
universal.exp (sum of amount from table safe where source_id = 8)
universal.bill (sum of bill_amount from table bills)
universal.take (sum of amount from table safe where source_id in (9,10))

I have pre-pended the setting of the "startdate" and "enddate" prior to the execution of the query in case you need that in order to dynamically set those values for iterative execution or if this query is located in a CFC or other form-action page where the dates are passed in as parameters...will enable you to do a "set-once...run-many" type of implementation.

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ColdFusion Language

From novice to tech pro — start learning today.