Home » SQL & PL/SQL » SQL & PL/SQL » Previous record for multiple combination (oracle 10g)
| Previous record for multiple combination [message #563849] |
Fri, 17 August 2012 08:48  |
patneel
Messages: 39
Registered: June 2007
Location: India
|
Member |
|
|
Hi All
Appreciate help if anybody let me know how to acheive "Prev_Value" cloumn as shown below without using ORACLE analytic functions
I have records stored in table for various categories and based on ID/Name/Office/Product/Category combination I want to acheive previous value column thorugh efficient SQL query
Test Scripts as below
CREATE TABLE TEST_Prev
(
ID1 NUMBER(3),
Name1 VARCHAR2(10),
OFFICE VARCHAR2(20),
PRODUCT VARCHAR2(20),
Overall VARCHAR2(20),
DATE1 DATE,
VALUE1 NUMBER(1)
);
commit;
--INSERT SCRIPT
INSERT INTO TEST_Prev(ID1,Name1,OFFICE,PRODUCT,Overall,DATE1,VALUE1)VALUES (228, 'woodlands', 'london', 'UK','Execution', '01-JAN-2011',1);
INSERT INTO TEST_Prev(ID1,Name1,OFFICE,PRODUCT,Overall,DATE1,VALUE1)VALUES (228, 'woodlands', 'london', 'Continental', 'Sales','01-JAN-2011',6);
INSERT INTO TEST_Prev(ID1,Name1,OFFICE,PRODUCT,Overall,DATE1,VALUE1)VALUES (228, 'woodlands', 'london', 'Continental', 'Research', '01-JAN-2011',6);
INSERT INTO TEST_Prev(ID1,Name1,OFFICE,PRODUCT,Overall,DATE1,VALUE1)VALUES (228, 'woodlands', 'london', 'Continental', 'Content', '01-JAN-2011',6);
INSERT INTO TEST_Prev(ID1,Name1,OFFICE,PRODUCT,Overall,DATE1,VALUE1)VALUES (228, 'woodlands', 'london', 'UK', 'Content', '01-JAN-2011',1);
INSERT INTO TEST_Prev(ID1,Name1,OFFICE,PRODUCT,Overall,DATE1,VALUE1)VALUES (228, 'woodlands', 'london', 'UK', 'Content', '23-FEB-2012',1);
INSERT INTO TEST_Prev(ID1,Name1,OFFICE,PRODUCT,Overall,DATE1,VALUE1)VALUES (228, 'woodlands', 'london', 'Continental', 'Sales', '23-FEB-2012',3);
INSERT INTO TEST_Prev(ID1,Name1,OFFICE,PRODUCT,Overall,DATE1,VALUE1)VALUES (228, 'woodlands', 'london', 'Continental', 'Research', '23-FEB-2012',3);
INSERT INTO TEST_Prev(ID1,Name1,OFFICE,PRODUCT,Overall,DATE1,VALUE1)VALUES (228, 'woodlands', 'london', 'UK', 'Execution', '23-FEB-2012',6);
INSERT INTO TEST_Prev(ID1,Name1,OFFICE,PRODUCT,Overall,DATE1,VALUE1)VALUES (228, 'woodlands', 'london', 'Continental', 'Execution', '23-FEB-2012',6);
INSERT INTO TEST_Prev(ID1,Name1,OFFICE,PRODUCT,Overall,DATE1,VALUE1)VALUES (228, 'woodlands', 'london', 'Continental', 'Content', '23-FEB-2012',3);
INSERT INTO TEST_Prev(ID1,Name1,OFFICE,PRODUCT,Overall,DATE1,VALUE1)VALUES (228, 'woodlands', 'london', 'Continental', 'Sales', '01-JUL-2012',4);
INSERT INTO TEST_Prev(ID1,Name1,OFFICE,PRODUCT,Overall,DATE1,VALUE1)VALUES (228, 'woodlands', 'london', 'Continental', 'Execution', '01-JUL-2012',6);
INSERT INTO TEST_Prev(ID1,Name1,OFFICE,PRODUCT,Overall,DATE1,VALUE1)VALUES (228, 'woodlands', 'london', 'Continental', 'Content', '01-JUL-2012',4);
INSERT INTO TEST_Prev(ID1,Name1,OFFICE,PRODUCT,Overall,DATE1,VALUE1)VALUES (228, 'woodlands', 'london', 'Continental', 'Research', '01-JUL-2012',4);
INSERT INTO TEST_Prev(ID1,Name1,OFFICE,PRODUCT,Overall,DATE1,VALUE1)VALUES (228, 'woodlands', 'london', 'UK', 'Content','01-JUL-2012',1)
Expected output as in attached sheet..
Thanks in Advance
[Updated on: Fri, 17 August 2012 08:51] Report message to a moderator |
|
|
|
|
|
| Re: Previous record for multiple combination [message #563851 is a reply to message #563850] |
Fri, 17 August 2012 09:13  |
patneel
Messages: 39
Registered: June 2007
Location: India
|
Member |
|
|
Hi
If you put filter in data as
Overall=Content
Product= Continental and here office is same across all records
So Prev val is nothing but previous value based on all these combinations
for 23/02/2012 previous value is nothing but value against 01/01/2011 record
for 1/07/2012 previous value is nothing but value against 23/02/2012 record
ID1 NAME1 OFFICE PRODUCT OVERALL DATE1 VALUE1 Month Prev-Val
228 woodlands london Continental Content 01/01/2011 6 201101 0
228 woodlands london Continental Content 23/02/2012 3 201202 6
228 woodlands london Continental Content 01/07/2012 4 201207 3
|
|
|
|
|
|
|
|
| Re: Previous record for multiple combination [message #563973 is a reply to message #563968] |
Sun, 19 August 2012 09:08 |
patneel
Messages: 39
Registered: June 2007
Location: India
|
Member |
|
|
Hi
This is what i was trying...it's wrong but checking the same..
SELECT tz.ID1,
tz.Name1,
tz.OFFICE,
tz.PRODUCT,
tz.Overall,
tz.DATE1,
TO_CHAR(tz.DATE1,'YYYYMM')As expr,
tz.VALUE1,
NVL((SELECT * FROM(SELECT VALUE1
FROM TEST_Prev t
WHERE
t.ID1 = tz.ID1
AND t.Name1 = tz.Name1
AND t.OFFICE = tz.OFFICE
AND t.PRODUCT = tz.PRODUCT
and t.Overall=tz.Overall
AND t.DATE1 < tz.DATE1
ORDER BY t.DATE1 DESC)WHERE ROWNUM<2),0) AS Prev
FROM TEST_Prev tz
ORDER BY tz.DATE1;[Updated on: Sun, 19 August 2012 09:09] Report message to a moderator |
|
|
|
| Re: Previous record for multiple combination [message #563977 is a reply to message #563973] |
Sun, 19 August 2012 09:50 |
John Watson
Messages: 3102
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Would something like this be any use:orcl> WITH d
2 AS (SELECT ROWNUM rn,
3 dname,
4 deptno
5 FROM (SELECT dname,
6 deptno
7 FROM dept
8 ORDER BY dname)),
9 e
10 AS (SELECT ROWNUM + 1 rn,
11 dname,
12 deptno
13 FROM (SELECT dname,
14 deptno
15 FROM dept
16 ORDER BY dname))
17 SELECT rn,
18 d.dname,
19 d.deptno this_row,
20 e.deptno previous_row
21 FROM d
22 left outer join e USING (rn)
23 ORDER BY dname;
RN DNAME THIS_ROW PREVIOUS_ROW
---------- -------------- ---------- ------------
1 ACCOUNTING 10
2 OPERATIONS 40 10
3 RESEARCH 20 40
4 SALES 30 20
orcl> |
|
|
|
| Re: Previous record for multiple combination [message #563979 is a reply to message #563973] |
Sun, 19 August 2012 10:33 |
Solomon Yakobson
Messages: 1397
Registered: January 2010
|
Senior Member |
|
|
patneel wrote on Sun, 19 August 2012 10:08This is what i was trying
You need to use analytic function LAG, something like:
SQL> select t.*,
2 to_char(date1,'yyyymm') month,
3 lag(value1,1,0) over(partition by name1,office,product,overall order by date1) prev_val
4 from test_prev t
5 order by name1,
6 office,
7 overall,
8 date1,
9 product
10 /
ID1 NAME1 OFFICE PRODUCT OVERALL DATE1 VALUE1 MONTH PREV_VAL
---- ---------- ------ ----------- --------- --------- ------ ------ ----------
228 woodlands london Continental Content 01-JAN-11 6 201101 0
228 woodlands london UK Content 01-JAN-11 1 201101 0
228 woodlands london Continental Content 23-FEB-12 3 201202 6
228 woodlands london UK Content 23-FEB-12 1 201202 1
228 woodlands london Continental Content 01-JUL-12 4 201207 3
228 woodlands london UK Execution 01-JAN-11 1 201101 0
228 woodlands london Continental Execution 23-FEB-12 6 201202 0
228 woodlands london UK Execution 23-FEB-12 6 201202 1
228 woodlands london Continental Execution 01-JUL-12 6 201207 6
228 woodlands london Continental Research 01-JAN-11 6 201101 0
228 woodlands london Continental Research 23-FEB-12 3 201202 6
ID1 NAME1 OFFICE PRODUCT OVERALL DATE1 VALUE1 MONTH PREV_VAL
---- ---------- ------ ----------- --------- --------- ------ ------ ----------
228 woodlands london Continental Research 01-JUL-12 4 201207 3
228 woodlands london Continental Sales 01-JAN-11 6 201101 0
228 woodlands london Continental Sales 23-FEB-12 3 201202 6
228 woodlands london Continental Sales 01-JUL-12 4 201207 3
15 rows selected.
SQL>
SY.
|
|
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Sat May 18 15:06:59 CDT 2013
Total time taken to generate the page: 0.80820 seconds
|
] 
Members
Search
Help
Register
Login
Home
