Ok - I think it's just late in the day here - but I can't get this query to
work...
I am building a "Sales Report" application where users(salesmen) mark down what
Products they "demonstrated" on what "sales call". Then I want to querry the
"Sales Calls" to show what products they demoed and what they didn't over a set
period of time. My current query works- but it doesn't return the products
they DIDN't demo... Only the ones they did. (And I would like a list of both)
There are THREE Tables to Query:
ProductCategory = The Product List (Oven, cabinet, etc)
SalesReports = The Sales Report
ProductCategoryLink = The LInk between the ProductCategory and the SalesReports
Thanks In Advance - Nick
Query as follows:
<cfquery name="qryGetProducts" datasource="#Application.DSN#"
username="#Application.username#" password="#Application.password#">
SELECT P.CategoryName
,L.PCID
,S.SRID
,COUNT(L.PCID) AS PCTotal
FROM ProductCategory P
Left Outer Join ProductCategoryLink L ON P.PCID = L.PCID
LEFT Outer Join SalesReports S ON S.SRID = L.SRID
WHERE S.DateOfVisit BETWEEN '#variables.StartDay#' AND '#variables.EndDay#'
AND S.ESID = #variables.ThisUser#
GROUP BY L.PCID
ORDER BY P.CategoryName ASC
</cfquery>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion
Archive:
http://www.houseoffusion.com/groups/cf-newbie/message.cfm/messageid:5065
Subscription: http://www.houseoffusion.com/groups/cf-newbie/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-newbie/unsubscribe.cfm
