January 7, 2013 at 7:19 am
hi
i have two tables
table A
main_table
id fill_date
1 09/04/2003
2 12/31/2005
3 01/05/1985
product
id name
1 oxygen
2 detox
3 carbo
what i want is top 10 product for each month in each year without cte.
output
in year 2007
january top 10 productname (calculate by count(productname) desc)
feb ----
....
dec
in year 2008
january top 10 productname (calculate by count(productname) desc)
feb ----
....
dec
please help me
January 7, 2013 at 7:22 am
You've been here long enough to know the score. Please provide DDL, sample data and desired output in readily consumable form, as per the link in my signature.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
January 7, 2013 at 7:22 am
Why the requirement that CTEs cannot be used?
January 7, 2013 at 8:12 am
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
January 7, 2013 at 8:26 am
Your sample data is obviously not complete. It is totally useless in it's current form. You have two tables, one with a date and one with a varchar. There is nothing tie them together. From your vague post it seems that you want to use a cross tab with some grouping. You have a couple paths to get from where you to a solution. You can read the link in my signature about cross tabs and figure it out on your own. Your second option is to read the article at the first link in my signature and post ddl and sample with enough information to solve your problem and we can help. The path you chose is entirely up to you.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 8, 2013 at 7:01 am
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
Few things. First, you really should have posted this in the SQL Server 7/2000 forums (and no, do repost it now). Second, you should have simply stated that you are using SQL Server 2000 up front instead of saying no CTEs. The third, Sean already mentioned. You really haven't given us enough to really help you yet. Please read the second article he suggested (it also happens to be the first one I reference below in my signature block as well) and follow the instructions in that article about what you should post and how to post it.
The more you do for us up front, the better answers you will get in return.
January 8, 2013 at 10:08 am
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
end
SELECT * FROM @TEMP_TOTAL
January 8, 2013 at 10:23 am
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
end
SELECT * FROM @TEMP_TOTAL
That would probably work but it will be slower than molasses on the Alaskan tundra in January. This can absolutely be done with a single insert statement but until we have details to work with there is no way to help write the query.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply