# 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.

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>
``````
###### Who is Participating?

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.

Commented:
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)
``````
0

Experts Exchange Solution brought to you by

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

Commented:
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
Commented:
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>
``````
0
Principal 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
Commented:
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
Commented:
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.