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
AlHal2Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

brejkCommented:
How many tables do you have?

(execute: use YourDatabase; select count(*) from sys.objects where type='U')
0
AlHal2Author Commented:
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.
0
brejkCommented:
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

0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

AlHal2Author Commented:
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
0
AlHal2Author Commented:
oops.  I just spotted the isdeleted at the end.  It looks like SQL is truncating the results  on the line.
0
brejkCommented:
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)
0
AlHal2Author Commented:
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 ')
0
AlHal2Author Commented:
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
0
brejkCommented:
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.
0
AlHal2Author Commented:
OK Let's go back to your snippet.  Can you give some feedback on the questions I raised?
0
AlHal2Author Commented:
Could the problem be the 256 character limit?
0
brejkCommented:
I don't know. Did you set max number of characters for Grid Results and max number of characters in single column for Text Results in SSMS?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AlHal2Author Commented:
Please note that the date is stored as an integer yyyymmdd
0
AlHal2Author Commented:
The problem was the 256 character limit.  I replace getdate with dbo.getISODate(getdate() and it works fine.  Thanks very much.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.