CODEWITHSUNDEEP
sqloracle-database
ram sriram
ram sriram

Reputation: 53

How to get previous and latest date and its details in SQL

I have a table with the following data:

create table tempdata(account varchar2(20)not null,bookid number(10),seqno number(20) not null,book_date date, book1 number(10),
book2 number(10),book3 number(10))

insert into tempdata values('123',101,09,add_months((sysdate),-1),100,120,130);

insert into tempdata values('123',101,10,sysdate),70,60,100)

select * from tempdata;

ACCOUNT BOOKID  SEQNO   BOOK_DATE               BOOK1   BOOK2   BOOK3

123      101     9      9/22/2015 10:05:28 AM   100      120    130

123      101     10     10/22/2015 10:01:42 AM  70       60     100

I need to output something like the following in order to create another temp table with latest book details including the previous date and latest date:

ACCOUNT BOOKID  SEQNO     Previous_DATE          Latest_date           BOOK1    BOOK2   BOOK3

123      101     10    9/22/2015 10:05:28 AM  10/22/2015 10:01:42 AM          70     60      100

Upvotes: 5

Views: 86

Answers (3)

Boneist
Boneist

Reputation: 23588

The LAG and ROW_NUMBER analytic functions would come in handy here:

select account,
       bookid,
       seqno,
       previous_date,
       latest_date,
       book1,
       book2,
       book3
from   (select account,
               bookid,
               seqno,
               lag(book_date) over (partition by account order by book_date) previous_date,
               book_date latest_date,
               book1,
               book2,
               book3,
               row_number() over (partition by account order by book_date) rn
        from   tempdata)
where  rn = 1;

ACCOUNT                  BOOKID      SEQNO PREVIOUS_DATE         LATEST_DATE                BOOK1      BOOK2      BOOK3
-------------------- ---------- ---------- --------------------- --------------------- ---------- ---------- ----------
123                         101          9                       22/09/2015 14:34:06          100        120        130

N.B. I've made the assumption that you want this information for each account. If the grouping needs to be changed (eg. maybe it's account and bookid, or just bookid) then you'll need to amend the partition by clauses appropriately.

Upvotes: 1

Utsav
Utsav

Reputation: 8143

Here I am assuming that you want data for a unique account and bookid combination.

SELECT T1.ACCOUNT, T1.BOOKID, T1.SEQNO,T1.PREVIOUS_DATE, 
T1.BOOK_DATE AS  LATEST_DATE , T1.BOOK1, T1.BOOK2, T1.BOOK3 
FROM (
    SELECT T.* ,ROW_NUMBER() OVER (PARTITION BY ACCOUNT,bookid ORDER BY BOOK_DATE desc) as rno, 
    LAG(TO_CHAR(BOOK_DATE), 1, 0) OVER (ORDER BY BOOK_DATE) AS PREVIOUS_DATE 
    FROM TEMPDATA T) T1
WHERE T1.RNO =1

Upvotes: 2

Matt
Matt

Reputation: 15061

SELECT ACCOUNT, 
BOOKID, 
MAX(SEQNO), 
(SELECT *
 FROM (SELECT BOOK_DATE, row_number() over (ORDER BY BOOK_DATE desc) AS row_num) t
 WHERE row_num = 2) AS Previous_DATE, 
MAX(BOOK_DATE) AS Latest_date, 
(SELECT a.BOOK1 
 FROM tempdataa a 
 WHERE a.BOOK_DATE = (SELECT MAX(BOOK_DATE) 
                      FROM tempdata
                      WHERE ACCOUNT = a.ACCOUNT 
                      AND BOOKID = a.BOOKID)) AS BOOK1,
(SELECT a.BOOK2 
 FROM tempdataa a 
 WHERE a.BOOK_DATE = (SELECT MAX(BOOK_DATE) 
                      FROM tempdata
                      WHERE ACCOUNT = a.ACCOUNT 
                      AND BOOKID = a.BOOKID)) AS BOOK2,
(SELECT a.BOOK3 
 FROM tempdataa a 
 WHERE a.BOOK_DATE = (SELECT MAX(BOOK_DATE) 
                      FROM tempdata
                      WHERE ACCOUNT = a.ACCOUNT 
                      AND BOOKID = a.BOOKID)) AS BOOK3
FROM tempdata
GROUP BY ACCOUNT, BOOKID

Upvotes: 0

Related Questions