Link to home
Create AccountLog in
Avatar of griff77
griff77

asked on

Coldfusion query optimization

I have a report where I am currently looping over the dates in a month, and using 3 separate queries to sum sales data for a date.  For example:

<cfquery name="getYear1">                  
SELECT SUM(Sales) AS SUMSALES, SUM(ActualCost) AS SUMCOST, SUM(GrossActualMargin) AS SUMGAM
FROM dbo.Fact_Sales
WHERE 0=0
AND Date = <cfqueryparam cfsqltype="cf_sql_date" value="12/01/2010">
</cfquery>

<cfquery name="getYear2">                  
SELECT SUM(Sales) AS SUMSALES, SUM(ActualCost) AS SUMCOST, SUM(GrossActualMargin) AS SUMGAM
FROM dbo.Fact_Sales
WHERE 0=0
AND Date = <cfqueryparam cfsqltype="cf_sql_date" value="12/01/2009">
</cfquery>

<cfquery name="getYear3">                  
SELECT SUM(Sales) AS SUMSALES, SUM(ActualCost) AS SUMCOST, SUM(GrossActualMargin) AS SUMGAM
FROM dbo.Fact_Sales
WHERE 0=0
AND Date = <cfqueryparam cfsqltype="cf_sql_date" value="12/01/2008">
</cfquery>

Is there a more efficient way to accomplish this with 1 query that puts the information into individual columns like: "Date1Sales, Date1Cost, Date1GAM, Date2Sales, Date2Cost, Date2GAM, Date3Sales, Date3Cost, Date3GAM"?  Thanks in advance!
Avatar of sventhan
sventhan
Flag of United States of America image

-- code not tested
-- you could try this idea

select  date1.sumslaes, date1.SUMCOST,date1. SUMGAM, date2.sumslaes, date2.SUMCOST,date2. SUMGAM, date3.sumslaes, date3.SUMCOST,date3. SUMGAM

from
(
SELECT SUM(Sales) AS SUMSALES, SUM(ActualCost) AS SUMCOST, SUM(GrossActualMargin) AS SUMGAM
FROM dbo.Fact_Sales
WHERE 0=0
AND Date = <cfqueryparam cfsqltype="cf_sql_date" value="12/01/2010"
) date1,
(
           
SELECT SUM(Sales) AS SUMSALES, SUM(ActualCost) AS SUMCOST, SUM(GrossActualMargin) AS SUMGAM
FROM dbo.Fact_Sales
WHERE 0=0
AND Date = <cfqueryparam cfsqltype="cf_sql_date" value="12/01/2009">
) date2,
(
<cfquery name="getYear3">                  
SELECT SUM(Sales) AS SUMSALES, SUM(ActualCost) AS SUMCOST, SUM(GrossActualMargin) AS SUMGAM
FROM dbo.Fact_Sales
WHERE 0=0
AND Date = <cfqueryparam cfsqltype="cf_sql_date" value="12/01/2008">
) date3
Avatar of Hammo777
Hammo777

Corrected some typos and corrected returned column names:

select  date1.sumsales as date1sales, date1.SUMCOST as date1cost,date1.SUMGAM as date1gam, date2.sumsales as date2sales, date2.SUMCOST as date2cost,date2.SUMGAM as date2gam,
date3.sumsales as date3sales, date3.SUMCOST as date3cost,date3.SUMGAM as date3gam

from
(
SELECT SUM(Sales) AS SUMSALES, SUM(ActualCost) AS SUMCOST, SUM(GrossActualMargin) AS SUMGAM
FROM dbo.Fact_Sales
WHERE 0=0
AND Date =  <cfqueryparam cfsqltype="cf_sql_date" value="12/01/2010">

) date1,
(
           
SELECT SUM(Sales) AS SUMSALES, SUM(ActualCost) AS SUMCOST, SUM(GrossActualMargin) AS SUMGAM
FROM dbo.Fact_Sales
WHERE 0=0
AND Date = <cfqueryparam cfsqltype="cf_sql_date" value="12/01/2009">

) date2,
(
SELECT SUM(Sales) AS SUMSALES, SUM(ActualCost) AS SUMCOST, SUM(GrossActualMargin) AS SUMGAM
FROM dbo.Fact_Sales
WHERE 0=0
AND Date = <cfqueryparam cfsqltype="cf_sql_date" value="12/01/2008">

) date3

ASKER CERTIFIED SOLUTION
Avatar of gdemaria
gdemaria
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account