Skip to main content
C
College Kid
Inspiring
March 11, 2014
Question

tally up to wholesale prices

  • March 11, 2014
  • 2 replies
  • 765 views

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>

    This topic has been closed for replies.

    2 replies

    BKBK
    Community Expert
    BKBKCommunity Expert
    Community Expert
    March 11, 2014

    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.

    C
    College KidAuthor
    Inspiring
    April 1, 2014

    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?

    BKBK
    Community Expert
    BKBKCommunity Expert
    Community Expert
    April 1, 2014

    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>

    BKBK
    Community Expert
    BKBKCommunity Expert
    Community Expert
    March 11, 2014

    Your ColdFusion version and database server brand?

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices