how to Pick value from previous date

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)

how to Pick value from previous date

Author

Topic

harvinder
Starting Member

8 Posts

Posted - 2006-05-22 : 03:32:30

Hi
Two fields are there 'Previous_Amt' and 'Current_Amt'. The 'Current_Amt' becomes 'Previous_amt' for the next Date. Ans so on.
I urgently need the solution for this.
Thanks

harvinder

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-22 : 04:03:19

Without further detail information . . . this is what i can suggest.

You can execute the update once daily

update yourtable
set    Previous_Amt = Current_Amt

Please provide more detail information for us to help you better.

harvinder
Starting Member

8 Posts

Posted - 2006-05-22 : 04:13:07

Basically i am working on a System related to Accounts. So i need to carry forward the 'Current_amt' of the Previous day(working day, excluding sundays or say a particular day on which there is no entry) to the Current day. I have written a procedure for this (using update) but not very effecient, Do you have any idea about it.
Any suggestions are welcome.

Thanks

harvinder

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-05-22 : 04:17:54

Hi Harvinder!!

Welcome to the SQL Team.

for the geting the better output from the SQL Team you need to give more information. somthing like Current Data in your table and what is the expected result you want after updating.

Also you can post what have you tried so far. so that we can work on that and let you know the best effective way.

hope this makes you clear and you can post accordingly.

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-22 : 04:18:00

Please post your table structure (simplified structure please), some sample data and the result that you want

harvinder
Starting Member

8 Posts

Posted - 2006-05-22 : 04:32:02

hi
this is the script of that table

CREATE TABLE [dbo].[Current_Amount](
[LOC_CODE] [int] NULL,
SQL_Latin1_General_CP1_CI_AS NULL,
[STATUS] [nvarchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[STATUS_DATE] [datetime] NULL,
[VOUCHER_NO] [int] NULL,
[VOUCHER_DATE] [datetime] NULL,
[RECEIPT_NO] [int] NULL,
[RECEIPT_DATE] [datetime] NULL,
[Previous_amt] [numeric](14, 2) NULL,
[Current_amt] [numeric](14, 2) NULL,
[TOTAL_AMOUNT] [numeric](14, 2) NULL,
[
) ON [PRIMARY]

Thanks for your efforts

harvinder

harvinder
Starting Member

8 Posts

Posted - 2006-05-22 : 04:50:40

hi this is What i am trying to do. This is Table structure with data.

status_date=5/21/2006 12:00:00 AM
Loc_code=1
Status=P
voucher_no=1
voucher_date=5/21/2006 12:00:00 AM
Previous_amt=100.00
Current_amt=300.00

next date is

status_date=5/22/2006 12:00:00 AM
Loc_code=1
Status=P
voucher_no=2
voucher_date=5/22/2006 12:00:00 AM
Previous_amt=300.00
Current_amt=500.00

next date is

status_date=5/23/2006 12:00:00 AM
Loc_code=1
Status=P
voucher_no=2
voucher_date=5/23/2006 12:00:00 AM
Previous_amt=500.00
Current_amt=250.00

next date is
...

Basically it's the Status_date on which 'Previous_amt' has to be modified

Imp:- 'Previous_amt' is equivalent to 'current_amt' of Previous date

harvinder

harvinder
Starting Member

8 Posts

Posted - 2006-05-22 : 04:53:44

hi
i have to make all the modifications in the back end and show it as report. Also i have to make modifications in bulk.

harvinder

harvinder
Starting Member

8 Posts

Posted - 2006-05-22 : 06:10:03

i got it
I think i have to use Stored Procdure. But there is one problem Could you tell me how to fetch the previous value of cursor, inside the cursor Fetch Statement.

harvinder

harvinder
Starting Member

8 Posts

Posted - 2006-05-22 : 06:12:14

What i am trying to do is:
1. Select the 'Current_amt' from the table
2. Update it

harvinder

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-22 : 06:23:03

quote:
Originally posted by harvinder

hi
i have to make all the modifications in the back end and show it as report. Also i have to make modifications in bulk.

harvinder

Can you explain more on this ?

1. When you set prevous_amount = current_amount and what will be the new current amount be ?
2. When do you need to do this ? Once a day ?

ya3mro
Starting Member

37 Posts

Posted - 2006-05-22 : 06:57:56

we need some more details to give u a perfect answer

How I Came To Islam?
http://english.islamway.com

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-05-22 : 08:19:12

I dont know whether you require somthing like this..but just check whether this will help you???


Declare @tblTmp Table 
(
Current_Amt Int,
Previous_Amt Int, 
Status_Date DateTime
)

Insert @tblTmp 
Select 200,0,Getdate() Union All 
Select 250,0, DateAdd(dd,1,GetDate()) Union all 
Select 300,0, DateAdd(dd,2,GetDate()) 

--Before Update 
Select * From @TblTmp


Update T1 Set Previous_Amt = A.Current_Amt From @tblTmp T1 Left Outer Join 
@tblTmp A On T1.Status_Date = DateAdd(Dd,1,A.Status_Date)

--After Update
Select * From @TblTmp

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

harvinder
Starting Member

8 Posts

Posted - 2006-05-23 : 03:07:45

Thanks i think that is appropriate. Current_amt is entered by the user. It depends on user some time it is done daily. Some time monthly.

harvinder

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources