June 16, 2004 at 8:33 pm
Why would this script no correctly rank the product field. (dept nbr and product have the same data) But only dept 90 which is the first processed product has the correct rankings.
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Department_Ranking_Sales]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Department_Ranking_Sales]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE procedure Department_Ranking_Sales as
declare @product varchar(50)
--retrieve the list of Departments
select distinct [dept nbr]
into
#Departments
from
report4
where [dept nbr] is not null
--loop for each Department
while exists (select * from #Departments) begin
create table #rankings (
ranknum int identity(1,1),
[store nbr] int
)
select
@product = [dept nbr]
from
#Departments
--order by stores by descending sales
insert
#rankings ([store nbr])
select
[store nbr]
from
report4
where
[dept nbr] = @product
order by
tytdsales desc
--set the value in the original table
update a set a.Ranking = b.ranknum from report4 as a join #rankings as b on b.[store nbr] = a.[store nbr]
--remove the department which has been processed
delete
#departments
where
[dept nbr]= @product
--drop & recreate the table to reset the identity value
drop table #rankings
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
June 17, 2004 at 2:01 am
I think the problem is in your update statement:
| update a set a.Ranking = b.ranknum from report4 as a join #rankings as b on b.[store nbr] = a.[store nbr] |
should be:
| update a set a.Ranking = b.ranknum from report4 as a join #rankings as b on b.[store nbr] = a.[store nbr] where a.[dept nbr] = @product |
Since you only want to update the store(s) for the current dept/product, right?
btw: this could have been solved a lot better using a cursor.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply