Possible Aggregate Function?? Stuck.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Possible Aggregate Function?? Stuck.

Author

Topic

Maccaman
Starting Member

16 Posts

Posted - 2009-07-20 : 06:39:33

Hi all,
This is my first post here. I have the following table (which is only part of the table. The table itself has about 1000 records with a larger number of about 750 different values of QUAD):

QUAD DATE TEXTA PENSA
10 2/06/2003 62.55 7.35
10 2/06/2003 62.55 7.35
10 2/06/2003 62.55 7.35
10 2/06/2003 62.55 7.35
10 2/06/2003 62.55 3.75
20 10/12/2003 38.00 2.50
20 10/12/2003 38.00 2.50
20 10/12/2003 38.00 2.50
20 10/12/2003 38.00 2.50
20 10/12/2003 38.00 2.50
30 12/04/2005 50.4 4.50
30 12/04/2005 50.4 4.50
30 12/04/2005 50.4 2.50
30 12/04/2005 50.4 2.50
30 12/04/2005 50.4 2.50

I need to create a query that for every QUAD value, the PENSA's are summed and subtracted from TEXTA. So for example, for QUAD = 10, SUM(PENSA) = 7.35+7.35....+3.75 = 33.15. So TEXTA - PENSA = 29.4. In the query I'd then like to have this new value added in a column called difference where I would end up with the following table:

QUAD DATE TEXTA PENSA DIFFERENCE
10 2/06/2003 62.55 7.35 29.4
10 2/06/2003 62.55 7.35 29.4
10 2/06/2003 62.55 7.35 29.4
10 2/06/2003 62.55 7.35 29.4
10 2/06/2003 62.55 3.75 29.4
20 10/12/2003 38.00 2.50 25.5
20 10/12/2003 38.00 2.50 25.5
20 10/12/2003 38.00 2.50 25.5
20 10/12/2003 38.00 2.50 25.5
20 10/12/2003 38.00 2.50 25.5
30 12/04/2005 50.40 4.50 33.9
30 12/04/2005 50.40 4.50 33.9
30 12/04/2005 50.40 2.50 33.9
30 12/04/2005 50.40 2.50 33.9
30 12/04/2005 50.40 2.50 33.9

The second query, and to be honest the query I would desire more than the first, is to have these values subtracted and stored as the records go down (poor description I know) for each QUAD. So the resulting table would be:

QUAD DATE TEXTA PENSA DIFFERENCE2
10 2/06/2003 62.55 7.35 55.20
10 2/06/2003 62.55 7.35 47.85
10 2/06/2003 62.55 7.35 40.50
10 2/06/2003 62.55 7.35 33.15
10 2/06/2003 62.55 3.75 29.40
20 10/12/2003 38.00 2.50 35.50
20 10/12/2003 38.00 2.50 33.00
20 10/12/2003 38.00 2.50 30.50
20 10/12/2003 38.00 2.50 28.00
20 10/12/2003 38.00 2.50 25.50
30 12/04/2005 50.40 4.50 45.90
30 12/04/2005 50.40 4.50 41.40
30 12/04/2005 50.40 2.50 38.90
30 12/04/2005 50.40 2.50 36.40
30 12/04/2005 50.40 2.50 33.90

As you can see, for the first value of QUAD (in this case 10) PENSA is subtracted from TEXTA to get DIFFERENCE2. For the remaining values where QUAD = 10, DIFFERENCE2 is obtained by subtracting PENSA from the previous row's value of DIFFERENCE2. When QUAD = 20 then, the process starts again.

I've been trying to find the solution for quite a while for this, so any help would be greatly appreciated. I don't even know if the query is possible, so I look forward for any proposed solutions.

Cheers,
Maccaman

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-20 : 06:46:04

[code]DECLARE @Sample TABLE
(
Quad INT,
Date DATETIME,
TextA MONEY,
PensA MONEY
)

INSERT @Sample
SELECT 10, '02/06/2003', 62.55, 7.35 UNION ALL
SELECT 10, '02/06/2003', 62.55, 7.35 UNION ALL
SELECT 10, '02/06/2003', 62.55, 7.35 UNION ALL
SELECT 10, '02/06/2003', 62.55, 7.35 UNION ALL
SELECT 10, '02/06/2003', 62.55, 3.75 UNION ALL
SELECT 20, '10/12/2003', 38.00, 2.50 UNION ALL
SELECT 20, '10/12/2003', 38.00, 2.50 UNION ALL
SELECT 20, '10/12/2003', 38.00, 2.50 UNION ALL
SELECT 20, '10/12/2003', 38.00, 2.50 UNION ALL
SELECT 20, '10/12/2003', 38.00, 2.50 UNION ALL
SELECT 30, '12/04/2005', 50.40, 4.50 UNION ALL
SELECT 30, '12/04/2005', 50.40, 4.50 UNION ALL
SELECT 30, '12/04/2005', 50.40, 2.50 UNION ALL
SELECT 30, '12/04/2005', 50.40, 2.50 UNION ALL
SELECT 30, '12/04/2005', 50.40, 2.50

SELECT Quad,
Date,
TextA,
PensA,
MAX(TextA) OVER (PARTITION BY Quad) - SUM(PensA) OVER (PARTITION BY Quad) AS Difference
FROM @Sample[/code]

N 56°04'39.26"
E 12°55'05.63"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-20 : 07:00:52

[code]
DECLARE @SAMPLE TABLE
(
QUAD int,
DATE datetime,
TEXTA decimal(10,2),
PENSA decimal(10,2)
)
INSERT INTO @SAMPLE
SELECT 10, '2/06/2003', 62.55, 7.35 UNION ALL
SELECT 10, '2/06/2003', 62.55, 7.35 UNION ALL
SELECT 10, '2/06/2003', 62.55, 7.35 UNION ALL
SELECT 10, '2/06/2003', 62.55, 7.35 UNION ALL
SELECT 10, '2/06/2003', 62.55, 3.75 UNION ALL
SELECT 20, '10/12/2003', 38.00, 2.50 UNION ALL
SELECT 20, '10/12/2003', 38.00, 2.50 UNION ALL
SELECT 20, '10/12/2003', 38.00, 2.50 UNION ALL
SELECT 20, '10/12/2003', 38.00, 2.50 UNION ALL
SELECT 20, '10/12/2003', 38.00, 2.50 UNION ALL
SELECT 30, '12/04/2005', 50.4, 4.50 UNION ALL
SELECT 30, '12/04/2005', 50.4, 4.50 UNION ALL
SELECT 30, '12/04/2005', 50.4, 2.50 UNION ALL
SELECT 30, '12/04/2005', 50.4, 2.50 UNION ALL
SELECT 30, '12/04/2005', 50.4, 2.50

;WITH DATA (QUAD, DATE, TEXTA, PENSA, ROW_NO)
AS
(
SELECT QUAD, DATE, TEXTA, PENSA,
ROW_NO = ROW_NUMBER() OVER (PARTITION BY QUAD ORDER BY DATE)
FROM @SAMPLE
)
SELECT D.QUAD, D.DATE, D.TEXTA, D.PENSA, DIFF2 = D.TEXTA - A.ACCUM
FROM DATA D
CROSS APPLY
(
SELECT ACCUM = SUM(PENSA)
FROM DATA X
WHERE X.QUAD = D.QUAD
AND X.ROW_NO <= D.ROW_NO
) A

/*
QUAD DATE TEXTA PENSA DIFF2
----------- ----------- ------------ ------------ -------
10 2003-02-06 62.55 7.35 55.20
10 2003-02-06 62.55 7.35 47.85
10 2003-02-06 62.55 7.35 40.50
10 2003-02-06 62.55 7.35 33.15
10 2003-02-06 62.55 3.75 29.40
20 2003-10-12 38.00 2.50 35.50
20 2003-10-12 38.00 2.50 33.00
20 2003-10-12 38.00 2.50 30.50
20 2003-10-12 38.00 2.50 28.00
20 2003-10-12 38.00 2.50 25.50
30 2005-12-04 50.40 4.50 45.90
30 2005-12-04 50.40 4.50 41.40
30 2005-12-04 50.40 2.50 38.90
30 2005-12-04 50.40 2.50 36.40
30 2005-12-04 50.40 2.50 33.90

(15 row(s) affected)
*/
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Maccaman
Starting Member

16 Posts

Posted - 2009-07-20 : 07:29:50

Wow thanks, I have a lot to learn. How could I do this if my table name is t_finance?

Can I do it without using:

DECLARE @SAMPLE TABLE
(
QUAD int,
DATE datetime,
TEXTA decimal(10,2),
PENSA decimal(10,2)
)
INSERT INTO @SAMPLE
SELECT 10, '2/06/2003', 62.55, 7.35 UNION ALL
SELECT 10, '2/06/2003', 62.55, 7.35 UNION ALL
SELECT 10, '2/06/2003', 62.55, 7.35 UNION ALL
SELECT 10, '2/06/2003', 62.55, 7.35 UNION ALL
SELECT 10, '2/06/2003', 62.55, 3.75 UNION ALL
SELECT 20, '10/12/2003', 38.00, 2.50 UNION ALL
SELECT 20, '10/12/2003', 38.00, 2.50 UNION ALL
SELECT 20, '10/12/2003', 38.00, 2.50 UNION ALL
SELECT 20, '10/12/2003', 38.00, 2.50 UNION ALL
SELECT 20, '10/12/2003', 38.00, 2.50 UNION ALL
SELECT 30, '12/04/2005', 50.4, 4.50 UNION ALL
SELECT 30, '12/04/2005', 50.4, 4.50 UNION ALL
SELECT 30, '12/04/2005', 50.4, 2.50 UNION ALL
SELECT 30, '12/04/2005', 50.4, 2.50 UNION ALL
SELECT 30, '12/04/2005', 50.4, 2.50
ect?

So I would be pulling directly from the table because I have around 1000 records.
Also, does the 'INSERT INTO' statement actually change the table? i.e. if doing this in SQL server, would the table now contain DIFF2?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-20 : 07:38:50

The table declaration is only there to mimic your environment, of which we have no access.

You only need the SELECT queries, and you have to replace the @Sample table name with the table name used in your environment.

N 56°04'39.26"
E 12°55'05.63"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-20 : 07:40:34

Déjà vu

KH
[spoiler]Time is always against us[/spoiler]

Maccaman
Starting Member

16 Posts

Posted - 2009-07-20 : 07:41:26

Thank-you very much. I feel dumb as I have only started using SQL, and don't know much yet.

Cheers

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-20 : 07:43:23

quote:
Originally posted by Maccaman

Thank-you very much. I feel dumb as I have only started using SQL, and don't know much yet.

Cheers

Don't worry, it is a phase most of us had gone through.

KH
[spoiler]Time is always against us[/spoiler]

Maccaman
Starting Member

16 Posts

Posted - 2009-07-20 : 08:17:21

Thanks again Khtan and Peso.
Peso when I try and run the query you provided I obtain the following error:

Msg 156, Level 15, State 1, Line 34
Incorrect syntax near the keyword 'CROSS'.
Msg 102, Level 15, State 1, Line 40
Incorrect syntax near 'A'.

Im using SQL Server. I tried fixing it when using my table (t_finance) but had no luck, and I don't yet know enough about SQL to fix the error.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-20 : 08:23:55

sorry . . my bad. edited the post. Should be CROSS APPLY

KH
[spoiler]Time is always against us[/spoiler]

Maccaman
Starting Member

16 Posts

Posted - 2009-07-20 : 08:54:44

thanks again. That is unreal :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources