I have a database that has a table for each day making it a huge database consisting of so many months and even years. Now, I wanna count the records of all the tables in each month. I mean, I want the count of all the records in all tables but monthly apart.
I already used this query:
select
(SELECT count(*) FROM [Rayanparsi].[dbo].[TransRecordTable_14000601] where mti=200 )+
(SELECT count(*) FROM [Rayanparsi].[dbo].[TransRecordTable_14000602] where mti=200 )+
(SELECT count(*) FROM [Rayanparsi].[dbo].[TransRecordTable_14000603] where mti=200 )+...
witch is useful but it won't give me the result for each month. it gives me the total count of all the tables existing in the query. Thx everyone.
You should just be able to hard-code the relevant year/month/day values into your query against each table, UNION all individual table queries together and then query that result to give what ever final result you need.
This SQL will give you an example of how to do this; obviously you may need to adjust it to meet your exact requirements
WITH ALL_DATA AS
(SELECT '1400' TABLE_YEAR, '06' TABLE_MONTH, '01' AS TABLE_DAY, count(*) AS TABLE_COUNT FROM [Rayanparsi].[dbo].[TransRecordTable_14000601] where mti=200
UNION
SELECT '1400' , '06', '01', count(*) FROM [Rayanparsi].[dbo].[TransRecordTable_14000602] where mti=200
UNION
SELECT '1400' , '06', '01', count(*) FROM [Rayanparsi].[dbo].[TransRecordTable_14000603] where mti=200
)
SELECT TABLE_YEAR, TABLE_MONTH, SUM(TABLE_COUNT)
FROM ALL_DATA
GROUP BY TABLE_YEAR, TABLE_MONTH
;