-
Query Help - Merging two rows
Hi,
PFB the sample data.
CREATE TABLE "MY_TABLE"
("NBR" VARCHAR2(20 BYTE),
"MO_YR" NUMBER,
"EFF_DT" DATE
) ;
INSERT INTO MY_TABLE (NBR, MO_YR, EFF_DT) VALUES('101',201310,Sysdate-20);
INSERT INTO MY_TABLE (NBR, MO_YR, EFF_DT) VALUES('102',201310,Sysdate-15);
INSERT INTO MY_TABLE (NBR, MO_YR, EFF_DT) VALUES('103',201310,Sysdate-10);
INSERT INTO MY_TABLE (NBR, MO_YR, EFF_DT) VALUES('104',201310,Sysdate-5);
INSERT INTO MY_TABLE (NBR, MO_YR, EFF_DT) VALUES('105',201310,Sysdate);
COMMIT;
SELECT * FROM MY_TABLE;
Here, i need to get a dataset like this.
Prev. | Old | Eff DT
105 104 Sysdate
104 103 Sysdate-5
103 102 Sysdate-10
102 101 Sysdate-15
As you can see, i need to merge the two consecutive records in the above fashion. Thanks in advance.
-
.
Try using the one of these analytic functions: LAG or LEAD.
Perhaps you could learn about analytic functions if you Read This Fine Manual:
Oracle® Database SQL Language Reference
"The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb
-
Originally Posted by LKBrwn_DBA
Thank you Good sir,
I know most of these functions and use them often. But, this requirement of mine is lil twisted and i just can not figure out how to do it. Anyways, thanks for writing in.
Hoping for some help on this.
-Parthiban
-
NBR AS CURR_NBR,
LAG(NBR, 1, 0) OVER (PARTITION BY MO_YR
ORDER BY MO_YR, EFF_DT) AS PREV_NBR.
Thanks
-
Sorry i clicked report button by mistake.
-
Originally Posted by ParthibanSekar
Sorry i clicked report button by mistake.
I'm not sure if that feature even works...
-
Yep, you got it:
Code:
SQL> WITH my_table (nbr, mo_yr, eff_dt)
2 AS (SELECT '101', 201310, TRUNC ( SYSDATE - 20) FROM DUAL UNION ALL
3 SELECT '102', 201310, TRUNC ( SYSDATE - 15) FROM DUAL UNION ALL
4 SELECT '103', 201310, TRUNC ( SYSDATE - 10) FROM DUAL UNION ALL
5 SELECT '104', 201310, TRUNC ( SYSDATE - 5) FROM DUAL UNION ALL
6 SELECT '105', 201310, TRUNC ( SYSDATE ) FROM DUAL)
7 SELECT nbr AS curr_nbr
8 , LAG ( nbr, 1, 0)
9 OVER ( PARTITION BY mo_yr ORDER BY mo_yr, eff_dt) AS prev_nbr
10 , eff_dt
11 FROM my_table
12* ORDER BY eff_dt desc
SQL> /
CURR_NBR PREV_NBR EFF_DT
--------- --------- -----------------------------
105 104 28-Oct-2013 00:00:00
104 103 23-Oct-2013 00:00:00
103 102 18-Oct-2013 00:00:00
102 101 13-Oct-2013 00:00:00
101 0 08-Oct-2013 00:00:00
SQL>
"The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|