SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Problem with UPDATE statement
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

cseward
Starting Member

18 Posts

Posted - 11/05/2013 :  08:29:42  Show Profile  Reply with Quote
My stored procedure has a temp table. I then create a cursor. for each record in the cursor, I look for a record in the temp table that matches on 3 fields. If a match is found update the temp table record with a field from the cursor record. If no match is found, insert a record to the temp table with the cursor record information.

The update is updating as I want it to, but it also seems to be inserting a record with the cursor field that was used in the update.

This is hard to explain. I will attach a portion of the code below. If I attach output, it doesn't format properly.

DECLARE entry_cursor CURSOR FOR
SELECT entries.date as date, entries.id,
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.Last_Name + ', ' + student.First_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 entries.school_id=school.Id
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 and therapist.Id=7181 and student.Id=37380
group by entries.date, entries.id,
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.last_Name + ', ' + student.First_Name,
entries.minutes,entries.notes

Open entry_cursor

FETCH NEXT FROM entry_cursor
INTO @date,@id, @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 School,SchoolId,Therapist,TherapistId,TherapyType,Child,ChildId,DATE,Direct,GroupSize,
Consult,ReEval,Indirect,Screen,Eval,RTI,Other,Individual,Intervention,EI,Rate,Hours,Cost,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))+' '+@student_name+' '+@therapist_name+' '+cast(@minutes as varchar(6))+' '+cast(@type_id as varchar(7))
update #CostPerChildTemp set
Consult = Case When (@type_id = 138) then (@minutes) else Consult end,
ReEval = Case When (@type_id = 139) then ( @minutes) else ReEval end,
Indirect = Case When (@type_id = 140) then ( @minutes) else Indirect end,
Screen = Case When (@type_id = 142) then ( @minutes) else Screen end,
Eval = Case When (@type_id = 143) then ( @minutes) else Eval end,
Individual = case when (@type_id=141) then (@minutes) else Individual end,
Intervention=case when (@type_id=144) then (@minutes) else Intervention end,
EI=case when (@type_id=157) then (@minutes) else EI end
where Date = @date and ChildId = @student_id and TherapistId = @therapist_id

END
ELSE
print 'NEW '+cast(@date as varchar(10))+' '+@student_name+' '+@therapist_name+' '+cast(@minutes as varchar(6))+' '+cast(@type_id as varchar(7))
Insert into #CostPerChildTemp (School, SchoolId, Therapist, TherapistId, TherapyType, Child, ChildId, Date,Direct,GroupSize, Consult, ReEval, Indirect, Screen, Eval,RTI,Other, Individual, Intervention, EI,Rate,Hours,Cost, Notes)
values (@school_name,
@school_id,
@therapist_name,
@therapist_id,
@therapy_type_name,
@student_name,
@student_id,
@date,
0,
0,
Case When (@type_id = 138) then @minutes else 0 end,
Case When (@type_id = 139) then @minutes else 0 end,
Case When (@type_id = 140) then @minutes else 0 end,
Case When (@type_id = 142) then @minutes else 0 end,
Case When (@type_id = 143) then @minutes else 0 end,
0,
0,
Case When (@type_id = 141) then @minutes else 0 end,
Case When (@type_id = 144) then @minutes else 0 end,
Case When (@type_id = 157) then @minutes else 0 end,
@rate,
0,
0,
@notes )


set @type_id=0
set @therapist_id=0
set @therapist_name=''
set @therapy_type_name=''
set @school_id=0
set @school_name=''
set @student_id=0
set @student_name=''
set @minutes=0
set @rate=0
set @notes=''



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

print 'AFTER FETCH '+@school_name+' '+cast(@school_id as varchar(10))


END
CLOSE entry_cursor;
DEALLOCATE entry_cursor;


select * from #CostPerChildTemp

Ifor
Aged Yak Warrior

574 Posts

Posted - 11/05/2013 :  09:32:31  Show Profile  Reply with Quote
The obvious problem with your code is a lack of a BEGIN END block.
However the whole approach of using a cursor is just so wrong that if produced here you would be shown the door.

You should either use separate set base INSERTs and UPDATEs or use the MERGE command.

An outline of MERGE is below - the syntax is in BOL.


MERGE #CostPerChildTemp AS T
USING
(
	SELECT entries.date as date, entries.id,
		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.Last_Name + ', ' + student.First_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 entries.school_id=school.Id
		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 and therapist.Id=7181 and student.Id=37380
	group by entries.date, entries.id,
		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.last_Name + ', ' + student.First_Name,
		entries.minutes,entries.notes
) AS S
ON (T.[Date] = S.[date] AND T.ChildId = S.student_id and T.TherapistId = S.therapist_id)
WHEN MATCHED THEN 
    UPDATE SET
		Consult = CASE WHEN (S.[type_id] = 138) THEN S.[minutes] ELSE Consult END
		,ReEval = Case When (S.[type_id] = 139) then S.[minutes] else ReEval end
		,Indirect = Case When (S.[type_id] = 140) then S.[minutes] else Indirect end
		,Screen = Case When (S.[type_id] = 142) then S.[minutes] else Screen end
		,Eval = Case When (S.[type_id] = 143) then S.[minutes] else Eval end
		,Individual = case when (S.[type_id]=141) then S.[minutes] else Individual end
		,Intervention=case when (S.[type_id]=144) then S.[minutes] else Intervention end
		,EI=case when (@type_id=157) then S.[minutes] else EI end
WHEN NOT MATCHED THEN
	INSERT (School, SchoolId, Therapist, TherapistId, TherapyType, Child, ChildId, Date,Direct,GroupSize, Consult, ReEval, Indirect, Screen, Eval,RTI,Other, Individual, Intervention, EI,Rate,Hours,Cost, Notes)
	VALUES
	(
		S.school_name,
		S.school_id,
		S.therapist_name,
		S.therapist_id,
		S.therapy_type_name,
		S.student_name,
		S.student_id,
		S.date,
		0,
		0,
		Case When (S.type_id = 138) then S.minutes else 0 end,
		Case When (S.type_id = 139) then S.minutes else 0 end,
		Case When (S.type_id = 140) then S.minutes else 0 end,
		Case When (S.type_id = 142) then S.minutes else 0 end,
		Case When (S.type_id = 143) then S.minutes else 0 end,
		0,
		0,
		Case When (S.type_id = 141) then S.minutes else 0 end,
		Case When (S.type_id = 144) then S.minutes else 0 end,
		Case When (S.type_id = 157) then S.minutes else 0 end,
		S.rate,
		0,
		0,
		S.notes
	);


Edited by - Ifor on 11/05/2013 09:33:41
Go to Top of Page

cseward
Starting Member

18 Posts

Posted - 11/05/2013 :  09:47:01  Show Profile  Reply with Quote
thank you for your reply and your constructive criticism - I intend to learn the proper set processing techniques to replace the cursor logic.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 11/05/2013 :  10:44:11  Show Profile  Reply with Quote
quote:
Originally posted by cseward

thank you for your reply and your constructive criticism - I intend to learn the proper set processing techniques to replace the cursor logic.


You need to if you want to be good in sql

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

cseward
Starting Member

18 Posts

Posted - 11/06/2013 :  10:35:49  Show Profile  Reply with Quote
Hello Visakh16. Your solution is good, but I can't use it the way you presented it because the Target set has more than 1 match in the Source set. I want to show you sample data, but it does not view properly in this form. is there another way to present sample data in this forum ?
To get around the problem, I thought i could do several merges. One for each time type. So, I would be update the Target set with Consult time. Then start execute another Merge to update the Target set with Individual time, etc. Is this allowed within a stored procedure ? Can I execute several merge statements to the same Target table.
Go to Top of Page

Ifor
Aged Yak Warrior

574 Posts

Posted - 11/06/2013 :  11:34:35  Show Profile  Reply with Quote
The best way to provide test data is in a consumable format:

CREATE TABLE #PTS_student_time_entry (...);
INSERT INTO #PTS_student_time_entry
SELECT ...
UNION ALL
SELECT ...
--etc
CREATE TABLE #VwRec_Schools 
INSERT INTO #VwRec_Schools 
SELECT ...
UNION ALL
SELECT ...
--etc


Any merge operation requires a one to one match between source and destination. In this case it looks as though you need to pivot the source query.

Maybe something like:



;WITH OrgSource
AS
(
	SELECT entries.date as date, entries.id,
		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.Last_Name + ', ' + student.First_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 entries.school_id=school.Id
		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 and therapist.Id=7181 and student.Id=37380
	group by entries.date, entries.id,
		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.last_Name + ', ' + student.First_Name,
		entries.minutes,entries.notes
)
,PivotSource -- should use PIVOT but without sample data this is less likely to be wrong.
(
	SELECT
		school_name,
		school_id,
		therapist_name,
		therapist_id,
		therapy_type_name,
		student_name,
		student_id,
		[date],
		0 AS Direct,
		0 AS GroupSize,
		SUM(CASE WHEN (type_id = 138) THEN [minutes] ELSE 0 END) AS Consult,
		SUM(CASE WHEN (type_id = 139) THEN [minutes] ELSE 0 END) AS ReEval,
		SUM(CASE WHEN (type_id = 140) THEN [minutes] ELSE 0 END) AS Indirect,
		SUM(CASE WHEN (type_id = 142) THEN [minutes] ELSE 0 END) AS Screen,
		SUM(CASE WHEN (type_id = 143) THEN [minutes] ELSE 0 END) AS Eval,
		0 AS RTI,
		0 AS Other,
		SUM(CASE WHEN (type_id = 141) THEN [minutes] ELSE 0 END) AS Individual,
		SUM(CASE WHEN (type_id = 144) THEN [minutes] ELSE 0 END) AS Intervention,
		SUM(CASE WHEN (type_id = 157) THEN [minutes] ELSE 0 END) AS EI,
		rate,
		0 AS [Hours],
		0 AS Cost,
		notes
	FROM OrgSource
	GROUP BY school_name, school_id, therapist_name, therapist_id, therapy_type_name, student_name,  student_id, [date], rate, notes
)
MERGE #CostPerChildTemp AS T
USING (SELECT * FROM PivotSource) S
ON (T.[Date] = S.[date] AND T.ChildId = S.student_id and T.TherapistId = S.therapist_id)
WHEN MATCHED THEN 
    UPDATE SET
		Consult = S.Consult
		,ReEval = S.ReEval
		,Indirect = S.Indirect
		,Screen = S.Screen
		,Eval = S.Eval
		,Individual = S.Individual
		,Intervention = S.Intervention
		,EI = S.EI
WHEN NOT MATCHED THEN
	INSERT (School, SchoolId, Therapist, TherapistId, TherapyType, Child, ChildId, Date,Direct,GroupSize, Consult, ReEval, Indirect, Screen, Eval,RTI,Other, Individual, Intervention, EI,Rate,Hours,Cost, Notes)
	VALUES
	(
		S.school_name,S.school_id,S.therapist_name,S.therapist_id,S.therapy_type_name,S.student_name,S.student_id,S.[date],
		S.Direct,S.GroupSize, S.Consult, S.ReEval, S.Indirect, S.Screen, S.Eval,S.RTI,S.Other, S.Individual, S.Intervention, S.EI,
		S.Rate,S.[Hours],S.Cost, S.Notes
	);


Edit:- I forgot to add aggreates to PivotSource - a good reason to provide sample data in consumable format to enable testing.

Edited by - Ifor on 11/06/2013 12:29:05
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 11/06/2013 :  12:22:31  Show Profile  Reply with Quote
quote:
Originally posted by cseward

Hello Visakh16. Your solution is good, but I can't use it the way you presented it because the Target set has more than 1 match in the Source set. I want to show you sample data, but it does not view properly in this form. is there another way to present sample data in this forum ?
To get around the problem, I thought i could do several merges. One for each time type. So, I would be update the Target set with Consult time. Then start execute another Merge to update the Target set with Individual time, etc. Is this allowed within a stored procedure ? Can I execute several merge statements to the same Target table.



First of all thanks for giving me the credit even if I didnt post any solution
To post sample data use code tags [ code ] and [/ code ] without the in between spaces and you'll get it properly aligned.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000