Link to home
Start Free TrialLog in
Avatar of AlHal2
AlHal2Flag for United Kingdom of Great Britain and Northern Ireland

asked on

Selected row counts on lots of tables

I have several tables  in an SQL 2005 database.  Each table has 2 fields maintdate and Isdeleted.
The maintdate field says when the row was updated.
The IsDeleted field says if the record is logically deleted  (to be physically deleted at a later date)

For each table I want a count of the number of rows where the maintdate is in the last week and Isdeleted = N.  The outupt would look something like this.

Table Name,  Maintdate Today, MaintDate Yesterday,....,MaintDate 7 days ago.
A
B
C
Avatar of brejk
brejk
Flag of Poland image

How many tables do you have?

(execute: use YourDatabase; select count(*) from sys.objects where type='U')
Avatar of AlHal2

ASKER

About 100.  Also I don't want the row count of the entire table.  I want to filter the output by maintdate and isdeleted as above.
Ok, try to go with my snippet. My code should generate another code (switch to text results in Management Studio) which should be copied and executed. I hope this will work.
DECLARE @newline CHAR(2)
SET @newline = CHAR(13) + CHAR(10)
SELECT 
  'select ' + QUOTENAME(QUOTENAME(SCHEMA_NAME([schema_id])) + '.' + 
  QUOTENAME([name]),'''') + ' AS [Table Name], ' + @newline +
  'count(case when maintdate >= DATEADD(DAY,0,DATEDIFF(DAY,0,GETDATE())) then 1 else null end) as [Maintdate Today],' + @newline +
  'count(case when maintdate >= DATEADD(DAY,0,DATEDIFF(DAY,0,GETDATE()))-1 then 1 else null end) as [Maintdate Today - 1 day],' + @newline +
  'count(case when maintdate >= DATEADD(DAY,0,DATEDIFF(DAY,0,GETDATE()))-2 then 1 else null end) as [Maintdate Today - 2 days],' + @newline +
  'count(case when maintdate >= DATEADD(DAY,0,DATEDIFF(DAY,0,GETDATE()))-3 then 1 else null end) as [Maintdate Today - 3 days],' + @newline +
  'count(case when maintdate >= DATEADD(DAY,0,DATEDIFF(DAY,0,GETDATE()))-4 then 1 else null end) as [Maintdate Today - 4 days],' + @newline +
  'count(case when maintdate >= DATEADD(DAY,0,DATEDIFF(DAY,0,GETDATE()))-5 then 1 else null end) as [Maintdate Today - 5 days],' + @newline +
  'count(case when maintdate >= DATEADD(DAY,0,DATEDIFF(DAY,0,GETDATE()))-6 then 1 else null end) as [Maintdate Today - 6 days],' + @newline +
  'count(case when maintdate >= DATEADD(DAY,0,DATEDIFF(DAY,0,GETDATE()))-7 then 1 else null end) as [Maintdate Today - 7 days]' + @newline +
  'from ' + QUOTENAME(SCHEMA_NAME([schema_id])) + '.' + QUOTENAME([name]) + @newline +
  'where maintdate >= DATEADD(DAY,0,DATEDIFF(DAY,0,GETDATE()))-7 and isdeleted = ''N''' + @newline + 
  'union all'
FROM sys.objects
WHERE [type] = 'U'

Open in new window

Avatar of AlHal2

ASKER

I attach the code generated.
I can't see any reference to the isdeleted column.
Also the heading all say maintdate today.

Here are the error messages when I ran it.
Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'select'.
Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'select'.
Msg 156, Level 15, State 1, Line 10
Incorrect syntax near the keyword 'select'.
Msg 102, Level 15, State 1, Line 13
Incorrect syntax near '.'.
Msg 105, Level 15, State 1, Line 319
Unclosed quotation mark after the character string ' AS [Table Name],
count(case when maintdate >= DATEADD(DAY,0,DATEDIFF(DAY,0,GETDATE())) then 1 else null end) as [Maintdate Today],
count(case when maintdate >= DATEADD(DAY,0,DATEDIFF(DAY,0,GETDATE()))-1 then 1 else null
test.txt
Avatar of AlHal2

ASKER

oops.  I just spotted the isdeleted at the end.  It looks like SQL is truncating the results  on the line.
Ok:

1) set results to text not to grid before executing my snippet,
2) copy, paste and run just the code generated by my query, but...
3) do not copy the last "union all" line!
3) if you have any table with quote in its name then modify lines 4 and 5 of my snippet as follows:

'select quotename( ' + QUOTENAME(SCHEMA_NAME([schema_id])) + '.' +
  QUOTENAME([name]),'''') + ') AS [Table Name], ' + @newline +

4) If you look at the headers they are always like [Maintdate Today - X day(s)] which means a substraction od X days from today's date :-) (Today - 1 = Yesterday and so on)
Avatar of AlHal2

ASKER

Thanks for all the help you've given so far.

The attachment I sent was from "Return to file".  Returning to Text also truncates some rows.

When you say "if you have any table with quote in its name then modify..", do you mean the word "quote" or a quote character?  None of the tables have a quote character.

When I run the revised code I get this message.

Msg 102, Level 15, State 1, Line 5
Incorrect syntax near ')
Avatar of AlHal2

ASKER

What about dynamic SQL to generate something like this for all the tables.  The dynamic SQL would need
to generate queries for getdate - 7 days and all the other tables.

select maintdate,count(*)
from Table1 with (nolock)
where isdeleted='N'
and maintdate = dbo.getISODate(getdate())
group by maintdate
union
select maintdate,count(*)
from Table1 with (nolock)
where isdeleted='N'
and maintdate = dbo.getISODate(getdate())-1
group by maintdate
union
select maintdate,count(*)
from Table2 with (nolock)
where isdeleted='N'
and maintdate = dbo.getISODate(getdate())
group by maintdate
The result of the query you mentioned will be not exactly what you need. Moreover, with dynamic sql you will have to build as complicated query (or even more complicated) as the one in my snippet. And finally - you will have to iterate through the sys.objects catalog view for table names so you probably will have to go with some cursor.
Avatar of AlHal2

ASKER

OK Let's go back to your snippet.  Can you give some feedback on the questions I raised?
Avatar of AlHal2

ASKER

Could the problem be the 256 character limit?
ASKER CERTIFIED SOLUTION
Avatar of brejk
brejk
Flag of Poland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of AlHal2

ASKER

Please note that the date is stored as an integer yyyymmdd
Avatar of AlHal2

ASKER

The problem was the 256 character limit.  I replace getdate with dbo.getISODate(getdate() and it works fine.  Thanks very much.