Finding the count with month wise

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

SQL Server 2005 Forums

SQL Server Administration (2005)

Finding the count with month wise

Author

Topic

manickarajbtech
Starting Member

4 Posts

Posted - 2011-01-30 : 12:27:55

hi ,

i need a query that out put contains,
list out all tables , in a databse with columnname having datatime,date & time datatypes
and its schema name,
from that tables i need a count for particular column(whach having
with datatime,date & time ) month wise.

i come out except the count . here is query

select convert(varchar, getdate(), 101) Gather_Date, @@servername Server_Name,(SELECT DB_NAME()) AS Database_Name,

so.name table_name ,
ss.table_schema onwer,

sc.name column_name

,st.name data_type

--,(convert(varchar, sc.name, 112), 0,7) Bucket_Date
, count( sc.name) Num_Rows

from sysobjects so

/*select * from sysobjects
SELECT COUNT(name)
FROM sysobjects

*/
inner join syscolumns sc on (so.id = sc.id)

inner join information_schema.tables ss on (so.name = ss.table_name)

inner join systypes st on (st.type = sc.type)

where so.type = 'U'

and st.name IN ('DATETIME', 'DATE', 'TIME')
"

the function to calculate the count is

substring(convert(varchar, column_name, 112), 0,7) Bucket_Date, count(*)Num_Rows from table_name

but it works separately..

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-01-30 : 13:13:48

Not sure exactly what that is even saying, but it sounds like you want to count, by month, the value of dates stored in date columns, by month

which you could do like this, generating SQL statements from Information_Schema.Columns and executing the strings


Declare @SQL varchar(max)

SELECT @SQL =  
'SELECT ''' 
+ TABLE_CATALOG +''' as DatabaseName,'''
+ TABLE_NAME + ''' as TableName,'''
+ TABLE_SCHEMA + ''' as [Owner],'
+ 'DATEPART(MONTH,[' + COLUMN_NAME + ']),'
+ ' COUNT(*) ' + 'as BucketDate' 
+ ' FROM [' + TABLE_NAME + '] ' 
+ ' GROUP BY DATEPART(MONTH,[' + COLUMN_NAME + ']);'
FROM INFORMATION_SCHEMA.Columns
WHERE data_type = 'date' 

 
EXEC (@SQL)

Which will count and group by month every table, and every table/date column combination

If you are saying your column name IS a date, and you want the month from it ( and therefore count the number of columns that have a certain MONTH in the COLUMN NAME itself you might use something more like this:


SELECT COUNT(*), MONTH(COLUMN_NAME) as Bucket_Date,TABLE_NAME, TABLE_CATALOG as DatabaseName, TABLE_SCHEMA as [dbowner]
FROM INFORMATION_SCHEMA.Columns
Where DATA_TYPE in ('date') and ISDATE(Column_Name)
GROUP BY MONTH(COLUMN_NAME),TABLE_NAME, TABLE_CATALOG,TABLE_SCHEMA
--untested because i won't even create a column with a date for a COLUMN_NAME for practice

Poor planning on your part does not constitute an emergency on my part.

manickarajbtech
Starting Member

4 Posts

Posted - 2011-01-31 : 02:24:18

tq ,

what i try to say is

i need a ouput like

(Gather_date , Server_name,Databse_name,Table_onwer,Table_Name,Column_name,bucket_date,Num_rows)

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-01-31 : 07:01:07

That isn't output, those are column names. Did you look into my options above? The first one might likely provide the results you are looking for.
surely you can rename the columns as you please, but what you intend to summarize isn't clear. Show some sample data and desired results...

Poor planning on your part does not constitute an emergency on my part.

manickarajbtech
Starting Member

4 Posts

Posted - 2011-01-31 : 07:45:57

hi ,

Gather_Date Server_Name Database_Name Table_Owner Table_Name Column_Name Bucket_Date Num_Rows
01/31/2011 LT007002 TEST dbo Table_1 Rdate 201011 1
01/31/2011 LT007002 TEST dbo Table_1 Rdate 201012 1

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-01-31 : 13:01:48

Okay so you have a column called Rdate in table_1 that you wanted bucketed by year/month, and you want to count the number of rows matching that year/month...but based on your original post, you want to do this for every column in a database which is datetime.

The code I posted above modified to do this by year-month.

Declare @SQL varchar(max)

SELECT @SQL =
'SELECT '''
+ TABLE_CATALOG +''' as DatabaseName,'''
+ TABLE_NAME + ''' as TableName,'''
+ TABLE_SCHEMA + ''' as [Owner],'
+ 'LEFT(CONVERT(varchar(8),[' + COLUMN_NAME + '],112),7)),'
+ ' COUNT(*) ' + 'as BucketDate'
+ ' FROM [' + TABLE_NAME + '] '
+ ' GROUP BY LEFT(CONVERT(varchar(8),[' + COLUMN_NAME + '],112),7));'
FROM INFORMATION_SCHEMA.Columns
WHERE data_type = 'date'

EXEC (@SQL)



I can't help you anymore until you help yourself a little by explaining more clearly what it is you want




Poor planning on your part does not constitute an emergency on my part.

manickarajbtech
Starting Member

4 Posts

Posted - 2011-01-31 : 13:29:44

ya sure

bucket data having the format of YYYYMM ok. from that i need the count

so for eg;
in my table_1 and column (Rdate ) having dates like 20100122(YYYYMMDD),20100123,20100212,20100214 and 20100325

my desried output would be
(here i give by column wise)

bucket_date, Count
201001 , 2
201002 , 2
201003, 1

before bucket_date

db_name,Schema_name,table_name,column_name are needed ---
thanks for reply's

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-01-31 : 13:42:25

Are your dates in datetime format? or are they strings?

If strings, the below would work..

Select LEFT(Rdate,6),COUNT(*)
FROM Table_1
GROUP BY LEFT(Rdate,7=6)

If dates,

Select convert(varchar(6),Rdate,112),COUNT(*)
FROM Table_1
GROUP BY convert(varchar(6),Rdate,112)

will work for dates.

The below does exactly what you ask for datetime fields (you can change to 'date' or 'time' for SQL 2008 if you need to). As long as you don't have a huge amount of tables..the variable will okay. If you have loads of tables to do this with, you may want to specify which tables by adding a condition/filter in the Where clause for the Table_Names.



Declare @SQL varchar(max)

SELECT @SQL =
'SELECT '''
+ TABLE_CATALOG +''' as DatabaseName,'''
+ TABLE_NAME + ''' as TableName,'''
+ TABLE_SCHEMA + ''' as [Owner],'
+ 'CONVERT(varchar(6),[' + COLUMN_NAME + '],112) as Bucket_Date,'
+ ' COUNT(*) ' + 'as BucketDate'
+ ' FROM [' + TABLE_NAME + '] '
+ ' GROUP BY CONVERT(varchar(6),[' + COLUMN_NAME + '],112);'
FROM INFORMATION_SCHEMA.Columns
WHERE data_type = 'datetime'


Exec ( @SQL)

Poor planning on your part does not constitute an emergency on my part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources