May 10, 2013 at 12:27 pm
Hi,
I have a table that I need to update based on the following conditions.
If the inventory type is physical, use that date as the physical inventory date.
Otherwise, use the date of the last physical inventory taken before this one.
I'm currently using a cursor for this, but the performance of my query suffers because of it.
Below are the table and queries I'm using to update it.
Is there anything else I can use that's faster?
Table
create table #inv (
Rep_LName nvarchar (50),
Rep_FName nvarchar (50),
Rep_ID nvarchar (50),
Rep_Email nvarchar (100),
Rep_Status nvarchar (50),
Rep_BU nvarchar (50),
Sales_Force nvarchar (50),
Territory nvarchar (50),
Sample_Eligibility nvarchar (50),
DM_Name nvarchar (100),
Phys_Inv_Date datetime,
Last_Reconciled datetime,
Inv_Type nvarchar(50),
Days_Since_Last_inv int)
Queries:
update i
set i.Inv_Type = h.inventory_type
from #inv i
inner join inv_header h on i.rep_id = h.rep_id
and h.Call_date =
(select Max(Call_Date)
from inv_header i2
where i2.rep_id = i.rep_id)
where i.inv_type is null
declare Inventory_info cursor
for select distinct rep_ID, call_date, inventory_type
from inv_header
where rep_id in (select rep_id from #inv)
Order by rep_ID, call_date desc, inventory_type desc
declare @call_date datetime
declare @rep_ID nvarchar(50)
declare @inventory_type nvarchar(50)
declare @ls_Sql as nvarchar(max)
declare @param as nvarchar(max)
select @ls_Sql=''
select @param=''
-- open cursor
open Inventory_info
fetch next from Inventory_info
into @rep_ID, @call_date, @inventory_type
while (@@fetch_status = 0)
begin
--use parameterized dynamic sql
SET @param='@rep_ID nvarchar(50),@call_date DATETIME,@inventory_type nvarchar(50)'
SET @ls_Sql='update #inv set Phys_Inv_Date = case when inv_type = ''physical'' and @inventory_type = ''physical'' then @call_date else b.call_date end from #inv a INNER JOIN (select top 1 call_date, rep_id from inv_header where call_date <= @call_date and rep_id = @rep_id and inventory_type = ''physical'' order by call_date desc) b ON a.rep_id = b.rep_id WHERE Phys_Inv_Date IS NULL'
--pass parameter to dynamic query
exec sp_executesql @ls_Sql,@param,@rep_ID,@call_date,@inventory_type
fetch next from Inventory_info
into @rep_ID, @call_date, @inventory_type
end
close Inventory_info
deallocate Inventory_info
May 10, 2013 at 12:46 pm
Not quite sure why you are using dynamic sql in the middle of your cursor. I am very confused by the case expression in your update. You either use the value from inv_header or the value in your variable (which also came from inv_header). Since we don't have that table to work with and there is no data it is hard to know for sure but I think those two values would always be the same thing???
Something like this should be a decent starting point for replacing your cursor. I may be over simplifying it but without structures to work with it is impossible to test.
update #inv
set Phys_Inv_Date = h.call_date
from #inv i
INNER JOIN inv_header h ON i.rep_id = h.rep_id
WHERE h.Phys_Inv_Date IS NULL
and inv_type = 'physical'
_______________________________________________________________
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 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply