Tally Table + Running Total

Question

Tally Table + Running Total

karthik M

One Orange Chip

Points: 29139
More actions
May 16, 2008 at 9:44 am

#71954

All,
I have the below data stored in a table.
Dec 31 2007 12:00AM
Nov 30 2007 12:00AM
Oct 31 2007 12:00AM
Sep 30 2007 12:00AM
Aug 31 2007 12:00AM
Jul 31 2007 12:00AM
Jun 30 2007 12:00AM
May 31 2007 12:00AM
Apr 30 2007 12:00AM
Mar 31 2007 12:00AM
Feb 28 2007 12:00AM
Jan 31 2007 12:00AM
Dec 31 2006 12:00AM
Nov 30 2006 12:00AM
Oct 31 2006 12:00AM
Sep 30 2006 12:00AM
Aug 31 2006 12:00AM
Jul 31 2006 12:00AM
Jun 30 2006 12:00AM
May 31 2006 12:00AM
Apr 30 2006 12:00AM
Mar 31 2006 12:00AM
Feb 28 2006 12:00AM
Jan 31 2006 12:00AM
Dec 31 2005 12:00AM
Nov 30 2005 12:00AM
Oct 31 2005 12:00AM
Sep 30 2005 12:00AM
Aug 31 2005 12:00AM
Jul 31 2005 12:00AM
Jun 30 2005 12:00AM
May 31 2005 12:00AM
Apr 30 2005 12:00AM
Mar 31 2005 12:00AM
Feb 28 2005 12:00AM
I want to create running total based on the year.
for example,
1 Dec 31 2007 12:00AM
2Nov 30 2007 12:00AM
3 Oct 31 2007 12:00AM
4 Sep 30 2007 12:00AM
5 Aug 31 2007 12:00AM
6 Jul 31 2007 12:00AM
7 Jun 30 2007 12:00AM
8 May 31 2007 12:00AM
9 Apr 30 2007 12:00AM
10 Mar 31 2007 12:00AM
11 Feb 28 2007 12:00AM
12 Jan 31 2007 12:00AM
1 Dec 31 2006 12:00AM
2 Nov 30 2006 12:00AM
3 Oct 31 2006 12:00AM
4 Sep 30 2006 12:00AM
5 Aug 31 2006 12:00AM
6 Jul 31 2006 12:00AM
7 Jun 30 2006 12:00AM
8 May 31 2006 12:00AM
9 Apr 30 2006 12:00AM
10 Mar 31 2006 12:00AM
11 Feb 28 2006 12:00AM
12 Jan 31 2006 12:00AM
1 Dec 31 2005 12:00AM
2 Nov 30 2005 12:00AM
3 Oct 31 2005 12:00AM
4 Sep 30 2005 12:00AM
5 Aug 31 2005 12:00AM
6 Jul 31 2005 12:00AM
7 Jun 30 2005 12:00AM
8 May 31 2005 12:00AM
9 Apr 30 2005 12:00AM
10 Mar 31 2005 12:00AM
11 Feb 28 2005 12:00AM
karthik

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 1

If you want that kind of help, then you have to "follow the rules" for posting data. Please click on the URL in my signature line and provide the table CREATE statement, the data in the usable format indicated in the article, and the primary key.

--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)

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 2

Actually, just select MONTH() from each date and subtract it from 13.

--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)

karthik M One Orange Chip Points: 29139 More actions · Answer 3

As you said if i subtract it from 13 it will give correct result. But assume if any month is missed in the sequence,out query will give wrong sequence. How to rectify it ?

karthik

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 4

Tally table join with MonthEnd calculation on "N" should do it. Give it a try... 😉

--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)