Query help - 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

Query help

Author

Topic

drpkrupa
Yak Posting Veteran

74 Posts

Posted - 2006-10-09 : 17:54:25

I have table with follwing colu and rows of data.
Year account# Div Balance Month
2006 6010 DI -3265.48000 1
2006 6010 DI -1778.80000 2
2006 6010 DI -3124.83000 3
2006 6010 DI -22.67000 4
2006 6011 DI -35.48000 1
2006 6011 DI -178.80000 2
2006 6011 DI -31.83000 3
2006 6011 DI -25.67000 4
2006 6012 DI 3265.48000 1
2006 6012 DI 1778.80000 2
2006 6012 DI 3124.83000 3
2006 6012 DI 2572.67000 4

I need answer of my query like. I want a add one more colu at the end which containt total balance
2006 6010 DI -3265.48000 1 -3265.48000
2006 6010 DI -1778.80000 2 -5044.28
2006 6010 DI -3124.83000 3 -8169.11
2006 6010 DI -2572.67000 4 -10741.78

That last column i want a add balace for piror month too. Like
Jan - Jan
Fen - Jan+Feb
Mar - Jan-+Feb+Mar
etc..

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-10-09 : 20:57:44

Where do you want to show data?
If you use Reports, make sue of Running Total feature

or refer
http://sqlservercentral.com/cs/blogs/amachanic/archive/2006/02/28/508.aspx

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-10 : 04:02:53

[code]-- Prepare test data
DECLARE @Test TABLE ([Year] SMALLINT, [Account#] SMALLINT, Div VARCHAR(2), Balance SMALLMONEY, [Month] TINYINT)

INSERT @Test
SELECT 2006, 6010, 'DI', -3265.48000, 1 UNION ALL
SELECT 2006, 6010, 'DI', -1778.80000, 2 UNION ALL
SELECT 2006, 6010, 'DI', -3124.83000, 3 UNION ALL
SELECT 2006, 6010, 'DI', -22.67000, 4 UNION ALL
SELECT 2006, 6011, 'DI', -35.48000, 1 UNION ALL
SELECT 2006, 6011, 'DI', -178.80000, 2 UNION ALL
SELECT 2006, 6011, 'DI', -31.83000, 3 UNION ALL
SELECT 2006, 6011, 'DI', -25.67000, 4 UNION ALL
SELECT 2006, 6012, 'DI', 3265.48000, 1 UNION ALL
SELECT 2006, 6012, 'DI', 1778.80000, 2 UNION ALL
SELECT 2006, 6012, 'DI', 3124.83000, 3 UNION ALL
SELECT 2006, 6012, 'DI', 2572.67000, 4

-- Do the work
SELECT t1.[Year],
t1.[Month],
t1.[Account#],
t1.Balance,
(SELECT SUM(t2.Balance) FROM @Test t2 WHERE t2.[Year] = t1.[Year] AND t2.[Account#] = t1.[Account#] AND t2.[Month] <= t1.[Month]) RunningTotal
FROM @Test t1[/code]

Peter Larsson
Helsingborg, Sweden

drpkrupa
Yak Posting Veteran

74 Posts

Posted - 2006-10-10 : 10:13:35

Thanks for you help.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-10-10 : 10:21:10

It seems Cursor is more faster in this case if you read the link I posted

Madhivanan

Failing to plan is Planning to fail

drpkrupa
Yak Posting Veteran

74 Posts

Posted - 2006-10-10 : 11:43:05

I tried to access that link and gave me error.
I have one more question on the same query.
SELECT t1.[Year],t1.[Month],t1.[Account#],t1.Balance,
(SELECT SUM(t2.Balance) FROM @Test t2 WHERE t2.[Year] = t1.[Year] AND t2.[Account#] = t1.[Account#] AND t2.[Month] <= t1.[Month]) RunningTotal
FROM @Test t1

This query works fine but the problem is if i have account with missing month then i want a add row and add display the previous month balance.
Ex:
2006 6010 -10 1
2006 6010 -20 2
2006 6010 -30 4
2006 6010 -40 6
2006 6011 10 1
2006 6011 20 3

In this case query retun:
6010 1 -10
6010 2 -30
6010 4 -60
6010 6 -100
6011 1 10
6011 3 30
I want a add more row in there like: for each missing month. (month 3 and 5 for 6010 and month 2 for 6011)
6010 1 -10
6010 2 -30
6010 3 -30
6010 4 -60
6010 5 -60
6010 6 -100
6011 1 10
6011 2 10
6011 3 30

drpkrupa
Yak Posting Veteran

74 Posts

Posted - 2006-10-10 : 15:24:13

Any update.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-10 : 15:57:55

CROSS JOIN a tally table and there you have the missing months.

Peter Larsson
Helsingborg, Sweden

drpkrupa
Yak Posting Veteran

74 Posts

Posted - 2006-10-10 : 16:19:22

how can i find missing month.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-10 : 16:58:58

I told you. With a tally table.
But since you didn't bother to investigate what that is, I figure this is homework.

-- Prepare test data
DECLARE @Test TABLE ([Year] SMALLINT, [Account#] SMALLINT, Balance SMALLMONEY, [Month] TINYINT)

INSERT	@Test
SELECT	2006, 6010, -10, 1 UNION ALL
SELECT	2006, 6010, -20, 2 UNION ALL
SELECT	2006, 6010, -30, 4 UNION ALL
SELECT	2006, 6010, -40, 6 UNION ALL
SELECT	2006, 6011, 10, 1 UNION ALL
SELECT	2006, 6011, 20, 3

-- Do the work
SELECT		d.[Account#],
		t.m [Month],
		(SELECT SUM(t2.Balance) FROM @Test t2 WHERE t2.[Year] = d.[Year] AND t2.[Account#] = d.[Account#] AND t2.[Month] <= t.m) RunningTotal
FROM		(
			SELECT		[Year],
					[Account#],
					1 mi,
					MAX([Month]) ma
			FROM		@Test
			GROUP BY	[Year],
					[Account#]
		) d			
INNER JOIN	(
			SELECT 1 m UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL
			SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12
		) t ON t.m BETWEEN d.mi AND d.ma
LEFT JOIN	@Test t1 ON t1.[Year] = d.[Year] AND t1.[Month] = t.m AND t1.[Account#] = d.[Account#]
ORDER BY	d.[Year],
		d.[Account#],
		t.m

Peter Larsson
Helsingborg, Sweden

drpkrupa
Yak Posting Veteran

74 Posts

Posted - 2006-10-11 : 11:08:03

Thanks man.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources