AlHal2
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
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
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'
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
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
count(case when maintdate >= DATEADD(DAY,0,DATEDIFF(DAY
test.txt
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([sch ema_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)
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([sch
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)
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 ')
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 ')
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
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())-
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.
ASKER
OK Let's go back to your snippet. Can you give some feedback on the questions I raised?
ASKER
Could the problem be the 256 character limit?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Please note that the date is stored as an integer yyyymmdd
ASKER
The problem was the 256 character limit. I replace getdate with dbo.getISODate(getdate() and it works fine. Thanks very much.
(execute: use YourDatabase; select count(*) from sys.objects where type='U')