Home » SQL & PL/SQL » SQL & PL/SQL » Help on SQL QUERY. ( SQL)
Help on SQL QUERY. [message #336088] Thu, 24 July 2008 14:20 Go to next message
sashidhar_g@yahoo.com
Messages: 1
Registered: July 2008
Junior Member
Hi,

i have a simple CRP01_DBA_TAB_MODIFICATIONS table which serves as history for day to day cumulative modifications on all objects. With data of as follows.

INSERTS UPDATES DELETES RUNDATE

61108856 129126596 61131449 7/20/2008 11:56:01 PM
61294146 129519126 61315483 7/21/2008 11:56:00 PM
61482624 129915824 61493119 7/22/2008 11:56:01 PM
61692995 130347271 61675937 7/23/2008 11:56:01 PM

Now i am looking SQL to present the data as a difference of daily modifications (i.e 7/23/2008 - 7/22/2008 data will give the transactions on 07/23/2008). i.e as follows.

RUNDATE INSERTS DELETES UPDATE

7/23/2008 210371 182818 431447

7/22/2008 188478 177636 396698

7/21/2008 185290 184034 392530

For which i had a query. which works OK to pull only 1 day data

select a.INSERTS-b.INSERTS INSERTS, a.UPDATES-b.UPDATES UPDATES, a.DELETES-b.DELETES DELETES from CRP01_DBA_TAB_MODIFICATIONS a, CRP01_DBA_TAB_MODIFICATIONS b where a.RUNDATE > sysdate-1 and b.RUNDATE > sysdate-2

INSERTS UPDATE DELETES

210371 431447 182818

0 0 0

But, really bad when i need more than one day

select a.INSERTS-b.INSERTS INSERTS, a.UPDATES-b.UPDATES UPDATES, a.DELETES-b.DELETES DELETES from CRP01_DBA_TAB_MODIFICATIONS a, CRP01_DBA_TAB_MODIFICATIONS b where a.RUNDATE > sysdate-3 and b.RUNDATE > sysdate-4

So, any help on the query is appreciated.

Thanks,
Sashi.

[Updated on: Thu, 24 July 2008 14:24]

Report message to a moderator

Re: Help on SQL QUERY. [message #336089 is a reply to message #336088] Thu, 24 July 2008 14:28 Go to previous message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Have a look at LAG/LEAD functions.

Regards
Michel
Previous Topic: Column Alias
Next Topic: Update CLOB column
Goto Forum:
  


Current Time: Mon Dec 05 14:51:37 CST 2016

Total time taken to generate the page: 0.10912 seconds