May 16, 2007 at 12:15 pm
Hi All,
I have the following query and it works great.
SELECT Export_AccountGroup.MEAT, Export_Versions.Version_Name,
SUM(Export_AccountGroup_Entity_Data.M1 - Export_AccountGroup_Entity_Data.BB) AS M1,
SUM(Export_AccountGroup_Entity_Data.M2 - Export_AccountGroup_Entity_Data.M1) AS M2,
SUM(Export_AccountGroup_Entity_Data.M3 - Export_AccountGroup_Entity_Data.M2) AS M3,
SUM(Export_AccountGroup_Entity_Data.M4 - Export_AccountGroup_Entity_Data.M3) AS M4,
SUM(Export_AccountGroup_Entity_Data.M5 - Export_AccountGroup_Entity_Data.M4) AS M5,
SUM(Export_AccountGroup_Entity_Data.M6 - Export_AccountGroup_Entity_Data.M5) AS M6,
SUM(Export_AccountGroup_Entity_Data.M7 - Export_AccountGroup_Entity_Data.M6) AS M7,
SUM(Export_AccountGroup_Entity_Data.M8 - Export_AccountGroup_Entity_Data.M7) AS M8,
SUM(Export_AccountGroup_Entity_Data.M9 - Export_AccountGroup_Entity_Data.M8) AS M9,
SUM(Export_AccountGroup_Entity_Data.M10 - Export_AccountGroup_Entity_Data.M9) AS M10,
SUM(Export_AccountGroup_Entity_Data.M11 - Export_AccountGroup_Entity_Data.M10) AS M11,
SUM(Export_AccountGroup_Entity_Data.M12 - Export_AccountGroup_Entity_Data.M11) AS M12
FROM Export_AccountGroup INNER JOIN Export_AccountGroup_Entity_Data ON
Export_AccountGroup.AccountGroup_ID = Export_AccountGroup_Entity_Data.AccountGroup_ID INNER JOIN Export_Versions ON Export_AccountGroup_Entity_Data.Version_ID = Export_Versions.Version_ID
GROUP BY Export_AccountGroup.MEAT, Export_Versions.Version_Name
HAVING (Export_AccountGroup.MEAT = N'REVENUE'
OR Export_AccountGroup.MEAT = N'COGS'
OR Export_AccountGroup.MEAT = N'General Expense')
ORDER BY dbo.Export_AccountGroup.MEAT
What I would like to do is the following, and I am not even sure it can be done:
In a different query, not the same one as this, I'd like to be able to calculate Gross Margin, which is Revenue - COGS. Is there a way to compose a query to calculate this and have the same columns?
Thanks!
Paul
March 28, 2009 at 1:26 am
yes
March 28, 2009 at 3:36 am
Hi Paul
Quite possibly, but we'll need more information from you. Firstly, let's have a look at the original query. I've added table aliases to make it easier to read, and replaced the HAVING (which filters the aggregated result set) with a WHERE, which filters the data pre-aggregation. This should make the query run faster. Put the N' back in if your datatype requires it - probably not. Here it is:
SELECT g.MEAT, v.Version_Name,
SUM(e.M1 - e.BB) AS M1,
SUM(e.M2 - e.M1) AS M2,
SUM(e.M3 - e.M2) AS M3,
SUM(e.M4 - e.M3) AS M4,
SUM(e.M5 - e.M4) AS M5,
SUM(e.M6 - e.M5) AS M6,
SUM(e.M7 - e.M6) AS M7,
SUM(e.M8 - e.M7) AS M8,
SUM(e.M9 - e.M8) AS M9,
SUM(e.M10 - e.M9) AS M10,
SUM(e.M11 - e.M10) AS M11,
SUM(e.M12 - e.M11) AS M12
FROM Export_AccountGroup g
INNER JOIN Export_AccountGroup_Entity_Data e
ON g.AccountGroup_ID = e.AccountGroup_ID
INNER JOIN Export_Versions v
ON e.Version_ID = v.Version_ID
WHERE g.MEAT IN ('REVENUE', 'COGS', 'General Expense')
GROUP BY g.MEAT, v.Version_Name
ORDER BY g.MEAT
It's easier now (for me, anyway) to see that 'REVENUE' and 'COGS' will be on different rows of the output from this query. How are any two rows (one with MEAT = 'REVENUE' and the other with MEAT = 'COGS') related, is it Version_Name? If it is, then you could use a CTE like this to join 'REVENUE' and 'COGS' together:
;WITH MyCTE
(MEAT, Version_Name, M1, M2, M3, M4, M5, M6, M7, M8, M9, M10, M11, M12)
AS (
SELECT g.MEAT, v.Version_Name,
SUM(e.M1 - e.BB) AS M1,
SUM(e.M2 - e.M1) AS M2,
SUM(e.M3 - e.M2) AS M3,
SUM(e.M4 - e.M3) AS M4,
SUM(e.M5 - e.M4) AS M5,
SUM(e.M6 - e.M5) AS M6,
SUM(e.M7 - e.M6) AS M7,
SUM(e.M8 - e.M7) AS M8,
SUM(e.M9 - e.M8) AS M9,
SUM(e.M10 - e.M9) AS M10,
SUM(e.M11 - e.M10) AS M11,
SUM(e.M12 - e.M11) AS M12
FROM Export_AccountGroup g
INNER JOIN Export_AccountGroup_Entity_Data e
ON g.AccountGroup_ID = e.AccountGroup_ID
INNER JOIN Export_Versions v
ON e.Version_ID = v.Version_ID
WHERE g.MEAT IN ('REVENUE', 'COGS', 'General Expense')
GROUP BY g.MEAT, v.Version_Name
ORDER BY g.MEAT)
--
SELECT r.*, '#' AS '#', c.*
FROM MyCTE r
LEFT JOIN MyCTE c
ON c.Version_Name = r.Version_Name AND c.MEAT = 'COGS'
WHERE r.MEAT = 'REVENUE'
With all of the values you need for your calculation now on the same row, you can calculate Gross Margin.
So: how are any two rows related? Is it Version_Name?
It would help immensely if you could post some sample data - see the link below for instructions.
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 28, 2009 at 6:12 pm
David (3/28/2009)
yes
Heh... bad day, David?
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply