SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 Time lapse query		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

phelit
Starting Member

2 Posts
Posted - 09/12/2011 :  09:48:45  Show Profile  Reply with Quote
Hi,
I need to make a particular query on a DB via SQLPLUS.

I have a table structured this way:
NAME - VALUE1 - VALUE2 - DATE

This table is populated every day by a procedure that does a count on 2 other tables that should be identical (they are the master table and its snapshot on a daily Fast Refresh).
A line is written by the procedure in the first table with name of the counted table (NAME) value of first count (VALUE1) value of second count (VALUE2) and DATE.

I need to find a query that allows me to identify all those tables that have not been identical for the past 5/7 days. I can't do it by hand because everyday we have a refresh of over 20K tables.

So to be extra clear I'll do a couple of examples:

Case1:

TabA and SnapA have not been identical for the past 5 days
10 vs 9
12 vs 10
15 vs 14
23 vs 20
38 vs 29

in this case I need to see this table on the query.

Case2:
TabB and SnapB have not been identical for the past 5 days apart from day 2

10 vs 9
12 vs 12
15 vs 14
23 vs 20
38 vs 29

I don't need this data because I need to be sure that the table and the snapshot are not aligned for at least 5 consecutive days.

I hope my problem is clear. I'm not sure there is a way to solve it easily.

Any help well be GREATLY appreciated.



Phelit Leafwise
Edited by - phelit on 09/12/2011 09:55:25

phelit
Starting Member

2 Posts
Posted - 09/12/2011 :  10:43:59  Show Profile  Reply with Quote
I have found this query:

select NAME, count(NAME) from USER.TABLE where
(to_char(DATE, 'dd/mm/yyyy') > to_char(sysdate - 7,'dd/mm/yyyy') and VALUE1<>VALUE2)
group by NAME order by NAME;

It gives me a good result. But I need to get only the Count value that is > 7 and not all of them. So to pinpoint directly the tables with issues.

Any help on how to put a limitation in the query above so to see only the counts > of 7?

Thanks again!


Phelit Leafwise
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000