UPDATING A TEMP TABLE - HELP !!!

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

UPDATING A TEMP TABLE - HELP !!!

Author

Topic

cseward
Starting Member

24 Posts

Posted - 2013-10-24 : 07:04:11

I have a temp table. I also have a cursor. for each row in the cursor, I see if there is a row in the temp table that matches 3 of the values from the cursor row. If a match is found, I want to UPDATE 8 other values of the temp table. the UPDATE is not working properly. I will paste the code below.

Open entry_cursor

FETCH NEXT FROM entry_cursor
INTO @date, @type_id,@therapist_id,@therapist_name,@therapy_type_name,
@school_id,@school_name,@student_id,@student_name,@minutes,@rate,@notes

WHILE @@FETCH_STATUS = 0
BEGIN
IF exists (select ID,School,SchoolId,Therapist,TherapistId,TherapyType,Child,ChildId,DATE,Direct,GroupSize,
Consult,ReEval,Indirect,Screen,Eval,RTI,Other,Individual,Intervention,EI,Rate,Hours,Cost,TimeType,Notes
from #CostPerChildTemp temp where temp.Date = @date and temp.ChildId = @student_id and temp.TherapistId = @therapist_id )
BEGIN
print 'UPDATE '+cast(@date as varchar(10))+' '+cast(@student_id as varchar(10))+' '+cast(@therapist_id as varchar(10))
update #CostPerChildTemp (consult,reeval,indirect,screen,eval,individual,intervention,ei) set
Consult = Case When (@type_id = 138) then (Consult + @minutes) else Consult end,
ReEval = Case When (@type_id = 139) then (ReEval + @minutes) else ReEval end,
Indirect = Case When (@type_id = 140) then (Indirect + @minutes) else Indirect end,
Screen = Case When (@type_id = 142) then (Screen + @minutes) else Screen end,
Eval = Case When (@type_id = 143) then (Eval + @minutes) else Eval end,
Individual = case when (@type_id=141) then (Individual+@minutes) else Individual end,
Intervention=case when (@type_id=144) then (Intervention+@minutes) else Intervention end,
EI=case when (@type_id=157) then (EI+@minutes) else EI end
where Date = @date and ChildId = @student_id and TherapistId = @therapist_id

END
ELSE
Insert into #CostPerChildTemp (School, SchoolId, Therapist, TherapistId, TherapyType, Child, ChildId, Date, Consult, ReEval, Indirect, Screen, Eval, Individual, Intervention, EI,Rate, Notes)
values (@school_name,
@school_id,
@therapist_name,
@therapist_id,
@therapy_type_name,
@student_name,
@student_id,
@date,
Case When (@type_id = 138) then @minutes else null end,
Case When (@type_id = 139) then @minutes else null end,
Case When (@type_id = 140) then @minutes else null end,
Case When (@type_id = 142) then @minutes else null end,
Case When (@type_id = 143) then @minutes else null end,
Case When (@type_id = 141) then @minutes else null end,
Case When (@type_id = 144) then @minutes else null end,
Case When (@type_id = 157) then @minutes else null end,
@rate,
@notes )

FETCH NEXT FROM entry_cursor
INTO @date, @type_id,@therapist_id,@therapist_name,@therapy_type_name,
@school_id,@school_name,@student_id,@student_name,@minutes,@rate,@notes

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-24 : 08:54:52

I cant see any reason why you should be using a cursor here. It looks like what you want is a simple UPDATE statement based on join to query which you're using to populate the cursor.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

cseward
Starting Member

24 Posts

Posted - 2013-10-24 : 09:14:47

i don't know how to do that.

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2013-10-24 : 10:03:23

I agree that you almost certainly don't need a cursor here, but would have to see the query that builds the cursor to adjust the code for that.

The UPDATE problem is he column list after the table name -- UPDATE doesn't allow a column list, since the SET "tells" SQL which columns will be updated.

update #CostPerChildTemp
~~(consult,reeval,indirect,screen,eval,individual,intervention,ei)~~
set Consult = Case When (@type_id = 138) then (Consult + @minutes) else Consult end,
ReEval = Case When (@type_id = 139) then (ReEval + @minutes) else ReEval end,
Indirect = Case When (@type_id = 140) then (Indirect + @minutes) else Indirect end,
Screen = Case When (@type_id = 142) then (Screen + @minutes) else Screen end,
Eval = Case When (@type_id = 143) then (Eval + @minutes) else Eval end,
Individual = case when (@type_id=141) then (Individual+@minutes) else Individual end,
Intervention=case when (@type_id=144) then (Intervention+@minutes) else Intervention end,
EI=case when (@type_id=157) then (EI+@minutes) else EI end
where Date = @date and ChildId = @student_id and TherapistId = @therapist_id

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-24 : 11:48:33

quote:
Originally posted by cseward

i don't know how to do that.

then why dont you show us cursor query atleast?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

cseward
Starting Member

24 Posts

Posted - 2013-10-24 : 12:21:38

DECLARE entry_cursor CURSOR FOR
SELECT entries.date as date,
entries.student_time_entry_type_id as type_id,
entries.therapist_id as therapist_id,
cms_user.last_name+', '+cms_user.first_name as therapist_name,
therapy_type.Name as therapy_type_name,
entries.school_id as school_id,
school.School_Name as school_name,
entries.student_id as student_id,
student.First_Name + ' ' + student.Last_Name as student_name,
isnull(entries.minutes,0) as minutes,
max(district_bill.Bill_Rate) as rate,
entries.notes as notes

FROM PTS_student_time_entry entries
join VwRec_Schools school on school.District = @District
join VwRec_Students student on entries.student_id = student.Id
join VwRec_Therapists therapist on entries.therapist_id = therapist.Id
join core_c_m_s_user cms_user on therapist.CMS_User = cms_user.id
join VwRec_Therapy_Types therapy_type on therapist.Therapy_Type = therapy_type.Id
join VwRec_District_Bill_Rates district_bill on district_bill.District = @District and therapy_type.Id = district_bill.Therapy_Type
where entries.date >= @StartDate and entries.date <= @EndDate and entries.district_id = @District
group by entries.date,
entries.student_time_entry_type_id,
entries.therapist_id,
cms_user.last_name+', '+cms_user.first_name,
therapy_type.Name,
entries.school_id,
school.School_Name,
entries.student_id,
student.First_Name + ' ' + student.Last_Name,
entries.minutes,entries.notes

cseward
Starting Member

24 Posts

Posted - 2013-10-24 : 12:23:18

i don't have the colummn list after the update anymore - it still does not update properly.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-10-24 : 12:40:08

It's always easer if you post sample data and expected output, here are some links on that:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Here is a sample showing that the update you posted should work:

CREATE TABLE #CostPerChildTemp 
(
	ChildID INT
	,TherapistId INT
	,[Date] DATE
	,consult INT
	,reeval INT
	,indirect INT
	,screen INT
	,eval INT
	,individual INT
	,intervention INT
	,ei INT
); 

INSERT #CostPerChildTemp VALUES
(123, 789, '20130101', 1, 1, 1, 1, 1, 1, 1, 1);

SELECT *
FROM #CostPerChildTemp;

DECLARE @type_id INT = 138;
DECLARE @minutes INT = 98;
DECLARE @student_id INT = 123;
DECLARE @therapist_id INT = 789;
DECLARE @date DATE = '20130101';

update #CostPerChildTemp 
set Consult = Case When (@type_id = 138) then (Consult + @minutes) else Consult end, 
 ReEval = Case When (@type_id = 139) then (ReEval + @minutes) else ReEval end, 
 Indirect = Case When (@type_id = 140) then (Indirect + @minutes) else Indirect end,
 Screen = Case When (@type_id = 142) then (Screen + @minutes) else Screen end,
 Eval = Case When (@type_id = 143) then (Eval + @minutes) else Eval end, 
 Individual = case when (@type_id=141) then (Individual+@minutes) else Individual end,
 Intervention=case when (@type_id=144) then (Intervention+@minutes) else Intervention end,
 EI=case when (@type_id=157) then (EI+@minutes) else EI end
 where Date = @date and ChildId = @student_id and TherapistId = @therapist_id 


SELECT *
FROM #CostPerChildTemp

DROP TABLE #CostPerChildTemp

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2013-10-24 : 13:28:42

>> it still does not update properly. <<

With NO details of any kind, I find it impossible to even attempt to debug it, especially working blind.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources