get Previous Value in Table

Question

1.00/5 (2 votes)

See more:

SQL-Server-2008R2

Dear All,
How to Get Previous Value in table.

Data

ITEMCODE  Date           ISSUE QTY 
1         10/10/2015       20
1         11/10/2015        0
1         13/10/2015       30
1         15/10/2015       30
1         16/10/2015        0 
1         17/10/2015        0
1         118/10/2015      20

I need to replace or change 0 issue qty into Previous Value like below.

ITEMCODE  Date           ISSUE QTY 
1         10/10/2015       20
1         11/10/2015       20
1         13/10/2015       30
1         15/10/2015       30
1         16/10/2015       30 
1         17/10/2015       20
1         118/10/2015      20

Please help me how to fix this?

Posted 13-Sep-15 2:30am

DotNetNarayanan

Updated 13-Sep-15 10:31am

Maciej Los

v2

Add a Solution

3 solutions

Solution 1

You need to update the record where the ISSUE QTY is zero, try the following SQL

C#

"UPDATE table_name SET [ISSUE QTY] = @0 WHERE [ISSUE QTY] = 0";

Then use the methods SQL Parameters (based on the language being used here, C# or VB.NET or what ever language for your platform) and change the value of @0 to a value that needs to be there; either 30 or 20 as per your data. There is no logic that I can use to give you an answer for that.

SQL Parameterization allows you to protect your application from SQL Injections, that are mostly caused by string concatenations and other similar query building procedures.

Posted 13-Sep-15 3:41am

Afzaal Ahmad Zeeshan

Updated 13-Sep-15 3:42am

v2

Solution 3

The best solution for such of situation is to use UPDATE + SELECT statement in one!

SQL

UPDATE destTable Set Field1 = sourceTable.Field2
FROM Table1 As destTable INNER JOIN Table2 AS sourceTable ON destTable.KeyField = sourceTable.ForeignKeyField
WHERE destTable.FilterField = @Whatever

Change the code to your needs!

Posted 13-Sep-15 10:45am

Maciej Los

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Accepted Answer · 2015-09-13T07:40:00

If I understand your question correctly, in an update statement you want to fetch the value from the previous row. So perhaps something like

SQL

UPDATE MyTable
SET [ISSUE QTY] = (SELECT b.[ISSUE QTY])
                   FROM MyTable b
                   WHERE b.[Date] = (SELECT MAX(c.[Date])
                                     FROM   MyTable c
                                     WHERE  c.[Date] < b.[Date]
                                     AND    c.[ISSUE QTY] <> 0))
WHERE [ISSUE QTY] = 0;