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
	);

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.

---

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

;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
	);

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.

---