need sql to sum across columns

I am trying the following code using SQL and CF8;

<cffunction name="GetStatsRSFsy" access="public" returntype="query">
    <cfargument name="MonthRange1" type="string" required="yes">
    <cfargument name="MonthRange2" type="string" required="yes">
    <cfargument name="User" type="string" required="yes">
   
    <cfset qsfsy = "">
    <!--- Month-to-date stat queries RSF  stats monthly--->
        <cfquery datasource="taxsearchdb" name="qsfsm">
            SELECT
            SUM(Fills) as sffillsm

            FROM JobBoardStats
            LEFT JOIN JOrder
            ON JobBoardStats.JobOrder=JOrder.JobOrderNumber            
            where Date between #MonthRange1# AND #MonthRange2# AND ConsRec = '#user#' and (StatGoal = '2' or StatGoal = ' ') and StatType = 'RSF'
            SUM(InvoiceAmount1,InvoiceAmount2,InvoiceAmount3,InvoiceAmount4,InvoiceAmount5,InvoiceAmount6,InvoiceAmount7,InvoiceAmount8,InvoiceAmount9,) as rcproductmstat
            where InvoiceDate <= #MonthRange2#
            <!---SUM(InvoiceAmount1,InvoiceAmount2,InvoiceAmount3,InvoiceAmount4,InvoiceAmount5,InvoiceAmount6,InvoiceAmount7,InvoiceAmount8,InvoiceAmount9,) as nwproductmstat
             WHERE InvoiceDate between #MonthRange1# and  #MonthRange2# --->          
                  </cfquery>
                 
    <cfreturn qsfsm>
</cffunction>

The invoice amounts and dates are in the Jorder table. It gets a little more complicated in that invoices dated prior to the current month are considered "recurring production" and obviously invoices in the current month are "new production".


LVL 1
larksysAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Brendt HessConnect With a Mentor Senior DBACommented:
Still missing single quotes around the dates in the WHERE line.

Take the generated SQL code, and run it in Query Analyzer.  That should give you better visibility to the issue.
0
 
Brendt HessSenior DBACommented:
Replace the commas with plus signs, and you should be close:

SUM(InvoiceAmount1 + InvoiceAmount2 + InvoiceAmount3 + InvoiceAmount4 + InvoiceAmount5 + InvoiceAmount6 + InvoiceAmount7 + InvoiceAmount8 + InvoiceAmount9)
0
 
larksysAuthor Commented:
Invalid syntax near keyword SUM;  (the second SUM)            

SELECT
            SUM(Fills) as sffillsy

            FROM JobBoardStats
            LEFT JOIN JOrder
            ON JobBoardStats.JobOrder=JOrder.JobOrderNumber            
            where Date between #YearRange1# AND #YearRange2# AND ConsRec = '#user#' and (StatGoal = '2' or StatGoal = ' ') and StatType = 'RSF'
            SUM(InvoiceAmount1 + InvoiceAmount2 + InvoiceAmount3 + InvoiceAmount4 + InvoiceAmount5 + InvoiceAmount6 + InvoiceAmount7 + InvoiceAmount8 + InvoiceAmount9) as rcproductystat
            where InvoiceDate <= #YearRange2#
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
Brendt HessSenior DBACommented:
Place all of the items you are selecting for display in one continuous sequence, e.g.:

SELECT
            SUM(Fills) as sffillsy,
            SUM(
                CASE WHEN InvoiceDate <= #YearRange2# THEN
                    InvoiceAmount1 + InvoiceAmount2 + InvoiceAmount3 + InvoiceAmount4 + InvoiceAmount5 + InvoiceAmount6 + InvoiceAmount7 + InvoiceAmount8 + InvoiceAmount9
                 ELSE 0 END) as rcproductystat
            FROM JobBoardStats
            LEFT JOIN JOrder
            ON JobBoardStats.JobOrder=JOrder.JobOrderNumber            
            where Date between #YearRange1# AND #YearRange2# AND ConsRec = '#user#' and (StatGoal = '2' or StatGoal = ' ') and StatType = 'RSF'

REMEMBER:  One SELECT selects all of the records being referenced in one swell foop.  If you need to have multiple SELECT statements run, then run multiple SELECTs and combine the results
0
 
imitchieCommented:
That would not sum anything outside of YearRange1 and 2
try this instead

