without cursor

[sarav2k]

Hi,
I have a table called months_data. this contains table_name and startdate as fields and 13 rows representing 12 months + 1 current month.

Now I want to retrieve tablenames after ordering the startdate in Desc order. so that i can current month and following months tables resp.

can anyone help ,how to do this without cursor? I have already done it with cursor?

thanks
Sarav

 

[rt63]

On the face of it, looks like a simple query. But I am sure there must be a catch. Can you post some sample data and the output which you require?
RT

 

[sarav2k]

Hi RT
This is the sample Table called months-data

Key startdate Table_name
1 2001-09-01 Table02
2 2001-10-01 Table03
3 2001-11-01 Table04
4 2001-12-01 Table05
5 2002-01-01 Table06
6 2002-02-01 Table07
7 2002-03-01 Table08
8 2002-04-01 Table09
9 2002-05-01 Table10
10 2002-06-01 Table11
11 2002-07-01 Table12
12 2002-08-01 Table13
13 2002-09-01 Table01

i want to get the latest 2 tablenames and create a view..and another view with rest of the tables.

thanks in advance
sarav

 

[rt63]

Will your table always have 13 records with key values 1 to 13. If yes then


select * from months-data where key > 11 order by key desc

will give you records for last 2 months

and

select * from months-data where key <= 11 order by key desc

will give you records for rest

Maybe, I have not understood your data/problem properly....
RT

 

[sarav2k]

RT thanks for your reply

there will always 13 records only but it doesn't mean the 13th key record will have the recent date..got it?

so we can't depend on key value.

sarav

 

[rt63]

OK. Understanding it better now. Try this

for last 2 months

select top 2 * from months-data order by startdate desc


for the rest

select * from months-data
where key not in
(select top 2 key from months-data order by startdate desc)





RT

 

[sarav2k]

Gr8 RT...but how do i save the table names to local variables so that I can use them for craete views(thats the goal)...


thanks a lot
sarav

 

[rt63]

I am lost again. Can you elaborate a bit - maybe with some example?
RT

 

[sarav2k]

your query will give the last 2 recent months' table names,good.

Now How to store them in local variables?

sarav

 

[rt63]

declare @last varchar(30), @second_last varchar(30)

set @last = (select top 1 table_name from months_data order by startdate desc)
set @second_last = (select top 1 table_name from months_data
where table_name <> @last order by startdate desc)






RT