too many queries, can't see how to loop

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

too many queries, can't see how to loop

Author

Topic

webster
Starting Member

1 Post

Posted - 2003-02-20 : 15:11:36

I'm creating a monthly expense report.

There are 18 different expense categories and as of now 4 different months, plus 2002 year-end totals and 2003 year-to-date.

Currently the only thing I have that works is multiple queries on the same page (18 x 6) and counting.

I know this is ridiculous so stop laughing at me. :-)

Here's one small example. I'm using cold fusion and, unfortunately, using Access but will be moving over to MSSQL soon.



<cfquery name="expensesTotal" datasource="store">

SELECT sum (cost) AS [costTotal]
FROM expenses

</cfquery>
<cfquery name="expenses2002" datasource="store">

SELECT sum (cost) AS [cost2002]
FROM expenses
WHERE expenseDate LIKE '%2002'

</cfquery>
<cfquery name="expenses2003" datasource="store">

SELECT sum (cost) AS [cost2003]
FROM expenses
WHERE expenseDate LIKE '%2003'

</cfquery>
<cfquery name="expenses2002_12" datasource="store">

SELECT sum (cost) AS [cost2002_12]
FROM expenses
WHERE Right([expenseDate],4) LIKE '2002' AND Left([expenseDate],2) LIKE '12'

</cfquery>



There has got to be a way to loop through this but I haven't found it.

TIA

r937
Posting Yak Master

112 Posts

Posted - 2003-02-21 : 07:16:15

yes, there is, it's called grouping

run one query like this:

  <CFQUERY NAME="monthly">
    select year(expenseDate) as theyear
         , month(expenseDate) as themonth
         , sum(cost) as monthlycost
      from expenses  
    group
        by year(expenseDate) 
         , month(expenseDate) 
    order
        by 1,2
  </CFQUERY>

now set up a two-level CFOUTPUT to produce your totals

  <CFOUTPUT QUERY="monthly" GROUP="theyear">
    <h1>#theyear#</h1> 
    <CFSET yeartotal = 0 >
    <CFOUTPUT>
       <p>month: #themonth#  total: #monthlycost#</p>
       <CFSET yeartotal = yeartotal + monthlycost >
    </CFOUTPUT>
    <h3>total for #theyear#: #yeartotal#</h3>
  </CFOUTPUT>

rudy
http://r937.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources