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>

[BKBK]

Your ColdFusion version and database server brand?

[BKBK]

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.

[College_Kid]

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]

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>