| Author |
Topic |
|
papaneil
Starting Member
2 Posts |
 Posted - 2011-12-19 : 23:09:27
|
| hi, we have an application with an sql back end that has several "log" tables. Each month has its on table devoted to these "logs". example, clog201112. i am trying to write a stored procedure that i will put into reporting services that will allow the user to specify a time frame. i know how to do this with just one table, or one table with a join or two, but how can i do this with these types of tables? thanks. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-12-20 : 00:11:32
|
| why do you need seperate table for each month? isnt it enough to use single table with month column indicating which month the data came?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2011-12-20 : 02:34:00
|
| You need to use dyanamic sql by calculating year and month value from current dateMadhivananFailing to plan is Planning to fail |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-12-20 : 03:16:14
|
| or even if its same table you can organise them into different file groups by applying partitioning in case query performances are low------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
papaneil
Starting Member
2 Posts |
Posted - 2011-12-20 : 05:23:47
|
| i do not control the structure of the database; this is not a custom app. usually there are over 3 million records for each month. i am sure this is the reason for the multiple tables.i figured i would need to base the procedure off the current date, but how do i account for the varying table names: clog201101, clog 201102, clog201103, clog201104, clog201105, clog201106, etc.also, there is a field in each table, evdate, that displays the exact time and date when the record was entered. thanks. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-12-20 : 06:55:29
|
| you have to make use dynamic sql for that. for getting time and date of entry use GETDATE() function------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2011-12-20 : 06:59:54
|
| or you could:Create a partitioned view over the tables and have a job that will recreate the view at the start of each month?Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
|
return value from varied tables