I'm writing a calendar that will contain sales data. I run a query against
that database to return all of the data for a given month, and then as I
loop through that resultset and build the calendar, I run a query of queries
against the DB query to return the data for that day.
The original DB query:
SELECT PS.dteStartDate, datePart(dayofyear, PS.dteStartDate) AS
dteStartDateDOY,
PS.dteEndDate, datePart(dayofyear, PS.dteEndDate) AS dteEndDateDOY,
PS.decAmount
FROM tblProductSales PS
WHERE ((datepart(m, PS.dteStartDate) = datepart(m, <cfqueryparam
cfsqltype="CF_SQL_DATE" value="#attributes.date#">)) AND
(datepart(yyyy, PS.dteEndDate) = datepart(yyyy, <cfqueryparam
cfsqltype="CF_SQL_DATE" value="#attributes.date#">))) AND
PS.intstoreid = <cfqueryparam cfsqltype="CF_SQL_INTEGER"
value="#session.storeid#">
ORDER BY PS.dteStartDate
This query returns the expected results (there's only data for one day):
query - Top 4 of 4 Rows
DECAMOUNT DTEENDDATE DTEENDDATEDOY DTESTARTDATE DTESTARTDATEDOY
1 43.4100 2007-04-05 09:15:00.0 95 2007-04-05 09:15:00.0 95
2 123.4100 2007-04-05 09:43:00.0 95 2007-04-05 09:43:00.0 95
3 9.3200 2007-04-05 09:43:00.0 95 2007-04-05 09:43:00.0 95
4 456.7800 2007-04-05 13:10:00.0 95 2007-04-05 13:10:00.0 95
The "decamount" column is the one that I'm havign issues with. So, the
query of query that SHOULD be returning a total for that day's sales through
the use of the SUM() aggregate function is this:
SELECT dteStartDate, dteStartDateDOY, dteEndDate, dteEndDateDOY,
SUM(decAmount) AS totalAmount
FROM getSalesCalendar
WHERE dteStartDateDOY = #variables.currDOY# OR
(#variables.currDOY# BETWEEN dteStartDateDOY AND dteEndDateDOY)
GROUP BY dteStartDate, dteStartDateDOY, dteEndDate, dteEndDateDOY, decAmount
The problem is that the "totalAmount" value for that query is only returning
the value for the first record, 43.41
Is this a problem that anyone else has encountered, or am I being obtuse?
Thanks!
Pete
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Upgrade to Adobe ColdFusion MX7
Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs
http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJQ
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:274679
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
