Show previous data - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Show previous data

Author

Topic

tkotey
Yak Posting Veteran

75 Posts

Posted - 2008-07-04 : 11:51:52

I have a data sample below that I would like a SQL query for. For TransType, 1=Debit(Invoice) and 2=Credit(Payment)

Index	UserID	TransType	TransNum	CreateDate	Total	Balance	PreviousInvoiceBalance
1	21	1	100	6/5/2008 0:00	234	234	
2	21	2	1	6/7/2008 0:00	40	194	
3	23	1	101	6/7/2008 0:00	50	50	
4	22	1	102	6/7/2008 0:00	456	456	
5	21	1	103	6/8/2008 0:00	356	550

As you look at the sample above I would like a query that will give me the results below which is filling in data for the PreviousInvoiceBalance column

Index	UserID	TransType	TransNum	CreateDate	Total	Balance	  PreviousInvoiceBalance
1	21	1	100	6/5/2008 	234	234	0
2	21	2	1	6/7/2008 	40	194	234
3	23	1	101	6/7/2008 	50	50	0
4	22	1	102	6/7/2008 	456	456	0
5	21	1	103	6/8/2008 	356	550	234

I would be very grateful if someone could help me with this

Thanks

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2008-07-05 : 05:58:29

try this,

Declare @T_InVoice Table (Id Int, UserID Int, TransType Int, TransNum Int, CreateDate DateTime,
Total Int, Balance Int)

Insert Into @T_InVoice
Select 1, 21, 1, 100, '6/5/2008 0:00', 234, 234 Union All
Select 2, 21, 2, 1, '6/7/2008 0:00', 40, 194 Union All
Select 3, 23, 1, 101, '6/7/2008 0:00', 50, 50 Union All
Select 4, 22, 1, 102, '6/7/2008 0:00', 456, 456 Union All
Select 5, 21, 1, 103, '6/8/2008 0:00', 356, 550

Select *,
( Select Balance From @T_InVoice Where UserID = T.UserID and Id < T.Id and TransType = 1) as 'PreviousInvoiceBalance'
From @T_InVoice T

tkotey
Yak Posting Veteran

75 Posts

Posted - 2008-07-07 : 04:00:37

quote:
Originally posted by PeterNeo

try this,

Declare @T_InVoice Table (Id Int, UserID Int, TransType Int, TransNum Int, CreateDate DateTime,
Total Int, Balance Int)

Insert Into @T_InVoice
Select 1, 21, 1, 100, '6/5/2008 0:00', 234, 234 Union All
Select 2, 21, 2, 1, '6/7/2008 0:00', 40, 194 Union All
Select 3, 23, 1, 101, '6/7/2008 0:00', 50, 50 Union All
Select 4, 22, 1, 102, '6/7/2008 0:00', 456, 456 Union All
Select 5, 21, 1, 103, '6/8/2008 0:00', 356, 550

Select *,
( Select Balance From @T_InVoice Where UserID = T.UserID and Id < T.Id and TransType = 1) as 'PreviousInvoiceBalance'
From @T_InVoice T

The statement above produces the same PreviousInvoiceBalance through out, yet every invoice should have different PreviousInvoiceBalance. Please see extension of the sample data below


Index	UserID	TransType	TransNum	CreateDate	Total	Balance	PreviousInvoiceBalance
1	21	1	100	6/5/2008 0:00	234	234	0
2	21	2	1	6/7/2008 0:00	40	194	234
3	23	1	101	6/7/2008 0:00	50	50	0
4	22	1	102	6/7/2008 0:00	456	456	0
5	21	1	103	6/8/2008 0:00	356	550	234
6	21	1	104	6/9/2008 0:00	100	650	550
7	21	1	105	6/11/2008 0:00	100	750	650
8	21	1	106	6/15/2008 0:00	50	800	750
9	21	2	2	6/16/2008 0:00	100	700	800
10	21	2	3	6/17/2008 0:00	150	550	800
11	21	1	107	6/20/2008 0:00	300	850	800

When I run the statement with the sample data above, I first get an error that reads

quote:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

So I used the statement by adding TOP (1)

Declare @T_InVoice Table (Id Int, UserID Int, TransType Int, TransNum Int, CreateDate DateTime,
Total Int, Balance Int)

Insert Into @T_InVoice
Select 1, 21, 1, 100, '6/5/2008 0:00', 234, 234 Union All
Select 2, 21, 2, 1, '6/7/2008 0:00', 40, 194 Union All
Select 3, 23, 1, 101, '6/7/2008 0:00', 50, 50 Union All
Select 4, 22, 1, 102, '6/7/2008 0:00', 456, 456 Union All
Select 5, 21, 1, 103, '6/8/2008 0:00', 356, 550 Union All
Select 6, 21, 1, 104, '6/9/2008 0:00', 100, 650 Union All
Select 7, 21, 1, 105, '6/11/2008 0:00', 100, 750 Union All
Select 8, 21, 1, 106, '6/15/2008 0:00', 50, 800 Union All
Select 9, 21, 2, 2, '6/16/2008 0:00', 100, 700 Union All
Select 10, 21, 2, 3, '6/17/2008 0:00', 150, 550 Union All
Select 11, 21, 1, 107, '6/20/2008 0:00', 300, 850

Select *,
( Select TOP(1) Balance From @T_InVoice Where UserID = T.UserID and Id < T.Id and TransType = 1) as 'PreviousInvoiceBalance'
From @T_InVoice T

The result is below


1	21	1	100	2008-06-05 00:00:00.000	234	234	NULL
2	21	2	1	2008-06-07 00:00:00.000	40	194	234
3	23	1	101	2008-06-07 00:00:00.000	50	50	NULL
4	22	1	102	2008-06-07 00:00:00.000	456	456	NULL
5	21	1	103	2008-06-08 00:00:00.000	356	550	234
6	21	1	104	2008-06-09 00:00:00.000	100	650	234
7	21	1	105	2008-06-11 00:00:00.000	100	750	234
8	21	1	106	2008-06-15 00:00:00.000	50	800	234
9	21	2	2	2008-06-16 00:00:00.000	100	700	234
10	21	2	3	2008-06-17 00:00:00.000	150	550	234
11	21	1	107	2008-06-20 00:00:00.000	300	850	234

As you can see above the PreviousInvoiceBalance field as 234 through.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources