Value of the previous row in the next row

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 2000 Forums

SQL Server Development (2000)

Value of the previous row in the next row

Author

Topic

ayamas
Aged Yak Warrior

552 Posts

Posted - 2006-06-07 : 01:58:58

Hi Guys,

I want to display a report like this

Date Balance on Date Credit Debit Current Balance
02/05/06 0 1200 0 1200
05/05/06 1200 500 0 1700
10/05/06 1700 0 200 1500
15/05/06 1500 200 0 1700

I mean for the new date the balance of the previous day transaction should be displayed.
How can it be done?
Any help is greately appreciated.
Thanks in advance

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-07 : 02:11:35

Is it like this ?

declare @table table
(
	[Date]			datetime,
	[Balance on Date]	int,
	[Credit] 		int,
	[Debit]			int,
	[Current Balance]	int
)
set dateformat dmy
insert into @table
select '02/05/06',    0, 1200,   0, 1200 union all
select '05/05/06', 1200,  500,   0, 1700 union all
select '10/05/06', 1700,    0, 200, 1500 union all
select '15/05/06', 1500,  200,   0, 1700


select 	*,
	[Previous Balance] = (select top 1 [Current Balance] from @table x where x.[Date] < t.[Date] order by x.[Date] desc)
from	@table t
order by [Date]

ayamas
Aged Yak Warrior

552 Posts

Posted - 2006-06-07 : 02:17:40

Thanks for ur reply
But khtan I cannot hard code the values in my query.I want it to be dynamic.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-07 : 02:18:19

"I cannot hard code the values in my query.I want it to be dynamic."
if you are referring to the "insert into .. select union ....", these are only for testing of the query

ayamas
Aged Yak Warrior

552 Posts

Posted - 2006-06-07 : 02:23:07

Yes khtan the output is perfect.But how can it be done dynamically?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-07 : 02:23:47

what do you mean dynamically ? can you explain more ?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-07 : 02:26:50

just use the query and replace your table name and column name with the actual name.

select 	*,
	[Previous Balance] = (select top 1 [Current Balance] from @table x where x.[Date] < t.[Date] order by x.[Date] desc)
from	yourtablenamehere t
order by [Date]

ayamas
Aged Yak Warrior

552 Posts

Posted - 2006-06-07 : 02:27:14

What I meant was i have the records in a table.there are about 2000 transactions like this in it.But in that table the Balance of the last transaction date is not stored.So I want to display what was the balance on the last transaction date for every new transaction.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-07 : 02:28:40

can you post your table structure, some sample data and the result that you want ?

ayamas
Aged Yak Warrior

552 Posts

Posted - 2006-06-07 : 02:38:39

I am posting just 4 columns from the table.The data is stored this way

Date Credit Debit Curr Balance
02/05/06 1200 0 1200
05/05/06 500 0 1700
10/05/06 0 200 1500
15/05/06 0 200 1300
I mean the date when the transaction was done & whether that transaction was of credit or debit.But I want the output like this.
I mean for each new transaction the cur balance of previous transaction should be displayed in bal on dt column.
Date Bal on Dt Credit Debit Cur Bal
02/05/06 0 1200 0 1200
05/05/06 1200 500 0 1700
10/05/06 1700 0 200 1500
15/05/06 1500 200 0 1700

While the current balance is calculated depending upon whether the transaction was of debit or credit.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-07 : 02:50:11

[code]
declare @table table
(
[Date] datetime,
[Credit] int,
[Debit] int
)
set dateformat dmy
insert into @table
select '02/05/06', 1200, 0 union all
select '05/05/06', 500, 0 union all
select '10/05/06', 0, 200 union all
select '15/05/06', 0, 200

select [Date],
[Prev Balance] = isnull((select sum(Credit - Debit) from @table x where x.[Date] < t.[Date]), 0),
[Credit], [Debit],
[Curr Balance] = isnull((select sum(Credit - Debit) from @table x where x.[Date] <= t.[Date]), 0)
from @table t
order by [Date]

/*
Date Prev Balance Credit Debit Curr Balance
----------- ------------ ----------- ----------- ------------
2006-05-02 0 1200 0 1200
2006-05-05 1200 500 0 1700
2006-05-10 1700 0 200 1500
2006-05-15 1500 0 200 1300
*/[/code]

ayamas
Aged Yak Warrior

552 Posts

Posted - 2006-06-07 : 03:03:37

