Cumulative Matrix - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Cumulative Matrix

Author

Topic

jfm
Posting Yak Master

145 Posts

Posted - 2012-09-20 : 06:15:44

Hi guys,

I have a matrix query and I don't know how to modify the query in order to have the cumulative between its periods.

This is how it looks:

USE db
SELECT date, SUM (Balance) as premiere,
SUM(CASE WHEN date = '1' THEN Balance END) as '1' ,
SUM(CASE WHEN date = '2' THEN Balance END) as '2' ,
SUM(CASE WHEN date = '3' THEN Balance END) as '3' ,
SUM(CASE WHEN date = '4' THEN Balance END) as '4' ,
SUM(CASE WHEN date = '5' THEN Balance END) as '5'
FROM Me
GROUP BY date
ORDER BY date

Thanks

Any idea?

Thanks !

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-20 : 07:36:03

Did you mean something like this?

USE db
SELECT date,
       SUM(Balance) AS premiere,
       SUM(CASE WHEN date = '1' THEN Balance END) AS '1',
       SUM(CASE WHEN date = '2' THEN Balance END) AS '2',
       SUM(CASE WHEN date IN ('1', '2') THEN Balance END) AS 'Cumulative_2',
       SUM(CASE WHEN date = '3' THEN Balance END) AS '3',
       SUM(CASE WHEN date IN ('1','2','3') THEN Balance END) AS 'Cumulative_3',
       SUM(CASE WHEN date = '4' THEN Balance END) AS '4',
       SUM(CASE WHEN date IN ('1','2','3','4') THEN Balance END) AS 'Cumulative_4',
       SUM(CASE WHEN date = '5' THEN Balance END) AS '5',
       SUM(CASE WHEN date IN ('1','2','3','4','5') THEN Balance END) AS 'Cumulative_5'
FROM   Me
GROUP BY
       date
ORDER BY
       date

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-20 : 07:40:53

quote:
Thanks for your reply.

I just need the cumulative balance for each period

For the regular balance i did the extraction to excel.

I have more than 100 different periods in my query, you think there is another way?

Are periods integers or are they random strings? If the are integers, you can use something like this (the example below for period 5):

       SUM(CASE WHEN date <= 5 THEN Balance END) AS 'Cumulative_5'

jfm
Posting Yak Master

145 Posts

Posted - 2012-09-20 : 07:41:21

Thanks for your reply.

I just need the cumulative balance for each period

For the regular balance i did the extraction to excel.

I have more than 100 different periods in my query, you think there is another way?

jfm
Posting Yak Master

145 Posts

Posted - 2012-09-20 : 07:47:07

Thanks again for the reply.

No, if I use that example, I will have the Sum of the total balance at the end.

In this case, I need, in each period of the matrix 1,2,3,4 ... 150 the cumulative.

So I will have:

Period 1= Balance Period 1
Period 2= Balance Period 1+ Balance Period 2
Period 3= Balance Period 2+ Balance Period 3

(...) And so on .

jfm
Posting Yak Master

145 Posts

Posted - 2012-09-20 : 09:47:36

Any idea guys?

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-09-20 : 09:52:14

don't quite understand what do you want here. Maybe if you can illustrate with an example and the expected result

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

jfm
Posting Yak Master

145 Posts

Posted - 2012-09-20 : 10:21:03

Hi,

Well I have the following matrix:

USE db
SELECT date, SUM (Balance) as premiere,
SUM(CASE WHEN date = '1' THEN Balance END) as 'FV' ,
SUM(CASE WHEN date = '2' THEN Balance END) as '2' ,
SUM(CASE WHEN date = '3' THEN Balance END) as '3' ,
SUM(CASE WHEN date = '4' THEN Balance END) as '4' ,
SUM(CASE WHEN date = '5' THEN Balance END) as '5'
FROM Me
GROUP BY date
ORDER BY date

The output of this matrix will be:

Date | FV | 2 | ( ... ) | 150
---------------------------
1 | 10 | 5 | 5 | 3 |
---------------------------
2 | 20 | 3 | 5 | 5 |
---------------------------
(...) | 10 | 2 | 1 | 4 |
---------------------------
150 | 15 | 1 | 2 | 1 |
---------------------------

And I also need the cumulative of this same matrix:

Date | FV | 2 | ( ... ) | 150
---------------------------
1 | 10 | 15 | 20 | 23 |
---------------------------
2 | 20 | 23 | 28 | 33 |
---------------------------
(...) | 10 | 12 | 13 | 17 |
---------------------------
150 | 15 | 16 | 18 | 19 |
---------------------------

Which is:

Date | FV | 2 | ( ... ) | 150
---------------------------------
1 | 10 | 10+5 | 15+5 | 20+3 |
---------------------------------
2 | 20 | 20+3 | 23+5 | 28+5 |
---------------------------------

And so on.

But using the matrix of the beginning,

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-09-20 : 10:46:28

[code]SELECT date, SUM (Balance) as premiere,
SUM(CASE WHEN date <= '1' THEN Balance END) as 'FV' ,
SUM(CASE WHEN date <= '2' THEN Balance END) as '2' ,
SUM(CASE WHEN date <= '3' THEN Balance END) as '3' ,
SUM(CASE WHEN date <= '4' THEN Balance END) as '4' ,
SUM(CASE WHEN date <= '5' THEN Balance END) as '5'
FROM Me
GROUP BY date
ORDER BY date[/code]

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

jfm
Posting Yak Master

145 Posts

Posted - 2012-09-20 : 11:40:24

Thanks for the reply.

Your way seems to be in the right path.

I need to double check the file, because is not working in all the rows. And is a massive file.

Whenever i'll find the solution to my possible error, I will post the answer.

Thanks again,

J

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources