Here is the case, I have 6 queries that all need to be put together, I can't
do a join cause the previous year's data doesn't match this years and I have
to do somet logic. It's on an AS/400 so fancy SQL is not an option. Is this
the best way to do this, or is there a better way, short of changing it to
cfscript.
<cfquery name="YesterdaySales" datasource="New400" dbtype="ODBC"
cachedwithin="#createtimespan(0,12,0,0)#">
SELECT SUM(CANTS) as NetSales,
trim(CAMCU) as CAMCU
FROM DIXCUST.FDCASLDL2
Where 1=1
<cfif attributes.cat eq "USC">
AND CACO NOT IN ('00036','00071')
AND CAMCU IN ('HUN','VER')
<cfelseif attributes.cat eq "NCR">
AND CACO IN ('00071')
<cfelseif attributes.cat eq "CAN">
AND CACO IN ('00036')
</cfif>
GROUP BY CAMCU
<cfif attributes.cat eq "USC">
UNION
SELECT SUM(CANTS) as NetSales,
'OTHER' as CAMCU
FROM DIXCUST.FDCASLDL2
Where 1=1
AND CACO NOT IN ('00036','00071')
AND CAMCU not IN ('HUN','VER')
Order by CAMCU
</cfif>
</cfquery>
<cfquery name="PYesterdaySales" datasource="New400" dbtype="ODBC"
cachedwithin="#createtimespan(0,12,0,0)#">
SELECT SUM(CANTS) as NetSales,
trim(CAMCU) as CAMCU
FROM DIXCUST.FDCASLSL2
Where 1=1
<cfif attributes.cat eq "USC">
AND CACO IN ('00030', '00032')
AND CACO NOT IN ('00036','00071')
AND CAMCU <> 'RTN'
<cfelseif attributes.cat eq "NCR">
AND CACO IN ('00071')
<cfelseif attributes.cat eq "CAN">
AND CACO IN ('00036')
</cfif>
and CADAT = 1#dateformat(createdate(2002, datepart("m",
now()), datepart("d", now())), "YYMMDD")#
GROUP BY CACO,CAMCU
<cfif attributes.cat eq "USC">
UNION
SELECT SUM(CANTS) as NetSales, 'OTHER' as CAMCU
FROM DIXCUST.FDCASLSL2
Where 1=1
AND CACO NOT IN ('00030', '00032','00036','00071')
and CADAT = 1#dateformat(createdate(2002, datepart("m",
now()), datepart("d", now())), "YYMMDD")#
Order by CAMCU
</cfif>
</cfquery>
<cfquery name="PYTDSales" datasource="New400" dbtype="ODBC"
cachedwithin="#createtimespan(0,12,0,0)#">
SELECT SUM(CANTS) as NetSales,
trim(CAMCU) as CAMCU
FROM DIXCUST.FDCASLSL2
Where 1=1
<cfif attributes.cat eq "USC">
AND CACO IN ('00030', '00032')
AND CACO NOT IN ('00036','00071')
AND CAMCU <> 'RTN'
<cfelseif attributes.cat eq "NCR">
AND CACO IN ('00071')
<cfelseif attributes.cat eq "CAN">
AND CACO IN ('00036')
</cfif>
and CADAT between 1#dateformat(createdate(2001, 10,
1), "YYMMDD")# and 1#dateformat(createdate(2002, 9, 30), "YYMMDD")#
GROUP BY CAMCU
<cfif attributes.cat eq "USC">
UNION
SELECT SUM(CANTS) as NetSales,
'OTHER' as CAMCU
FROM DIXCUST.FDCASLSL2
Where 1=1
AND CACO NOT IN ('00030', '00032','00036','00071')
and CADAT between 1#dateformat(createdate(2001, 10,
1), "YYMMDD")# and 1#dateformat(createdate(2002, 9, 30), "YYMMDD")#
Order by CAMCU
</cfif>
</cfquery>
<cfquery name="YTDSales" datasource="New400" dbtype="ODBC"
cachedwithin="#createtimespan(0,12,0,0)#">
SELECT SUM(CANTS) as NetSales,
trim(CAMCU) as CAMCU
FROM DIXCUST.FDCASLSL2
Where 1=1
<cfif attributes.cat eq "USC">
AND CACO NOT IN ('00036','00071')
AND CAMCU IN ('HUN','VER')
<cfelseif attributes.cat eq "NCR">
AND CACO IN ('00071')
<cfelseif attributes.cat eq "CAN">
AND CACO IN ('00036')
</cfif>
and CADAT >= 1#dateformat(createdate(2002, 10, 1),
"YYMMDD")#
GROUP BY CAMCU
<cfif attributes.cat eq "USC">
UNION
SELECT SUM(CANTS) as NetSales, 'OTHER' as CAMCU
FROM DIXCUST.FDCASLSL2
Where 1=1
AND CACO NOT IN ('00036','00071')
AND CAMCU not IN ('HUN','VER')
and CADAT >= 1#dateformat(createdate(2002, 10, 1),
"YYMMDD")#
Order by CAMCU
</cfif></cfquery>
<cfquery name="PMTDSales" datasource="New400" dbtype="ODBC"
cachedwithin="#createtimespan(0,12,0,0)#">
SELECT SUM(CANTS) as NetSales,
trim(CAMCU) as CAMCU
FROM DIXCUST.FDCASLSL2
Where 1=1
<cfif attributes.cat eq "USC">
AND CACO IN ('00030', '00032')
AND CACO NOT IN ('00036','00071')
AND CAMCU <> 'RTN'
<cfelseif attributes.cat eq "NCR">
AND CACO IN ('00071')
<cfelseif attributes.cat eq "CAN">
AND CACO IN ('00036')
</cfif>
and CADAT between
1#dateformat(createdate(datepart("yyyy",dateadd("yyyy", -1, now())),
datepart("m",now()), 1), "YYMMDD")# and
1#dateformat(createdate(datepart("yyyy",dateadd("yyyy", -1, now())),
datepart("m",now()), DaysInMonth(now())), "YYMMDD")#
GROUP BY CAMCU
<cfif attributes.cat eq "USC">
UNION
SELECT SUM(CANTS) as NetSales, 'OTHER' as CAMCU
FROM DIXCUST.FDCASLSL2
Where 1=1
AND CACO NOT IN ('00030', '00032','00036','00071')
and CADAT between
1#dateformat(createdate(datepart("yyyy",dateadd("yyyy", -1, now())),
datepart("m",now()), 1), "YYMMDD")# and
1#dateformat(createdate(datepart("yyyy",dateadd("yyyy", -1, now())),
datepart("m",now()), DaysInMonth(now())), "YYMMDD")#
Order by CAMCU
</cfif>
</cfquery>
<cfquery name="MTDSales" datasource="New400" dbtype="ODBC"
cachedwithin="#createtimespan(0,12,0,0)#">
SELECT SUM(CANTS) as NetSales,
trim(CAMCU) as CAMCU
FROM DIXCUST.FDCASLSL2
Where 1=1
<cfif attributes.cat eq "USC">
AND CACO NOT IN ('00036','00071')
AND CAMCU IN ('HUN','VER')
<cfelseif attributes.cat eq "NCR">
AND CACO IN ('00071')
<cfelseif attributes.cat eq "CAN">
AND CACO IN ('00036')
</cfif>
and CADAT between
1#dateformat(createdate(datepart("yyyy",now()), datepart("m",now()), 1),
"YYMMDD")# and 1#dateformat(createdate(datepart("yyyy",now()),
datepart("m",now()), DaysInMonth(now())), "YYMMDD")#
GROUP BY CAMCU
<cfif attributes.cat eq "USC">
UNION
SELECT SUM(CANTS) as NetSales, 'OTHER' as CAMCU
FROM DIXCUST.FDCASLSL2
Where 1=1
AND CACO NOT IN ('00036','00071')
AND CAMCU not IN ('HUN','VER')
and CADAT between
1#dateformat(createdate(datepart("yyyy",now()), datepart("m",now()), 1),
"YYMMDD")# and 1#dateformat(createdate(datepart("yyyy",now()),
datepart("m",now()), DaysInMonth(now())), "YYMMDD")#
Order by CAMCU
</cfif>
</cfquery>
<!--- Make a combined query --->
<cfset TotalSales = QueryNew("CAMCU, Yesterday, PYesterday, MTD, PMTD, YTD,
PYTD")>
<!--- make some rows in the query --->
<cfset newRow = QueryAddRow(TotalSales, YTDSales.recordcount)>
<!--- set the cells in the query --->
<cfset x = 0>
<cfloop query="YTDSales">
<cfset x = incrementvalue(x)>
<cfset temp = QuerySetCell(TotalSales, "CAMCU", CAMCU, x)>
<cfset temp = QuerySetCell(TotalSales, "YTD",
YTDSales.NetSales*exchangerate, x)>
<cfset comparevalue = YTDSales.CAMCU>
<cfif MTDSales.recordcount GT 0>
<cfloop query="MTDSales">
<cfif comparevalue eq MTDSales.CAMCU>
<cfif isnumeric(MTDSales.NetSales)>
<cfset temp =
QuerySetCell(TotalSales, "MTD", MTDSales.NetSales*exchangerate, x)>
<cfelse>
<cfset temp =
QuerySetCell(TotalSales, "MTD", "0", x)>
</cfif>
<cfbreak>
<cfelse>
<cfset temp = QuerySetCell(TotalSales,
"MTD", "0", x)>
</cfif>
</cfloop>
<cfelse>
<cfset temp = QuerySetCell(TotalSales, "MTD", "0", x)>
</cfif>
<cfif YesterdaySales.recordcount GT 0>
<cfloop query="YesterdaySales">
<cfif comparevalue eq YesterdaySales.CAMCU>
<cfif isnumeric(YesterdaySales.NetSales)>
<cfset temp =
QuerySetCell(TotalSales, "Yesterday", YesterdaySales.NetSales*exchangerate,
x)>
<cfelse>
<cfset temp =
QuerySetCell(TotalSales, "Yesterday", "0", x)>
</cfif>
<cfbreak>
<cfelse>
<cfset temp = QuerySetCell(TotalSales,
"Yesterday", "0", x)>
</cfif>
</cfloop>
<cfelse>
<cfset temp = QuerySetCell(TotalSales, "Yesterday", "0", x)>
</cfif>
<cfif PYesterdaySales.recordcount GT 0>
<cfloop query="PYesterdaySales">
<cfif comparevalue eq PYesterdaySales.CAMCU or
PYesterdaySales.CAMCU eq "ACE" and comparevalue eq "HUN">
<cfif isnumeric(PYesterdaySales.NetSales)>
<cfset temp =
QuerySetCell(TotalSales, "PYesterday",
PYesterdaySales.NetSales*exchangerate, x)>
<cfelse>
<cfset temp =
QuerySetCell(TotalSales, "PYesterday", "0", x)>
</cfif>
<cfbreak>
<cfelse>
<cfset temp = QuerySetCell(TotalSales,
"PYesterday", "0", x)>
</cfif>
</cfloop>
<cfelse>
<cfset temp = QuerySetCell(TotalSales, "PYesterday", "0",
x)>
</cfif>
<cfif PYTDSales.recordcount GT 0>
<cfloop query="PYTDSales">
<cfif comparevalue eq PYTDSales.CAMCU or
PYTDSales.CAMCU eq "ACE" and comparevalue eq "HUN">
<cfif isnumeric(PYTDSales.NetSales)>
<cfset temp =
QuerySetCell(TotalSales, "PYTD", PYTDSales.NetSales*exchangerate, x)>
<cfelse>
<cfset temp =
QuerySetCell(TotalSales, "PYTD", "0", x)>
</cfif>
<cfbreak>
<cfelse>
<cfset temp = QuerySetCell(TotalSales,
"PYTD", "0", x)>
</cfif>
</cfloop>
<cfelse>
<cfset temp = QuerySetCell(TotalSales, "PYTD", "0", x)>
</cfif>
<cfif PMTDSales.recordcount GT 0>
<cfloop query="PMTDSales">
<cfif comparevalue eq PMTDSales.CAMCU or
PMTDSales.CAMCU eq "ACE" and comparevalue eq "HUN">
<cfif isnumeric(PMTDSales.NetSales)>
<cfset temp =
QuerySetCell(TotalSales, "PMTD", PMTDSales.NetSales*exchangerate, x)>
<cfelse>
<cfset temp =
QuerySetCell(TotalSales, "PMTD", "0", x)>
</cfif>
<cfbreak>
<cfelse>
<cfset temp = QuerySetCell(TotalSales,
"PMTD", "0", x)>
</cfif>
</cfloop>
<cfelse>
<cfset temp = QuerySetCell(TotalSales, "PMTD", "0", x)>
</cfif>
</cfloop>
Robert Everland III
Web Developer Extraordinaire
Dixon Ticonderoga Company
http://www.dixonusa.com
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Your ad could be here. Monies from ads go to support these lists and provide more
resources for the community. http://www.fusionauthority.com/ads.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