U know what khtan you are a GENIUS.U know I tried this thing yesterday the whole day but I could not get the output but you solved it in jst few minutes.I tried using cursors I tried to use loops but with no success.
Thank you very very much.Thanks a lot once again.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-07 : 03:10:37

You are welcome

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-06-07 : 06:14:10

Hi all,

ayamas - Khtan's suggestion is great, but beware of an issue if you can have duplicate dates...

declare @table table
(
	[Date]			datetime,
	[Credit] 		int,
	[Debit]			int
)
set dateformat dmy
insert into @table
select '02/05/06', 1200,   0 union all
select '05/05/06',  500,   0 union all
select '05/05/06',  100,   0 union all
select '10/05/06',    0, 200 union all
select '15/05/06',    0, 200 

select	[Date],
	[Prev Balance] = isnull((select sum(Credit - Debit) from @table x where x.[Date] < t.[Date]), 0),
	[Credit], [Debit],
	[Curr Balance] = isnull((select sum(Credit - Debit) from @table x where x.[Date] <= t.[Date]), 0)
from	@table t
order by [Date]

/*
Date                                                   Prev Balance Credit      Debit       Curr Balance 
------------------------------------------------------ ------------ ----------- ----------- ------------ 
2006-05-02 00:00:00.000                                0            1200        0           1200
2006-05-05 00:00:00.000                                1200         500         0           1800
2006-05-05 00:00:00.000                                1200         100         0           1800
2006-05-10 00:00:00.000                                1800         0           200         1600
2006-05-15 00:00:00.000                                1600         0           200         1400
/*

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-07 : 06:22:58

"beware of an issue if you can have duplicate dates..."
Ryan, you are correct. Hopefully, date is the PK of the table

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-06-07 : 06:30:01

Yeah, I hope so too!

Otherwise it might be better to resort to Madhivanan's standard "do it at the front-end" response

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2006-06-07 : 06:57:55

quote:
Originally posted by RyanRandall

Yeah, I hope so too!

Otherwise it might be better to resort to Madhivanan's standard "do it at the front-end" response

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

It might be better anyway.

ayamas
Aged Yak Warrior

552 Posts

Posted - 2006-06-07 : 07:29:20

Hi guys,
thanks for all of your valuable suggestions.But unfortunately date is not the PK in the table because on one date there can be more than one transaction.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-07 : 07:46:46

"date is not the PK in the table because on one date there can be more than one transaction."
In that case, is this acceptable ?

declare @table table
(
	[Date]			datetime,
	[Credit] 		int,
	[Debit]			int
)
set dateformat dmy
insert into @table
select '02/05/06', 1200,   0 union all
select '05/05/06',  500,   0 union all
select '05/05/06',  100,   0 union all
select '10/05/06',    0, 200 union all
select '15/05/06',    0, 200 

select	[Date],
	[Prev Balance] 	= isnull((select sum(Credit - Debit) from @table x where x.[Date] < t.[Date]), 0),
	[Credit]	= sum([Credit]), 
	[Debit]		= sum([Debit]),
	[Curr Balance] = isnull((select sum(Credit - Debit) from @table x where x.[Date] <= t.[Date]), 0)
from	@table t
group by [Date]
order by [Date]

/* RESULT : 
Date                    Prev Balance Credit      Debit       Curr Balance 
----------------------- ------------ ----------- ----------- ------------ 
2006-05-02 00:00:00.000 0            1200        0           1200
2006-05-05 00:00:00.000 1200         600         0           1800
2006-05-10 00:00:00.000 1800         0           200         1600
2006-05-15 00:00:00.000 1600         0           200         1400
*/

ayamas
Aged Yak Warrior

552 Posts

Posted - 2006-06-07 : 08:00:27

No khtan
I tried with this data

select '02/05/06', 1200, 0 union all
select '05/05/06', 500, 0 union all
select '05/05/06', 100, 0 union all
select '05/05/06', 0, 550 union all
select '10/05/06', 0, 200 union all
select '12/05/06', 1000, 0 union all
select '15/05/06', 0, 200

it shows the output as follows
Date Prev Bal Credit debit Curr Balance
2006-05-02 0 1200 0 1200
2006-05-05 1200 600 550 1250
2006-05-10 1250 0 200 1050

Guys I cant do it from the front end because I want to display it as a report in Crystal Reports.Is there anyway it can be done in CR because I thought that I will make a stored proc & then execute it in the report.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-07 : 08:04:30

What is the PK of your table ? Can you post your table structure ?

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources