Time lapse query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Script Library

Time lapse query

Author

Topic

phelit
Starting Member

2 Posts

Posted - 2011-09-12 : 09:48:45

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

phelit
Starting Member

2 Posts

Posted - 2011-09-12 : 10:43:59

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources