value from previous value - 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 2008 Forums

Transact-SQL (2008)

value from previous value

Author

Topic

julius.delorino
Starting Member

29 Posts

Posted - 2012-05-29 : 06:42:00

good day sir,im currently stuck with this problem,i hope you guys can help me out,your help is greatly appreciated,thank you. here is my sample date.

________________________________________________________________
key-----------------date -------------- beginbal---credit-----endbal
101---------------1/1/2011-------------500.00----0.00---------500.00
101---------------1/2/2011------------(500.00)---300.00-------800.00
101---------------1/4/2011------------(800.00)---0.00---------800.00
102---------------1/5/2011-------------200.00----0.00---------200.00
102---------------1/6/2011-------------(200.00)--300.00-------500.00
102---------------1/7/2011-------------(500.00)--200.00-------700.00
102---------------1/8/2011-------------(700.00)--100.00-------800.00
___________________________________________________________________

as shown on data,i want to attain the values in (parenthesis) those
are the beginbal values from previous endbal values according to
key and date,my deepest thanks to all.

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-05-29 : 08:19:57

For a better solution please post some Sample Data.

Till then check if you are looking for something like this:


--Creating table

Create Table Ex
kkey int,
 date Date,
 beginbal float,
 credit float,
 endbal float 


--Inserting Sample Data with Begin Balance = 0

Insert Into Ex
Select 101, '1/1/2011', 0, 0.00, 500.00
Union ALL
Select 101, '1/2/2011', 0, 300.00, 800.00
Union ALL
Select 101, '1/4/2011', 0, 0.00, 800.00
Union ALL
Select 102, '1/5/2011', 0, 0.00, 200.00
Union ALL
Select 102, '1/6/2011', 0, 300.00, 500.00
Union ALL
Select 102, '1/7/2011', 0, 200.00, 700.00
Union ALL
Select 102, '1/8/2011', 0, 100.00, 0


--Query For Your Requirement

;With CTE
As
(Select *, ROW_NUMBER() Over (Partition By KKey Order By Date) As rn From Ex)
Select a.kkey, a.date,
(Case When a.rn = 1 Then a.EndBal Else b.EndBal End) As BegiBal,
a.credit, a.endbal From CTE as a
Left JOIN CTE as b ON a.kkey = b.kkey
AND b.rn = (a.rn - 1)

N 28° 33' 11.93148"
E 77° 14' 33.66384"

Gigabyte
Starting Member

30 Posts

Posted - 2012-05-29 : 08:43:15

Hi,

could you explain the logic you are using?

quote:

Originally posted by vinu.vijayan

For a better solution please post some Sample Data.

Till then check if you are looking for something like this:


--Creating table

Create Table Ex
kkey int,
 date Date,
 beginbal float,
 credit float,
 endbal float 


--Inserting Sample Data with Begin Balance = 0

Insert Into Ex
Select 101, '1/1/2011', 0, 0.00, 500.00
Union ALL
Select 101, '1/2/2011', 0, 300.00, 800.00
Union ALL
Select 101, '1/4/2011', 0, 0.00, 800.00
Union ALL
Select 102, '1/5/2011', 0, 0.00, 200.00
Union ALL
Select 102, '1/6/2011', 0, 300.00, 500.00
Union ALL
Select 102, '1/7/2011', 0, 200.00, 700.00
Union ALL
Select 102, '1/8/2011', 0, 100.00, 0


--Query For Your Requirement

;With CTE
As
(Select *, ROW_NUMBER() Over (Partition By KKey Order By Date) As rn From Ex)
Select a.kkey, a.date,
(Case When a.rn = 1 Then a.EndBal Else b.EndBal End) As BegiBal,
a.credit, a.endbal From CTE as a
Left JOIN CTE as b ON a.kkey = b.kkey
AND b.rn = (a.rn - 1)

N 28° 33' 11.93148"
E 77° 14' 33.66384"

? NULL ?

Gigabyte
Starting Member

30 Posts

Posted - 2012-05-29 : 08:46:23

Hi Julius,

If you could provide a little sample data as said by Vinu, it will help us to extract data the way you want.

Also could you explain the issue more detail.

? NULL ?

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-05-29 : 09:02:56

quote:

Originally posted by Gigabyte

Hi,

could you explain the logic you are using?

quote:

Originally posted by vinu.vijayan

For a better solution please post some Sample Data.

Till then check if you are looking for something like this:



 
--Creating table

Create Table Ex
kkey int,
 date Date,
 beginbal float,
 credit float,
 endbal float 


--Inserting Sample Data with Begin Balance = 0

Insert Into Ex
Select 101, '1/1/2011', 0, 0.00, 500.00
Union ALL
Select 101, '1/2/2011', 0, 300.00, 800.00
Union ALL
Select 101, '1/4/2011', 0, 0.00, 800.00
Union ALL
Select 102, '1/5/2011', 0, 0.00, 200.00
Union ALL
Select 102, '1/6/2011', 0, 300.00, 500.00
Union ALL
Select 102, '1/7/2011', 0, 200.00, 700.00
Union ALL
Select 102, '1/8/2011', 0, 100.00, 0


--Query For Your Requirement

;With CTE
As
(Select *, ROW_NUMBER() Over (Partition By KKey Order By Date) As rn From Ex)
Select a.kkey, a.date,
(Case When a.rn = 1 Then a.EndBal Else b.EndBal End) As BegiBal,
a.credit, a.endbal From CTE as a
Left JOIN CTE as b ON a.kkey = b.kkey
AND b.rn = (a.rn - 1)

N 28° 33' 11.93148"
E 77° 14' 33.66384"

? NULL ?

I am partitioning by Kkey an adding RowNumber to it.
Then I am using a Case Statement to Select Begin Balance.
If rownumber is 1 then I select endbalance of current date and if rownumber is not 1 then I select endbalance of previous Date.

It could be clearer if we work on some sample data that you provide according to your requirement.

N 28° 33' 11.93148"
E 77° 14' 33.66384"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-29 : 12:31:17

same as

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=175179

please dont crosspost

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

julius.delorino
Starting Member

29 Posts

Posted - 2012-05-30 : 00:19:01

quote:
Originally posted by visakh16

same as

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=175179

please dont crosspost

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sir thank you all for your help, i overcome the problem with sir
visakhm script cross apply:

---***
SET @BEGINBAL = (SELECT TOP 1 T1.ENDBAL FROM #TEMPSAVINGS T CROSS APPLY
(SELECT TOP 1 ENDBAL FROM #TEMPSAVINGS WHERE CSHDRKEY = @CSHDRKEY
AND TRANSDATE < @TRANSDATE ORDER BY TRANSDATE DESC)T1)

UPDATE #TEMPSAVINGS SET BEGINBAL = @BEGINBAL WHERE CSHDRKEY = @CSHDRKEY AND TRANSDATE = @TRANSDATE
UPDATE #TEMPSAVINGS SET ENDBAL = BEGINBAL + CREDIT -DEBIT WHERE CSHDRKEY = @CSHDRKEY AND TRANSDATE = @TRANSDATE
---***

thank you all and more power

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-05-30 : 00:38:21

Good for you bro.

N 28° 33' 11.93148"
E 77° 14' 33.66384"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-30 : 16:16:29

quote:
Originally posted by julius.delorino

quote:
Originally posted by visakh16

same as

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=175179

please dont crosspost

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sir thank you all for your help, i overcome the problem with sir
visakhm script cross apply:

---***
SET @BEGINBAL = (SELECT TOP 1 T1.ENDBAL FROM #TEMPSAVINGS T CROSS APPLY
(SELECT TOP 1 ENDBAL FROM #TEMPSAVINGS WHERE CSHDRKEY = @CSHDRKEY
AND TRANSDATE < @TRANSDATE ORDER BY TRANSDATE DESC)T1)

UPDATE #TEMPSAVINGS SET BEGINBAL = @BEGINBAL WHERE CSHDRKEY = @CSHDRKEY AND TRANSDATE = @TRANSDATE
UPDATE #TEMPSAVINGS SET ENDBAL = BEGINBAL + CREDIT -DEBIT WHERE CSHDRKEY = @CSHDRKEY AND TRANSDATE = @TRANSDATE
---***

thank you all and more power

welcome

and please dont open multiple threads in future for same issue

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources