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 Go to next message
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 #563850 is a reply to message #563849] Fri, 17 August 2012 09:02 Go to previous messageGo to next message
BlackSwan
Messages: 22688
Registered: January 2009
Senior Member
>Appreciate help if anybody let me know how to acheive "Prev_Value"
what is PREV_VALUE & how is it computed?
Re: Previous record for multiple combination [message #563851 is a reply to message #563850] Fri, 17 August 2012 09:13 Go to previous messageGo to next message
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 #563966 is a reply to message #563851] Sun, 19 August 2012 02:35 Go to previous messageGo to next message
patneel
Messages: 39
Registered: June 2007
Location: India
Member
Hi All

Any thoughts pls..I have tried it out but first previous value is getting repeated across all records

Appreciate your inputs
Re: Previous record for multiple combination [message #563968 is a reply to message #563966] Sun, 19 August 2012 05:33 Go to previous messageGo to next message
Solomon Yakobson
Messages: 1994
Registered: January 2010
Senior Member
patneel wrote on Sun, 19 August 2012 03:35
I have tried


Show us what you tried.

SY.
Re: Previous record for multiple combination [message #563973 is a reply to message #563968] Sun, 19 August 2012 09:08 Go to previous messageGo to next message
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 Go to previous messageGo to next message
John Watson
Messages: 4479
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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 1994
Registered: January 2010
Senior Member
patneel wrote on Sun, 19 August 2012 10:08
This 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.
Re: Previous record for multiple combination [message #564347 is a reply to message #563979] Wed, 22 August 2012 23:23 Go to previous messageGo to next message
patneel
Messages: 39
Registered: June 2007
Location: India
Member
Thanks SY.

But i am looking for something withput analytic function due to requirement..
Re: Previous record for multiple combination [message #564348 is a reply to message #564347] Wed, 22 August 2012 23:38 Go to previous messageGo to next message
Michel Cadot
Messages: 58851
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is wrong, NO requirement is "do not use analytic function",
I NEVER saw a client saying I want a query that returns this but do NOT use analytic functions.

Regards
Michel
Re: Previous record for multiple combination [message #564358 is a reply to message #564348] Thu, 23 August 2012 02:19 Go to previous messageGo to next message
patneel
Messages: 39
Registered: June 2007
Location: India
Member
I have to implement same using MS Access as well which dont support these functions..
Re: Previous record for multiple combination [message #564363 is a reply to message #564358] Thu, 23 August 2012 02:40 Go to previous message
Michel Cadot
Messages: 58851
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So post your question in a MS/Access forum and NOT in an Oracle one.

Regards
Michel
Previous Topic: Need a number of months the contract was open (6 Merged)
Next Topic: issue in union condition
Goto Forum:
  


Current Time: Thu Aug 21 03:41:27 CDT 2014

Total time taken to generate the page: 0.06284 seconds