根据历史数据建立一张价格的log表
哪位高手帮忙看看下面的语句,语句没有错误,可以运行但是速度非常非常非常慢。
我想要达到的目的是根据[dbo].[Fact_Point_Of_Sale]的数据建立一张新的价格存储表,这张价格存储表包含所有物品的价格走势,简单来说就是一张价格的log表。
语句跑了一天才返回八万条信息,哪位高手看看是哪里漏了什么条件。信息!
Declare @Retail_Partner nvarchar(255),@Retail_Location nvarchar(255),@Lookup_Type nvarchar(20),@Part_Number nvarchar(40),@Value float , @StartDate Date, @enddate date;
DECLARE sub_cursor CURSOR
FOR SELECT [Retail_Partner]
,[Retail_Partner_Location] Retail_Location
,'RETAIL PRICE' Lookup_Type
,[Part_Number]
,max([POS_Price_$]) Value
,cast(cast(cast([POS_Date_ID]as varchar(8)) as datetime) as date) StartDate
,'2999-12-31' enddate
FROM [Point_of_Sales_dev].[dbo].[Fact_Point_Of_Sale]
where isnull([POS_Price_$],0)<>0 and [Retail_Partner] = 'XYZ'
group by [Retail_Partner],[Retail_Partner_Location],[Part_Number],cast(cast(cast([POS_Date_ID]as varchar(8)) as datetime) as date)
order by [Retail_Partner],[Retail_Partner_Location],[Part_Number],cast(cast(cast([POS_Date_ID]as varchar(8)) as datetime) as date)
OPEN sub_cursor
FETCH NEXT FROM sub_cursor into @Retail_Partner ,@Retail_Location ,@Lookup_Type ,@Part_Number ,@Value , @StartDate , @enddate
WHILE @@FETCH_STATUS = 0
begin
declare @t table( [Retail_Partner] [nvarchar](255), [Part_Number] [nvarchar](40) ,[Value] [float] ,[StartDate] [DATE])
insert into @t( Retail_Partner, Part_Number, Value, StartDate)
select a.Retail_Partner, a.Part_Number,a.Value, a.StartDate
from [Point_of_Sales_dev].[dbo].[Dim_Partner_Price_Cost_Values] a,
(select Retail_Partner, Part_Number, max(StartDate) StartDate
from [Point_of_Sales_dev].[dbo].[Dim_Partner_Price_Cost_Values] where Lookup_Type ='RETAIL PRICE' and [Retail_Partner] = 'XYZ' group by Retail_Partner, Part_Number) b
where a.Retail_Partner = b.Retail_Partner and
a.Part_Number = b.Part_Number and
a.StartDate = b.StartDate and
a.Part_Number = @Part_Number and
a.Retail_Partner = @Retail_Partner
order by a.StartDate, a.Part_Number
Declare @old_Value float , @Old_Start_Date date, @new_end_date date;
set @old_Value = (select [Value] from @t)
set @Old_Start_Date = (select StartDate from @t)
set @new_end_date = cast(cast(@StartDate as datetime)-1 as Date)
if not exists (select * from @t where Part_Number = @Part_Number)
begin
insert into [Point_of_Sales_dev].[dbo].[Dim_Partner_Price_Cost_Values](Retail_Partner, Retail_Location, Lookup_Type, Part_Number, Value, StartDate, enddate)
VALUES (@Retail_Partner ,@Retail_Location ,@Lookup_Type ,@Part_Number ,@Value , @StartDate , @enddate );
end
else
begin
if @old_Value<>@Value
begin
update [Point_of_Sales_dev].[dbo].[Dim_Partner_Price_Cost_Values]
set enddate = @new_end_date
where [Point_of_Sales_dev].[dbo].[Dim_Partner_Price_Cost_Values].StartDate = @Old_Start_Date
and [Point_of_Sales_dev].[dbo].[Dim_Partner_Price_Cost_Values].Part_Number = @Part_Number
and [Point_of_Sales_dev].[dbo].[Dim_Partner_Price_Cost_Values].Retail_Partner= @Retail_Partner
and [Point_of_Sales_dev].[dbo].[Dim_Partner_Price_Cost_Values].Lookup_Type = 'RETAIL PRICE'
insert into [Point_of_Sales_dev].[dbo].[Dim_Partner_Price_Cost_Values](Retail_Partner, Retail_Location, Lookup_Type, Part_Number, Value, StartDate, enddate)
VALUES (@Retail_Partner ,@Retail_Location ,@Lookup_Type ,@Part_Number ,@Value , @StartDate , @enddate );
end
delete from @t
end
FETCH NEXT FROM sub_cursor into @Retail_Partner ,@Retail_Location ,@Lookup_Type ,@Part_Number ,@Value , @StartDate , @enddate
end;
close sub_cursor;
DEALLOCATE sub_cursor;