-1

The following code returns data from 11 tables. Each table contains the year and month. I need to make use of an enddate parameter to check that only tables todate are pulled out. So if the user wants data from 201505 it should pull out all tables only till 201509 as from 201510 tables do not exists assuming we in the month of 201510. Code below:

{

declare 
@enddate varchar(6),
@FirstTableMonth int =201505,
@Table_Name sysname,
@TableMonth int,
@end int,
@CurrentMonth int = 0,
@NextYearMonth int = 1

set @enddate = 201611

WHILE @CurrentMonth < 11
BEGIN

    SELECT  @TableMonth =   CASE WHEN (@FirstTableMonth + @CurrentMonth) % 100 < 13 THEN 
                                @FirstTableMonth + @CurrentMonth 
                            ELSE
                                @FirstTableMonth + 100 - (@FirstTableMonth % 100) + @NextYearMonth
                            END,
                    @NextYearMonth =    CASE WHEN (@FirstTableMonth + @CurrentMonth) % 100 < 13 THEN 
                                    @NextYearMonth
                                ELSE
                                    @NextYearMonth + 1
                                END,
                    @end = case when @enddate 
                    @Table_Name =  'xx_'+CAST(@TableMonth as varchar)+'_T' 

                    SET @CurrentMonth = @CurrentMonth + 1
                    print @Table_Name;
END

}
jarlh
  • 42,561
  • 8
  • 45
  • 63
Miss R
  • 23
  • 6
  • 1
    Please tag dbms used. (Doesn't look like ANSI SQL.) – jarlh Nov 26 '15 at 11:04
  • Why do you have different tables for the same type of data? That looks horrible. There should be one table only. Change this if possible. Then retrieving the data would be very simple. – Thorsten Kettner Nov 26 '15 at 11:08
  • I did not create the data structure – Miss R Nov 26 '15 at 11:11
  • But you can change it? Or have somebody change it? This looks more like fighting the DBMS than simply using it. – Thorsten Kettner Nov 26 '15 at 11:13
  • 1
    You are using multiple tables with names containing dates. This is a highly unorthodox practice which is frowned upon. It is utilized either by hopeless n00bs who don't understand relational databases, or by desperate people who are trying to circumvent hardware limitations in software, instead of buying better hardware. – Mike Nakis Nov 26 '15 at 11:22
  • If the first is the case, then before you even look for a solution to your problem, you should get rid of those multiple tables and put everything in one table. – Mike Nakis Nov 26 '15 at 11:22
  • If the second is the case, then whoever originally decided to employ this highly unorthodox mechanism must have obviously thought of themselves as very smart, so they should be able to solve any problem that arises with this design without having to resort to stackoverflow. – Mike Nakis Nov 26 '15 at 11:23
  • Excuse you Mike! I just said that the structure was created like this many many years ago and it is unchangeable... The point of this site is for assistance - not for you to critise!!! I'm sorry to say but you are extremely rude. – Miss R Nov 26 '15 at 11:48
  • Well, as there is no chance to change the design, let's try to find some way to deal with it ... So there is one table per month starting several years ago? xx_201301, ..., xx_201510? And xx_201511 does not exist yet? Or xx_201511 exists, but shall not be read? So you want to select from all tables from start month 201505 to end month? And end month can be greater than last month (201510 here), but we must never read beyond last month? – Thorsten Kettner Nov 26 '15 at 12:07
  • Thank you Thorsten. Yes there is a table starting several years ago. Xx_201511 and 201510 does not exists. So if we select start month as 201505 then it should throw out 201505 up to 201509 as after 201509 the remainder does not exist. So essentially we have data of everything till before the previous 2 months – Miss R Nov 26 '15 at 12:13

2 Answers2

0

You get all table names from INFORMATION_SCHEMA.TABLES. So just apply your criteria to get them:

select table_name
from information_schema.tables
where table_type = 'BASE TABLE'
and table_name like 'xx\_______\_T' escape '\'  
and table_name >= 'xx_' + CAST(@FirstTableMonth as varchar) + '_T';

SQL fiddle: http://www.sqlfiddle.com/#!6/7b4f5/2

Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73
  • Thank you Thorsten but I am not sure how to put this in my code. This needs to be within the while loop. Would it be possible to refer Zohar Peled to this link as I am unable to access chat. Zohar helped me with the loop – Miss R Nov 26 '15 at 12:59
  • Well, it looked like you wanted to retrieve the table names, so you wrote a loop in a stored procedure. I am telling you that you can use a mere query for this. No stored procedure, no loop. If you must put this in a stored procedure for some reason, then create a cursor with the query and fetch the names in a loop. Here is an example: http://stackoverflow.com/questions/1943892/sql-server-print-select-print-a-select-query-result. – Thorsten Kettner Nov 26 '15 at 13:40
  • Its not that simple since I use openquery to access each of the tables as that server has restrictions. Unfortunately I can not use that method, but thank you anyway – Miss R Nov 26 '15 at 13:59
  • Okay, sorry about that. My other answer is better anyway :-) – Thorsten Kettner Nov 26 '15 at 14:04
  • Haha! thanks, Would it be possible to refer Zohar Peled to this link as I am unable to access chat. Zohar helped me with the loop? – Miss R Nov 26 '15 at 14:10
  • You want a certain person to read here? Well, you can write a comment using their name with an at sign. Like so: Please @Miss R, help me once more... By the way, I was serious about my other answer. You would be able to change the database design, but have it look and work like before, so old apps would still run. By and by you could switch to accessing the new table directly and get rid of cumbersome old queries. You should discuss this with your colleagues. – Thorsten Kettner Nov 26 '15 at 14:35
  • Thank you Thorsten for you assistance. Unfortunately that database is restricted to a certain extent so that would not be possible – Miss R Nov 27 '15 at 06:14
  • @Zohar Peled please can you assist with the above as I received help from you on this previously – Miss R Nov 27 '15 at 06:15
0

I've posted an answer explaining how to retrieve the table names.

But it would really be better to change the design instead. Make it one table and create views for the months (named as the tables are named now). Provide the views with instead-of insert triggers, so you can use select, update, insert and delete just as before, but you'd also have a proper table to work with:

select * from xx_all_t where month >= @FirstTableMonth;
Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73