Home » SQL & PL/SQL » SQL & PL/SQL » How to get a current + last year data in a single record?
How to get a current + last year data in a single record? [message #279957] Mon, 12 November 2007 01:34 Go to next message
gksenthilkumar
Messages: 23
Registered: November 2007
Location: india
Junior Member
Do we have any option/method to get a current + last year data in a single record..?
for example
I have table name called: log_data
fields are:
level varchar2
sub_level varchar2
start_time date
end_date date

I want to compare for current +last year data and also want to listout record/data like..
level, sub_level, (start_time, end_time, duration between start_tme & end_time for year-2006), (start_time, end_time, duration between start_tme & end_time for year-2007).

I am not able to predict how to do this one. Please help me

Small change in the tablestructure.. I forget to include in the table structure:
-- Create table
create table LOG_DATA
(
LOG_DATE DATE,
LEVEL VARCHAR2(40),
SUB_LEVEL VARCHAR2(40),
START_TIME DATE,
END_TIME DATE
);

SAMPLE DATA:
21/1/2006 L1 SL1 21/1/2006 12:43:03 22/1/2006 14:43:03
21/2/2006 L1 SL1 21/2/2006 12:43:03 22/2/2006 14:43:03

21/1/2006 L1 SL2 21/1/2006 12:43:03 22/1/2006 14:43:03
21/2/2006 L1 SL2 21/2/2006 12:43:03 22/2/2006 14:43:03

21/1/2007 L1 SL1 21/1/2007 12:43:03 22/1/2007 14:43:03
21/2/2007 L1 SL1 21/2/2007 12:43:03 22/2/2007 14:43:03

21/1/2007 L1 SL2 21/1/2007 12:43:03 22/1/2007 14:43:03
21/2/2007 L1 SL2 21/2/2007 12:43:03 22/2/2007 14:43:03


OUTPUT like..
LOG_DATE, LEVEL, SUB_LEVEL, [START_TIME END_TIME (Duration between Start_Time & End_Time) for YEAR_2006], [START_TIME END_TIME (Duration between Start_Time & End_Time) for YEAR_2007]
21/1/2006 L1 SL1 [21/1/2006 12:43:03 22/1/2006 14:43:03 (26) 21/1/2007 12:43:03 22/1/2006 14:43:03 (26)]
21/1/2006 L1 SL2 [21/1/2006 12:43:03 22/1/2006 14:43:03 (26) 21/1/2007 12:43:03 22/1/2006 14:43:03 (26)]

21/2/2006 L1 SL1 [21/2/2006 12:43:03 22/2/2006 14:43:03 (26) 21/2/2007 12:43:03 22/2/2006 14:43:03 (26)]
21/2/2006 L1 SL2 [21/2/2006 12:43:03 22/2/2006 14:43:03 (26) 21/2/2007 12:43:03 22/2/2006 14:43:03 (26)]

[Updated on: Mon, 12 November 2007 02:01]

Report message to a moderator

Re: How to get a current + last year data in a single record? [message #279965 is a reply to message #279957] Mon, 12 November 2007 02:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Always post your Oracle version (4 decimals).

Sample data must also be post as insert statements.
You can do it with min/max functions as longs as decode to pivot the result.

Regards
Michel
Re: How to get a current + last year data in a single record? [message #280596 is a reply to message #279957] Wed, 14 November 2007 03:26 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

One more option .. Using Self join

select lgdt1.LOG_DATE L1_DATE, lgdt1.LEVEL_V L1_LEVEL, 
		lgdt1.SUB_LEVEL L1_SUB_LEVEL, lgdt1.START_TIME L1_START_TIME, 
		lgdt1.END_TIME L1_END_TIME , (lgdt1.END_TIME  - lgdt1.START_TIME) *24 DIFF1   ,
		lgdt2.LOG_DATE L2_DATE, lgdt2.START_TIME L2_START_TIME, 
		lgdt2.END_TIME L2_END_TIME ,(lgdt1.END_TIME  - lgdt1.START_TIME) *24 DIFF2  
from     LOG_DATA lgdt1,
        LOG_DATA lgdt2
where   lgdt1.LOG_DATE = ADD_MONTHS(lgdt2.LOG_DATE ,-12)
and	lgdt1.LEVEL_V = lgdt2.LEVEL_V
and 	lgdt1.SUB_LEVEL = lgdt2.SUB_LEVEL
order    by lgdt1.LOG_DATE   ,lgdt1.LEVEL_V  , lgdt1.SUB_LEVEL 


I think this is what OP is looking for Smile

Thumbs Up
Rajuvan

Re: How to get a current + last year data in a single record? [message #280608 is a reply to message #279957] Wed, 14 November 2007 04:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select deptno,
  2         min(decode(extract (year from hiredate),1980,hiredate)) min_1980,
  3         max(decode(extract (year from hiredate),1980,hiredate)) max_1980,
  4         min(decode(extract (year from hiredate),1981,hiredate)) min_1981,
  5         max(decode(extract (year from hiredate),1981,hiredate)) max_1981,
  6         min(decode(extract (year from hiredate),1982,hiredate)) min_1982,
  7         max(decode(extract (year from hiredate),1982,hiredate)) max_1982
  8  from emp
  9  group by deptno
 10  order by deptno
 11  /
    DEPTNO MIN_1980   MAX_1980   MIN_1981   MAX_1981   MIN_1982   MAX_1982
---------- ---------- ---------- ---------- ---------- ---------- ----------
        10                       09/06/1981 17/11/1981 23/01/1982 23/01/1982
        20 17/12/1980 17/12/1980 02/04/1981 03/12/1981
        30                       20/02/1981 03/12/1981

3 rows selected.

Regards
Michel
Re: How to get a current + last year data in a single record? [message #280623 is a reply to message #279957] Wed, 14 November 2007 04:28 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Michel's Answer May not be Sufficient for Op's requirement , thoug logic is fine . He Might have confused on joining Date as per his table structure.

Here is the Script (For reference )

CREATE TABLE LOG_DATA
(
  LOG_DATE    DATE,
  LEVEL_V     VARCHAR2(40 BYTE),
  SUB_LEVEL   VARCHAR2(40 BYTE),
  START_TIME  DATE,
  END_TIME    DATE
);

  Insert into LOG_DATA
   (log_date, level_v, sub_level, start_time, end_time)
 Values
   (TO_DATE('01/21/2007 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
    'L1', 'SL1', TO_DATE('02/21/2007 12:43:03', 'MM/DD/YYYY HH24:MI:SS'), 
    TO_DATE('02/22/2007 14:43:03', 'MM/DD/YYYY HH24:MI:SS'));
Insert into LOG_DATA
   (log_date, level_v, sub_level, start_time, end_time)
 Values
   (TO_DATE('02/21/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
    'L1', 'SL1', TO_DATE('01/21/2006 12:43:03', 'MM/DD/YYYY HH24:MI:SS'), 
    TO_DATE('01/22/2006 14:43:03', 'MM/DD/YYYY HH24:MI:SS'));
Insert into LOG_DATA
   (log_date, level_v, sub_level, start_time, end_time)
 Values
   (TO_DATE('01/21/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
    'L1', 'SL2', TO_DATE('02/21/2006 12:43:03', 'MM/DD/YYYY HH24:MI:SS'), 
    TO_DATE('02/22/2006 14:43:03', 'MM/DD/YYYY HH24:MI:SS'));
Insert into LOG_DATA
   (log_date, level_v, sub_level, start_time, end_time)
 Values
   (TO_DATE('02/21/2007 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
    'L1', 'SL2', TO_DATE('01/21/2007 12:43:03', 'MM/DD/YYYY HH24:MI:SS'), 
    TO_DATE('01/22/2007 14:43:03', 'MM/DD/YYYY HH24:MI:SS'));
Insert into LOG_DATA
   (log_date, level_v, sub_level, start_time, end_time)
 Values
   (TO_DATE('01/21/2007 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
    'L1', 'SL2', TO_DATE('01/21/2007 12:43:03', 'MM/DD/YYYY HH24:MI:SS'), 
    TO_DATE('01/22/2007 14:43:03', 'MM/DD/YYYY HH24:MI:SS'));
Insert into LOG_DATA
   (log_date, level_v, sub_level, start_time, end_time)
 Values
   (TO_DATE('02/21/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
    'L1', 'SL2', TO_DATE('01/21/2006 12:43:03', 'MM/DD/YYYY HH24:MI:SS'), 
    TO_DATE('01/22/2006 14:43:03', 'MM/DD/YYYY HH24:MI:SS'));
Insert into LOG_DATA
   (log_date, level_v, sub_level, start_time, end_time)
 Values
   (TO_DATE('01/21/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
    'L1', 'SL1', TO_DATE('02/21/2006 12:43:03', 'MM/DD/YYYY HH24:MI:SS'), 
    TO_DATE('02/22/2006 14:43:03', 'MM/DD/YYYY HH24:MI:SS'));
Insert into LOG_DATA
   (log_date, level_v, sub_level, start_time, end_time)
 Values
   (TO_DATE('02/21/2007 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
    'L1', 'SL1', TO_DATE('02/21/2007 12:43:03', 'MM/DD/YYYY HH24:MI:SS'), 
    TO_DATE('02/22/2007 14:43:03', 'MM/DD/YYYY HH24:MI:SS'));
COMMIT;



Thumbs Up
Rajuvan.

[Updated on: Wed, 14 November 2007 06:20] by Moderator

Report message to a moderator

Re: How to get a current + last year data in a single record? [message #280656 is a reply to message #280623] Wed, 14 November 2007 06:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I just gave a clue as I don't really understand the question.

Regards
Michel
Re: How to get a current + last year data in a single record? [message #280657 is a reply to message #279957] Wed, 14 November 2007 06:39 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

One of Michel's Previous Post. Smile

Quote:

1/ how could you post a solution without knowing what OP wants, for instance in the cases I posted
2/ your "solution" will not work, I don't even see the logic behind this, who say that the range are/may be/have to be contiguous?

Regards
Michel


No Offence is meant !!!!

Thumbs Up
Rajuvan.
Re: How to get a current + last year data in a single record? [message #280660 is a reply to message #280657] Wed, 14 November 2007 06:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You can see I did not use OP's table or data just to clearly stated that this is not a solution.
Actually it is just an explaination on an example of what I previously said: "You can do it with min/max functions as long as decode to pivot the result."
But maybe this is not so clear.

Regards
Michel
Re: How to get a current + last year data in a single record? [message #280668 is a reply to message #279957] Wed, 14 November 2007 07:57 Go to previous messageGo to next message
srinivasreddy777
Messages: 11
Registered: October 2007
Location: Hyderabad
Junior Member

Hi.. Guys

I hope this might give the solution for u....

If i am wrong pl correct me...

select level_v,
sub_level,
max(decode(to_char(start_time,'YYYY'),2006,(start_time || ' ' || end_time || ' ' || ceil(((end_time-start_time)*24*60*60)/3600)),null)) Yr_2006,
max(decode(to_char(start_time,'YYYY'),2007,(start_time || ' ' || end_time || ' ' || ceil(((end_time-start_time)*24*60*60)/3600)),null)) Yr_2007
from log_data
group by level_v,sub_level



Regards,

Srinivas
Re: How to get a current + last year data in a single record? [message #280669 is a reply to message #279957] Wed, 14 November 2007 08:08 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

This will not give the Exact output for the OP.

Thumbs Up
Rajuvan
Re: How to get a current + last year data in a single record? [message #280670 is a reply to message #280668] Wed, 14 November 2007 08:14 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
to_char(start_time,'YYYY') is a string
2006 is a number

Never compare objects of different types.

Regards
Michel
Previous Topic: Inserting into clob, data more than 4000 chars via pl/sql
Next Topic: Silly question about PL/SQL queries
Goto Forum:
  


Current Time: Sat Dec 03 00:56:02 CST 2016

Total time taken to generate the page: 0.10645 seconds