November 14, 2011 at 6:06 pm
I have a stored procedure that does a merge with not matched.
The below sproc is fine until today we have some problem.
The sproc is to compare some data in two tables, if not exist, then insert into the target table.
The target table has a unique key of studentID, schoolid and seqnbr, it failed to excute the sproc because there may be a TruancyStatus=isClosed that has the same unique key.
We want to change it to if the status is closed we want to increment the seq by 1.
And also for all Status= isopened ones keep what we have now.
How in the below to add the logic into
CREATE PROCEDURE [dbo].[TruancyCase_Daily_Update]
AS
SET NOCOUNT ON;
MERGE TruancyCase tc
USING (
SELECT TOP 100 PERCENT
td.StudentID
,td.SchoolID
,td.SchoolYear
,s.GradeLevel
,ROW_NUMBER() OVER(PARTITION BY td.StudentID ORDER BY td.StudentID) AS SeqNbr
FROM Truant_Days td
LEFT JOIN Student s ON s.StudentId = td.StudentID
WHERE td.AbsenceDate <= CAST(GETDATE() AS DATE)
GROUP BY td.StudentID
,td.SchoolID
,td.SchoolYear
,s.GradeLevel
HAVING COUNT(*) > 1
) AS td
ON tc.StudentID = td.StudentID
AND tc.SchoolYear = td.SchoolYear
AND tc.SeqNbr = td.SeqNbr
AND tc.TruancyStatus=isopen
WHEN NOT MATCHED THEN
INSERT
(
StudentID
,SchoolYear
,SeqNbr
,TruancyStatusID
,SchoolID
,GradeLevel
,CourtNbr
,EligibilityYYMM
,ThresholdDate
,StayExpirationDate
,ClosureDate
,ContractSignedDate
,PetitionTypeID
,PetitionFileDate
,AdminName
,AdminTitle
,CreateDate
,CreatedBy
)
VALUES
(
td.StudentID
,td.SchoolYear
,td.SeqNbr
,1 /*TruancyStatus = Awaiting SAA from school*/
,td.SchoolID
,td.GradeLevel
,NULL /*CourtNbr*/
,NULL /*Eligibili
November 14, 2011 at 8:11 pm
What should the output look like?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 14, 2011 at 11:57 pm
The below sproc is fine until today we have some problem.
Please define your problem with consumable data (DDL, sample data, Output format etc.).
November 15, 2011 at 9:49 am
Never mind, I felt it is so hard to describe it.
November 15, 2011 at 9:54 am
It is when you can lay out a table of what the expected results should look like, that often times the solution becomes more apparent for you. It also becomes much easier for others to try and test solutions that could work for you.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply