4 Replies Latest reply on Apr 1, 2014 3:30 AM by BKBK

    tally up to wholesale prices

    College Kid

      Good afternoon,

       

       

      We have a weekly magazine that was at a wholesale price of $0.70 to purchase.  The retail price was $1.00. After February 21,2014 the price of this magazine's wholesale price has increased to $1.00 and the retail price increased to $2.00. All wholesale prices before February 20, 2014 was at $0.70. What I am trying to do is tally up the total that each salesman sold say from January 01, 2014 thru today 03/06/2015 reflecting both to totals from the old wholesale and the new wholesale price.

      So if I had a member that sold one magazine per week from 01/01/2014 until 02/19/2014 at the $0.70 price, it will total $5.60.

      Then from 02/20/2014 until 03/06/2015 sold 1 magazine per week at the $1.00 price I get $3.00. The total from January 01 until March 06,

      2015 would be $8.60. The old code using the $0.70 wholesale calculation is below. The new code using "cfif" statement (which it throwing the error) is right below that. Am I doing something wrong?

       

       

      <!--- Existing Code--->

       

       

      <cfquery name="volno" >

      SELECT CONCAT(name.fname,' ',name.lname)AS member,

      name.foiid,

      name.city,

      fcnsales.salesfoiid,

      fcnsales.salesvolno,

      round(sum(fcnsales.salesamt/.7)) as TOTAL_MAG_SOLD

       

       

      FROM name, fcnsales

       

       

      WHERE fcnsales.salesfoiid = name.foiid

      AND fcnsales.salesvolno between '#form.volno1#' and '#form.volno2#'

      GROUP BY name.foiid

      ORDER BY TOTAL_MAG_SOLD desc;

      </cfquery>

       

       

       

       

      <!--- New Code--->

       

       

      <cfquery name="volno" >

      SELECT CONCAT(name.fname,' ',name.lname)AS member,

      name.foiid,

      name.city,

      fcnsales.salesfoiid,

      fcnsales.salesvolno,

      <cfif #fcnsales.salesdate# GTE '2014-02-20'> round(sum(fcnsales.salesamt/1)) as TOTAL_MAG_SOLD<cfelseif #fcnsales.salesdate# LT '2014-02-20'> round(sum(fcnsales.salesamt/.7)) as TOTAL_MAG_SOLD

       

       

      FROM name, fcnsales

       

       

      WHERE fcnsales.salesfoiid = name.foiid

      AND fcnsales.salesvolno between '#form.volno1#' and '#form.volno2#'

      GROUP BY name.foiid

      ORDER BY TOTAL_MAG_SOLD desc;

      </cfquery>

        • 1. Re: tally up to wholesale prices
          BKBK Adobe Community Professional & MVP

          Your ColdFusion version and database server brand?

          • 2. Re: tally up to wholesale prices
            BKBK Adobe Community Professional & MVP

            Hang on a minute. You need to improve the whole concept!

             

            <cfif #fcnsales.salesdate# GTE '2014-02-20'>

            The value of fcnsales.salesdate is only known at runtime. Therefore, it cannot be known during the compilation of the query Volno.

             

            sum(fcnsales.salesamt/1)

            That is an aggregate function. It means you are grouping data values to a single row. It is therefore self-contradictory to proceed to list the IDs, volnos and cities that correspond to the summed value.

             

            round(sum(fcnsales.salesamt/1))

            You could make your code more universal, hence more accessible, by using SQL's own round(x,y) function. Its second argument denotes the number of decimal places.

             

            Playing dumb is not necessarily stupid. I have split your code up into 4 queries, as follows, holding on to the spirit of your original query:

             

            <cfquery name="salesBefore20Feb2014" >

            SELECT round(sum(salesamt/.7),0) as sumSalesAmt

            FROM fcnsales

            WHERE salesdate < <cfqueryparam cfsqltype="cf_sql_date" value="#createDate(2014,2,20)#"> AND salesvolno between <cfqueryparam cfsqltype="cf_sql_integer" value="#form.volno1#"> AND <cfqueryparam cfsqltype="cf_sql_integer" value="#form.volno2#">

            </cfquery>

             

            <cfquery name="salesAfter19Feb2014" >

            SELECT round(sum(salesamt),0) as sumSalesAmt

            FROM fcnsales

            WHERE salesdate >= <cfqueryparam cfsqltype="cf_sql_date" value="#createDate(2014,2,20)#"> AND salesvolno between <cfqueryparam cfsqltype="cf_sql_integer" value="#form.volno1#"> AND <cfqueryparam cfsqltype="cf_sql_integer" value="#form.volno2#">

            </cfquery>

             

            <cfquery name="volnoBefore" >

            SELECT CONCAT(name.fname,' ',name.lname)AS member,

            name.foiid,

            name.city,

            fcnsales.salesvolno

            FROM name, fcnsales

            WHERE fcnsales.salesfoiid = name.foiid

            AND fcnsales.salesdate < <cfqueryparam cfsqltype="cf_sql_date" value="#createDate(2014,2,20)#"> AND fcnsales.salesvolno between <cfqueryparam cfsqltype="cf_sql_integer" value="#form.volno1#"> AND <cfqueryparam cfsqltype="cf_sql_integer" value="#form.volno2#">

            GROUP BY name.foiid

            </cfquery>

             

            <cfquery name="volnoAfter" >

            SELECT CONCAT(name.fname,' ',name.lname)AS member,

            name.foiid,

            name.city,

            fcnsales.salesvolno

            FROM name, fcnsales

            WHERE fcnsales.salesfoiid = name.foiid

            AND fcnsales.salesdate >= <cfqueryparam cfsqltype="cf_sql_date" value="#createDate(2014,2,20)#"> AND fcnsales.salesvolno between <cfqueryparam cfsqltype="cf_sql_integer" value="#form.volno1#"> AND <cfqueryparam cfsqltype="cf_sql_integer" value="#form.volno2#">

            GROUP BY name.foiid

            </cfquery>

             

            <p>

            Sales before 20 Feb 2014: #salesBefore20Feb2014.sumSalesAmt# <br>

            <cfdump var="#volnoBefore#" label="Vol no. before">

            </p>

             

            <p>

            Sales after 19 Feb 2014: #salesAfter19Feb2014.sumSalesAmt# <br>

            <cfdump var="#volnoAfter#" label="Vol no. after">

            </p>

             

            I have assumed sales volume is a whole number. Modify this if necessary. Also note use of cfqueryparam for binding and security.

            • 3. Re: tally up to wholesale prices
              College Kid Level 1

              I was able to study your method the last couple of weeks to execute the queries perfectly using CFC's!!! I am however at a lost on how to combine these two total's into one talley for each member in the form of a list. So if member "A" sold 50 magazine at $0.70 before 02/20/2014 the total would be $35. And if the same member sold another 50 magazine at the new price of $1.00 after 02/19/2014 the total would be $50. An the combined total being $85.

               

              The list would look like

              Member A = $85.00

              Member B = $25.00

              Member C = $42.50

               

              The coding probably very simple i'm just at a road block. Any direction that you could point me to?

              • 4. Re: tally up to wholesale prices
                BKBK Adobe Community Professional & MVP

                The code could indeed be very simple. From what I understand, you already have the sales amounts of each member in the fcnsales table. So, you could just extend the first two queries a little. I was thinking of something like

                 

                <cfquery name="salesBefore20Feb2014" >

                SELECT salesfoiid AS id, sum(salesamt) as memberSalesAmt, round(sum(salesamt/.7),0) as memberNoOfUnitsSold

                FROM fcnsales

                WHERE salesdate < <cfqueryparam cfsqltype="cf_sql_date" value="#createDate(2014,2,20)#"> AND salesvolno between <cfqueryparam cfsqltype="cf_sql_integer" value="#form.volno1#"> AND <cfqueryparam cfsqltype="cf_sql_integer" value="#form.volno2#">

                GROUP BY salesfoiid

                </cfquery>

                 

                <cfquery name="salesAfter19Feb2014" >

                SELECT salesfoiid AS id, sum(salesamt) as memberSalesAmt, round(sum(salesamt/.7),0) as memberNoOfUnitsSold

                FROM fcnsales

                WHERE salesdate >= <cfqueryparam cfsqltype="cf_sql_date" value="#createDate(2014,2,20)#"> AND salesvolno between <cfqueryparam cfsqltype="cf_sql_integer" value="#form.volno1#"> AND <cfqueryparam cfsqltype="cf_sql_integer" value="#form.volno2#">

                GROUP BY salesfoiid

                </cfquery

                 

                Then use query-of-a-query to combine the results:

                 

                <cfquery name="getMemberSales" dbType="query">

                SELECT salesBefore20Feb2014.id AS memberId,

                salesBefore20Feb2014.memberSalesAmt AS memberSalesAmtBefore20Feb2014,

                salesAfter19Feb2014.memberSalesAmt AS memberSalesAmtAfter19Feb2014,

                (salesBefore20Feb2014.memberSalesAmt + salesAfter19Feb2014.memberSalesAmt) AS memberTotalSalesAmt

                FROM salesBefore20Feb2014, salesAfter19Feb2014

                WHERE salesBefore20Feb2014.id = salesAfter19Feb2014.id

                </cfquery>