SELECT
            SUM(CASE WHEN Date >= #YearRange1# Then Fills else 0 END) as sffillsy,
            ISNULL(SUM(InvoiceAmount1 + InvoiceAmount2 + InvoiceAmount3 + InvoiceAmount4 + InvoiceAmount5 + InvoiceAmount6 + InvoiceAmount7 + InvoiceAmount8 + InvoiceAmount9),0) as rcproductystat
            FROM JobBoardStats
            LEFT JOIN JOrder
            ON JobBoardStats.JobOrder=JOrder.JobOrderNumber            
            where Date <= #YearRange2# AND ConsRec = '#user#' and (StatGoal = '2' or StatGoal = ' ') and StatType = 'RSF'
0
 
larksysAuthor Commented:
The following code;

      SELECT
          SUM(Fills) as sffillsy
          SUM(
          CASE WHEN InvoiceDate <= #YearRange2# THEN InvoiceAmount1 + InvoiceAmount2 + InvoiceAmount3 + InvoiceAmount4 + InvoiceAmount5 + InvoiceAmount6 + InvoiceAmount7 + InvoiceAmount8 + InvoiceAmount9 ELSE 0 END) as rcproductystat
           SUM(
          CASE WHEN InvoiceDate BETWEEN #yearRange1# and #YearRange2# THEN InvoiceAmount1 + InvoiceAmount2 + InvoiceAmount3 + InvoiceAmount4 + InvoiceAmount5 + InvoiceAmount6 + InvoiceAmount7 + InvoiceAmount8 + InvoiceAmount9 ELSE 0 END) as nwproductystat            

            FROM JobBoardStats
            LEFT JOIN JOrder
            ON JobBoardStats.JobOrder=JOrder.JobOrderNumber            
            where Date between #YearRange1# AND #YearRange2# AND ConsRec = '#user#' and (StatGoal = '2' or StatGoal = ' ') and StatType = 'RSF'
-------------------------------------------------------------------------------------------------------------------
yielded a syntax error near the keyword SUM and the following SQL statement;


 SELECT SUM(Fills) as sffillsy SUM( CASE WHEN InvoiceDate <= 20071131 THEN InvoiceAmount1 + InvoiceAmount2 + InvoiceAmount3 + InvoiceAmount4 + InvoiceAmount5 + InvoiceAmount6 + InvoiceAmount7 + InvoiceAmount8 + InvoiceAmount9 ELSE 0 END) as rcproductystat SUM( CASE WHEN InvoiceDate BETWEEN 20070101 and 20071131 THEN InvoiceAmount1 + InvoiceAmount2 + InvoiceAmount3 + InvoiceAmount4 + InvoiceAmount5 + InvoiceAmount6 + InvoiceAmount7 + InvoiceAmount8 + InvoiceAmount9 ELSE 0 END) as nwproductystat FROM JobBoardStats LEFT JOIN JOrder ON JobBoardStats.JobOrder=JOrder.JobOrderNumber where Date between 20070101 AND 20071131 AND ConsRec = 'VL ' and (StatGoal = '2' or StatGoal = ' ') and StatType = 'RSF'
--------------------------------------------------------------------------------------------------------------------------
0
 
imitchieCommented:
larksys, please give my query (ID:20378286) a try
0
 
Brendt HessSenior DBACommented:
You need single quotes around the dates.  You also need a comma after sffillsy.  Try:

      SELECT
          SUM(Fills) as sffillsy,
          SUM(
          CASE WHEN InvoiceDate <= '#YearRange2#' THEN InvoiceAmount1 + InvoiceAmount2 + InvoiceAmount3 + InvoiceAmount4 + InvoiceAmount5 + InvoiceAmount6 + InvoiceAmount7 + InvoiceAmount8 + InvoiceAmount9 ELSE 0 END) as rcproductystat
           SUM(
          CASE WHEN InvoiceDate BETWEEN '#yearRange1#' and '#YearRange2#' THEN InvoiceAmount1 + InvoiceAmount2 + InvoiceAmount3 + InvoiceAmount4 + InvoiceAmount5 + InvoiceAmount6 + InvoiceAmount7 + InvoiceAmount8 + InvoiceAmount9 ELSE 0 END) as nwproductystat            

            FROM JobBoardStats
            LEFT JOIN JOrder
            ON JobBoardStats.JobOrder=JOrder.JobOrderNumber            
            where Date between '#YearRange1#' AND '#YearRange2#' AND ConsRec = '#user#' and (StatGoal = '2' or StatGoal = ' ') and StatType = 'RSF'
0
 
larksysAuthor Commented:
Incorrect syntax near the keyword SUM;

SELECT
   SUM(Fills) as sffillsy,
   SUM(
          CASE WHEN InvoiceDate1 <= '#YearRange2#' THEN InvoiceAmount1 + InvoiceAmount2 + InvoiceAmount3 + InvoiceAmount4 + InvoiceAmount5 + InvoiceAmount6 + InvoiceAmount7 + InvoiceAmount8 + InvoiceAmount9 ELSE 0 END) as rcproductystat,
    SUM(          
          CASE WHEN InvoiceDate1 BETWEEN '#yearRange1#' and '#YearRange2#' THEN InvoiceAmount1 + InvoiceAmount2 + InvoiceAmount3 + InvoiceAmount4 + InvoiceAmount5 + InvoiceAmount6 + InvoiceAmount7 + InvoiceAmount8 + InvoiceAmount9 ELSE 0 END) as nwproductystat            

   FROM JobBoardStats
   LEFT JOIN JOrder
   ON JobBoardStats.JobOrder=JOrder.JobOrderNumber            
            where Date between #YearRange1# AND #YearRange2# AND ConsRec = '#user#' and (StatGoal = '2' or StatGoal = ' ') and StatType = 'RSF'
0
 
imitchieCommented:
am i being ignored?
0
 
larksysAuthor Commented:
The data type for JobBoardStats.Date is nvarchar. The data type for Jorder.InvoiceDate1 is decimal.

I need to sum all JobBoardStats.Fills ytd. In fact, I need to select all rows from JobBoardStats that match the date criteria and user and record types. From those records, if there is a JobOrderNumber, I need to join to Jorder and sum the InvoiceAmounts columns based on the InvoiceDates.

I hope that makes it a little clearer.

0
 
imitchieCommented:
I gave you the most correct answer. Not only do you ignore me but you still haven't correctly defined your requirement. What if you decide to come up with more information later?
Your query clearly has an intention to get the "invoices dated prior to the current month" as well, which the previous answers have failed to address. Your latest comment contradicts that "that match the date criteria (only)" or was not worded very well.

Bye
0
 
larksysAuthor Commented:
The following code passes syntax muster but does not yield the correct amounts;

      SELECT
         SUM(Fills) as sffillsy,
         SUM(
          CASE WHEN InvoiceDate1 <= '#YearRange2#' THEN InvoiceAmount1 + InvoiceAmount2 + InvoiceAmount3 + InvoiceAmount4 + InvoiceAmount5 + InvoiceAmount6 + InvoiceAmount7 + InvoiceAmount8 + InvoiceAmount9 ELSE 0 END) as rcproductystat,
         SUM(          
          CASE WHEN InvoiceDate1 BETWEEN '#yearRange1#' and '#YearRange2#' THEN InvoiceAmount1 + InvoiceAmount2 + InvoiceAmount3 + InvoiceAmount4 + InvoiceAmount5 + InvoiceAmount6 + InvoiceAmount7 + InvoiceAmount8 + InvoiceAmount9 ELSE 0 END) as nwproductystat            

            FROM JobBoardStats
            LEFT JOIN JOrder
            ON JobBoardStats.JobOrder=JOrder.JobOrderNumber          
            where Date between '#YearRange1#' AND '#YearRange2#' AND ConsRec = '#user#' and (StatGoal = '2' or StatGoal = ' ') and StatType = 'RSF'


For one job record the invoice amounts are 41250 and 61875. For the other job record the invoice amounts are 40626 and 35874. The results for each of the job records is 17962. All invoice fields either have the amounts I stated or have a zero. I think I need to take another tack. I thought maybe I could do it in one SQL statement.
0
 
larksysAuthor Commented:
I apologize to imitchie. If you look at all the code I supplied, I have really not followed my own problem very well. I have several functions where I intend to gather stats for the month and goal predictions for the month and the same for the quarter and the same for the year. In general, all of that code works well. In one instance, where I am gathering "fills" for the month, quarter, and year, I need to examine the job associated with that record and determine production dollars from invoicing. Unfortuantely there are 9 possible invoices and dates that need to be examined. The test code I have been showing you doesn't even consider the complication of having 9 different invoices and dates on one record. I was trying to learn how to conditionally sum columns. As far as my initial question goes, bhess1 followed it through. However, and obviously, I still need help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.