Help with Nested SUM query to calculate difference between 2 months
-
Hi,
i have these two queries;
[Company]
,[POD0]
,SUM([YTDPlanActivity])
,SUM([YTDActualActivity])
,SUM([YTDDifferenceActivity])
,SUM([YTDPlanCost])
,SUM([YTDActualCost])
,SUM([YTDDifferenceCost])
,Max (Period)
FROM [FinanceReport]
GROUP BY
[Company]
,[POD0]
ORDER BY
[Company]
,SUM([YTDDifferenceCost])DESC
SELECT
[Company]
,[POD0]
,SUM([YTDPlanActivity])
,SUM([YTDActualActivity])
,SUM([YTDDifferenceActivity])
,SUM([YTDPlanCost])
,SUM([YTDActualCost])
,SUM([YTDDifferenceCost])
,MIN (Period)
FROM [FinanceReport]
GROUP BY
[Company]
,[POD0]
ORDER BY
[Company]
,SUM([YTDDifferenceCost])DESC
I wanted to add a new sum within the first query which calculates the SUM([YTDDifferenceActivity]) difference between both periods.
What is the simplest way to do this?
-
Your query is not very clear. I am putting up a scenario, hope this is what you are looking for:
CREATE TABLE test
(
process_name varchar(10), process_date datetime, process_value int
);
INSERT INTO test
(process_name, process_date,process_value)
VALUES
('P1',GETDATE(),10),
('P2',GETDATE()+1,20),
('P3',GETDATE()+2,25),
('P4',GETDATE()+3,30),
('P5',GETDATE()+4,40);
WITH abc
AS
( SELECT row_number() over( order by process_date) rownum,process_name, process_date,process_value
from test)
select a1.rownum r1,
a1.process_name,
a1.process_date,
a1.process_value ,
a2.process_value process_value_previous_day,
a1.process_value-ISNULL(a2.process_value,0) value_diff
from abc a1 left join abc a2
on a1.rownum = a2.rownum+1;
| R1 | PROCESS_NAME | PROCESS_DATE | PROCESS_VALUE | PROCESS_VALUE_PREVIOUS_DAY | VALUE_DIFF |
-------------------------------------------------------------------------------------------------------------
| 1 | P1 | July, 21 2012 20:01:45-0700 | 10 | (null) | 10 |
| 2 | P2 | July, 22 2012 20:01:45-0700 | 20 | 10 | 10 |
| 3 | P3 | July, 23 2012 20:01:45-0700 | 25 | 20 | 5 |
| 4 | P4 | July, 24 2012 20:01:45-0700 | 30 | 25 | 5 |
| 5 | P5 | July, 25 2012 20:01:45-0700 | 40 | 30 | 10 |
If this is not what you intended. Pls provide some sample data, I will try to create a query.
-Lokesh
~ Lokesh Vij
Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
-
Your question is not clearly explained but what i understand is you want difference of SUM([YTDDifferenceActivity]) between both periods.But in both the queries only period is different and SUM([YTDDifferenceActivity]) is same. So if u get the difference then also it is not correct.
Bye
-
justmohit (7/22/2012)
Your question is not clearly explained but what i understand is you want difference of SUM([YTDDifferenceActivity]) between both periods.But in both the queries only period is different and SUM([YTDDifferenceActivity]) is same. So if u get the difference then also it is not correct.Bye
I guess I'd have to say the same thing about your answer. 😉 Would you clarify, please?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply