harri.reddy (1/7/2013)

---

i need to run in sql 2000,so cte is nto working over there.i need select statement.


create table A
(id int primary key,
date1 date)


create table tableb
(id int references tableA(id),
productname varchar(40)


insert into tableA values(1,'01/01/2008')
insert into tableA values(2,'12/31/2005')
insert into tableA values(3,23/03/2004')


insert into tableb(1,'ordfy')
insert into tableb(2,'detox')
insert into tablec(3,'lokhj')

my output should be


year 2005
month drugname drugname drugname
january top1drugname top2drugname top10drugname
february top1drugname top2drugname top10drugname
march



dec

year 2006

month drugname drugname drugname
january top1drugname top2drugname top10drugname
february top1drugname top2drugname top10drugname
march



dec


till year 2012

what i want is top 10 product in each month in each year

mdsharif532 (1/8/2013)

---

Just follow the sample code, hope it will help you to get start.............................................

declare @index integer
set @index = 1
DECLARE @C_YEAR VARCHAR(4)
SET @C_YEAR = 1995

DECLARE @TEMP_TOTAL TABLE
(
TERM_REPORTING_YEAR decimal(4),
TERMS_ID varchar(7)

)

while @index <= (SELECT count (distinct CATALOGS) FROM PROGRAMS )
begin

set @C_YEAR = @C_YEAR + 1

INSERT @TEMP_TOTAL
SELECT top 3 TERM_REPORTING_YEAR, TERMS_ID
FROM TERMS WHERE TERM_REPORTING_YEAR = @C_YEAR

set @index = @index + 1
end

SELECT * FROM @TEMP_TOTAL