How to Get current column value, Previous Column Value

Question

How to get Previous Column Value?

IIf id1 = id2 then display previous column id1 value


id1  id2

1001 1001
1002 1002 
1003 1003

so on...

select id1, id2, Iff id2 = id1 then disply previous id1 value as  idadjusted

Output

id1 id2 id3(Expected)

1001    1001    **1000**
1002    1002    **1001**
1003    1003    **1002**

so on...

I want to disply previous column value of id1

My query

SELECT CARDNO, NAME, TITLENAME, CARDEVENTDATE, MIN(CARDEVENTTIME) AS INTIME, MAX(CARDEVENTTIME) AS OUTTIME, 

CARDEVENTDATE AS LASTDATE, MAX(CARDEVENTTIME) AS LASTTIME

 FROM (SELECT T_PERSON.CARDNO, T_PERSON.NAME, T_TITLE.TITLENAME, T_CARDEVENT.CARDEVENTDATE, T_CARDEVENT.CARDEVENTTIME FROM (T_TITLE INNER JOIN T_PERSON ON T_TITLE.TITLECODE = T_PERSON.TITLECODE) INNER JOIN T_CARDEVENT ON T_PERSON.PERSONID = T_CARDEVENT.PERSONID ORDER BY T_PERSON.TITLECODE) GROUP BY CARDNO, NAME, TITLENAME, CARDEVENTDATE

For the LastDate - I want to Display Previous column cardeventdate value For the Lasttime - I want to display previous column outtime value

Need Query Help?

Answer 1

When you are designing your database you should consider the fact that you cannot rely on all the rows being in the right order. Instead you should create an identity value, that increment by one for every new row. And if you do this your solution becomes easy (or easier at least)

Assuming a new column called ID

SELECT colum1 FROM myTable WHERE ID = (SELECT ID FROM myTAble WHERE Column1 = Column2) - 1

If you get no match you will end up with ID -1 and this does not exist so you're ok.

If it is possible to get more than one match you will have to consider that too

Answer 2

The on clause is used to retrieve the previous id, I have tested it and works fine. This solution will work even if intermediate ids are missiing i.e. ids are not consecutive

select t1.id, t1.column1, t1.column2,
case 
    when (t1.column1 = t1.column2) then t2.column1
    else null
end as column3
from mytable t1
left outer join mytable t2 
    on t1.id = (select max(id) from mytable where id < t1.id)

For your complex query, you can create a view and then use the above sql format for your view:

Create a view MyView for:

SELECT CARDNO, NAME, TITLENAME, CARDEVENTDATE, MIN(CARDEVENTTIME) AS INTIME, MAX(CARDEVENTTIME) AS OUTTIME 
    FROM (SELECT T_PERSON.CARDNO, T_PERSON.NAME, T_TITLE.TITLENAME, T_CARDEVENT.CARDEVENTDATE, T_CARDEVENT.CARDEVENTTIME 
        FROM T_TITLE
            INNER JOIN T_PERSON ON T_TITLE.TITLECODE = T_PERSON.TITLECODE 
            INNER JOIN T_CARDEVENT ON T_PERSON.PERSONID = T_CARDEVENT.PERSONID 
            ORDER BY T_PERSON.TITLECODE) GROUP BY CARDNO, NAME, TITLENAME, CARDEVENTDATE

And then the query would be:

select  v1.CARDNO, v1.NAME, v1.TITLENAME, v1.CARDEVENTDATE, v1.INTIME, v1.OUTTIME,
    case
        when (v1.NAME = v1.TITLENAME) then  v2.CARDEVENTDATE -- Replace v1.NAME = v1.TITLENAME with your reqd condn
        else null end as LASTDATE,
    case
        when (v1.NAME = v1.TITLENAME) then v2.OUTTIME -- Replace v1.NAME = v1.TITLENAME with your reqd condn
        else null end as LASTTIME
from myview v1
    left outer join myview v2 
        on v2.CARDNO = (select max(CARDNO) from table1 where CARDNO < v1.CARDNO)

The v1.NAME = v1.TITLENAME in case stmt needs to be replaced with appropriate condn. I was not sure of the condn as its not mentioned in the question.

Answer 3

Your table isn't in first normal form (1NF):

According to Date's definition of 1NF, a table is in 1NF if and only if it is 'isomorphic to some relation', which means, specifically, that it satisfies the following five conditions:

1) There's no top-to-bottom ordering to the rows.

2) There's no left-to-right ordering to the columns.

3) ...