2 rows showing up in table after updates??? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2005 Forums

Transact-SQL (2005)

2 rows showing up in table after updates???

Author

Topic

ramoneguru
Yak Posting Veteran

69 Posts

Posted - 2007-05-23 : 19:22:10

Doing some maint on this sql implementation (not my methodology, but I'd rather not do a complete rewrite). In any event, I'm getting a row in my table that I didn't expect...here's the code.

Here's the variables/table declarations:

DECLARE @LanguageArtsMinCred
SET @LanguageArtsMinCred = 4

DECLARE @SocialStudiesMinCred
SET @SocialStudiesMinCred = 1

etc...

DECLARE @AllocatedCredits TABLE (
PersonID INT,
LanguageArts REAL,
SocialStudies REAL,
USHistory REAL,
Economics REAL,
Government REAL,
Mathematics REAL,
Science REAL,
ComputerScience REAL,
ELECTIVES REAL,
WorkCredits REAL
)

She then proceeds to do a sum of the values from other tables within the database and store these in the @AllocatedCredits table:

INSERT @AllocatedCredits
SELECT
PersonID,
SUM(CASE WHEN (ClassMeetsRequirements = 'Language Arts') THEN EarnedCredits ELSE 0.0 END) AS LanguageArts,
SUM(CASE WHEN (ClassMeetsRequirements = 'Social Studies') THEN EarnedCredits ELSE 0.0 END) AS SocialStudies,
SUM(CASE WHEN (ClassMeetsRequirements = 'US History') THEN EarnedCredits ELSE 0.0 END) AS USHistory,
etc....

Then based on those values she adjusts the @AllocatedCredits table like so (as per requirements):

DECLARE @result AS REAL
--------Recalculate Language Arts Hours--------
SET @result = (SELECT SUM(LanguageArts) AS LanguageArts FROM @AllocatedCredits) - @LanguageArtsMinCred

IF (@result > 0)
BEGIN
UPDATE @AllocatedCredits
SET Electives = (SELECT SUM(Electives) FROM @AllocatedCredits) + @result, LanguageArts = @LanguageArtsMinCred
END

--------Recalculate SocialStudies Hours--------
SET @result = (SELECT SUM(SocialStudies) FROM @AllocatedCredits) - @SocialStudiesMinCred

IF (@result > 0)
BEGIN
UPDATE @AllocatedCredits
SET Electives = (SELECT SUM(Electives) FROM @AllocatedCredits) + @result, SocialStudies = @SocialStudiesMinCred
END

etc...

Then I select * from @allocatedCredits and get this:
L = language
s = social studies
m = math
etc...

L s m etc...
0 0 0 0 0 0 3 0 13.5
4 1 1 0.5 0.5 4 3 1 13.5

The update inserts a row at the top that shows the value of @result if the result was 0 or the @<subject>MinCred value that was assigned at the beginning of the script.

What the heck is going on, why is it inserting a new row???

--Nick

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-05-24 : 02:22:27

Post the entire query. It's too hard to try and decipher with the missing code.

ramoneguru
Yak Posting Veteran

69 Posts

Posted - 2007-05-24 : 15:09:47

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/****** Version 110; Updated Nov 6 2006 2:16PM ******/
--ALTER PROCEDURE [dbo].[spGetPersonTranscriptAllocatedCredits] @ssn VARCHAR(50)

--AS

--Leave for testing
DECLARE @ssn INT
SET @ssn = 250895497

DECLARE @LanguageArtsMinCred REAL
SET @LanguageArtsMinCred = 4

DECLARE @SocialStudiesMinCred REAL
SET @SocialStudiesMinCred = 1

DECLARE @USHistoryMinCred REAL
SET @USHistoryMinCred = 1

DECLARE @EconomicsMinCred REAL
SET @EconomicsMinCred = 0.5

DECLARE @GovernmentMinCred REAL
SET @GovernmentMinCred = 0.5

DECLARE @MathematicsMinCred REAL
SET @MathematicsMinCred = 4

DECLARE @ScienceMinCred REAL
SET @ScienceMinCred = 3

DECLARE @ComputerScienceMinCred REAL
SET @ComputerScienceMinCred = 1

--Student Allocated Credits will contain the earned credits from linked and unlinked classes
DECLARE @AllocatedCredits TABLE (
PersonID INT,
LanguageArts REAL,
SocialStudies REAL,
USHistory REAL,
Economics REAL,
Government REAL,
Mathematics REAL,
Science REAL,
ComputerScience REAL,
ELECTIVES REAL,
WorkCredits REAL
)

--this table will contain student credits and credit classifications
DECLARE @CreditsClassification TABLE(
seq INT IDENTITY(1,1),
PersonID INT,
ClassTitle VARCHAR(150),
EarnedCredits REAL,
ClassMeetsRequirements VARCHAR(150),
ClassMeetsOptionCode INT,
ClassMeetsRequirementsCode INT,
IsWorkCredit INT,
AgencyID INT
)

-- The table contains unlinked courses
DECLARE @UnlinkedCreditsClassification TABLE(
seq INT IDENTITY(1,1),
PersonID INT,
ClassTitle VARCHAR(150),
EarnedCredits REAL,
ClassMeetsRequirements VARCHAR(150),
ClassMeetsOptionCode INT,
ClassMeetsRequirementsCode INT,
IsWorkCredit INT,
AgencyID INT
)

INSERT @CreditsClassification
(PersonID, ClassTitle, EarnedCredits, ClassMeetsRequirements, ClassMeetsOptionCode, ClassMeetsRequirementsCode, IsWorkCredit, AgencyID)
SELECT
dbo.ClassRegistration.PersonID AS PersonID,
dbo.ClassSection.ClassSectionTitle AS ClassSectionTitle_Linked,
dbo.ClassRegistration.EarnedCredits,
dbo.OT_ClassMeetsRequirement.ShortDescription AS ClassMeetsRequirements,
dbo.OT_ClassMeetsRequirement.OptionCode AS ClassMeetsOptionCode,
dbo.ClassRegistration.ClassMeetsRequirementCode AS ClassMeetsRequirementsCode,
dbo.ClassRegistration.IsWorkCredit,
dbo.Tempagency.AgencyID
FROM
dbo.Student WITH (READUNCOMMITTED) JOIN
dbo.Person WITH (READUNCOMMITTED) ON dbo.Student.PersonID = dbo.Person.PersonID JOIN
dbo.ClassRegistration WITH (READUNCOMMITTED) ON dbo.Student.PersonID = dbo.ClassRegistration.PersonID JOIN
dbo.ClassSection WITH (READUNCOMMITTED) ON dbo.ClassRegistration.ClassSectionID = dbo.ClassSection.ClassSectionID LEFT OUTER JOIN
dbo.PersonIdentifier ON (dbo.Student.PersonID = dbo.PersonIdentifier.PersonID) LEFT OUTER JOIN
dbo.OT_RegistrationStatus WITH (READUNCOMMITTED) ON dbo.ClassRegistration.StatusCode = dbo.OT_RegistrationStatus.OptionCode
AND dbo.ClassRegistration.AgencyID = dbo.OT_RegistrationStatus.AgencyID LEFT OUTER JOIN
dbo.OT_ClassMeetsRequirement WITH (READUNCOMMITTED) ON dbo.ClassRegistration.ClassMeetsRequirementCode = dbo.OT_ClassMeetsRequirement.OptionCode
AND dbo.ClassRegistration.AgencyID = dbo.OT_ClassMeetsRequirement.AgencyID
JOIN dbo.TempAgency ON (dbo.Tempagency.AgencyID = dbo.Student.AgencyID)
JOIN dbo.ReportOnSelectionTempID WITH (READUNCOMMITTED) ON (dbo.ClassRegistration.PersonID = dbo.ReportOnSelectionTempID.ID)
WHERE dbo.PersonIdentifier.IdentificationCode = @ssn AND dbo.ClassRegistration.StatusCode = 103 --AND
and ClassRegistration.ClassMeetsRequirementCode != 2

--(ISNUMERIC(dbo.ClassRegistration.FinalLetterGrade) = 1
-- AND dbo.ClassRegistration.FinalLetterGrade < '100' OR dbo.ClassRegistration.FinalLetterGrade > '0')
ORDER BY dbo.Student.PersonID

INSERT @UnlinkedCreditsClassification
(PersonID, ClassTitle, EarnedCredits, ClassMeetsRequirements, ClassMeetsOptionCode, ClassMeetsRequirementsCode, IsWorkCredit, AgencyID)
SELECT
t.PersonID AS PersonID,
t.CourseName AS Unlined_CourseName,
t.EarnedCredits,
dbo.OT_ClassMeetsRequirement.ShortDescription AS ClassMeetsRequirements,
dbo.OT_ClassMeetsRequirement.OptionCode AS ClassMeetsOptionCode,
t.ClassMeetsRequirementCode AS ClassMeetsRequirementsCode,
t.IsWorkCredit,
dbo.TempAgency.AgencyID AS AgencyID
FROM
dbo.Student JOIN
dbo.TranscriptClassRegistration t ON (dbo.Student.PersonID = t.PersonID) LEFT OUTER JOIN
dbo.PersonIdentifier ON (dbo.Student.PersonID = dbo.PersonIdentifier.PersonID) LEFT OUTER JOIN
dbo.OT_RegistrationStatus ON (t.StatusCode = dbo.OT_RegistrationStatus.OptionCode
AND t.AgencyID = dbo.OT_RegistrationStatus.AgencyID) LEFT OUTER JOIN
dbo.OT_ClassMeetsRequirement ON (t.ClassMeetsRequirementCode = dbo.OT_ClassMeetsRequirement.OptionCode
AND dbo.Student.AgencyID = dbo.OT_ClassMeetsRequirement.AgencyID)
JOIN dbo.TempAgency ON (dbo.Tempagency.AgencyID = dbo.Student.AgencyID)
JOIN dbo.ReportOnSelectionTempID WITH (READUNCOMMITTED) ON (t.PersonID = dbo.ReportOnSelectionTempID.ID)
WHERE dbo.PersonIdentifier.IdentificationCode = @ssn AND t.StatusCode = 103
and t.ClassMeetsRequirementCode != 2
-- AND (ISNUMERIC(t.FinalLetterGrade) = 1 AND t.FinalLetterGrade < '100' OR t.FinalLetterGrade > '0')
ORDER BY dbo.Student.PersonID

--Union of Linked and Unlinked Classes
INSERT @AllocatedCredits
SELECT
PersonID,
SUM(CASE WHEN (ClassMeetsRequirements = 'Language Arts') THEN EarnedCredits ELSE 0.0 END) AS LanguageArts,
SUM(CASE WHEN (ClassMeetsRequirements = 'Social Studies') THEN EarnedCredits ELSE 0.0 END) AS SocialStudies,
SUM(CASE WHEN (ClassMeetsRequirements = 'US History') THEN EarnedCredits ELSE 0.0 END) AS USHistory,
SUM(CASE WHEN (ClassMeetsRequirements = 'Economics') THEN EarnedCredits ELSE 0.0 END) AS Economics,
SUM(CASE WHEN (ClassMeetsRequirements = 'Government') THEN EarnedCredits ELSE 0.0 END) AS Government,
SUM(CASE WHEN (ClassMeetsRequirements = 'Mathematics') THEN EarnedCredits ELSE 0.0 END) AS Mathematics,
SUM(CASE WHEN (ClassMeetsRequirements = 'Natural Science') THEN EarnedCredits ELSE 0.0 END) AS Science,
SUM(CASE WHEN (ClassMeetsRequirements IN ('Computer Science','ComputerScience', 'Keyboarding', 'Computer Applications')) THEN EarnedCredits ELSE 0.0 END) AS ComputerScience,
SUM(CASE WHEN (ClassMeetsRequirements NOT IN ('Language Arts', 'Social Studies', 'US History', 'Economics', 'Government', 'Mathematics',
'Natural Science', 'ComputerScience', 'Computer Applications', 'Keyboarding' ))
THEN EarnedCredits ELSE 0 END) AS Electives,
SUM(CASE WHEN (IsWorkCredit=1) THEN EarnedCredits ELSE 0.0 END) AS WorkCredits
FROM @CreditsClassification
GROUP BY PersonID
UNION
SELECT
PersonID,
SUM(CASE WHEN (ClassMeetsRequirements = 'Language Arts') THEN EarnedCredits ELSE 0.0 END) AS LanguageArts,
SUM(CASE WHEN (ClassMeetsRequirements = 'Social Studies') THEN EarnedCredits ELSE 0.0 END) AS SocialStudies,
SUM(CASE WHEN (ClassMeetsRequirements = 'US History') THEN EarnedCredits ELSE 0.0 END) AS USHistory,
SUM(CASE WHEN (ClassMeetsRequirements = 'Economics') THEN EarnedCredits ELSE 0.0 END) AS Economics,
SUM(CASE WHEN (ClassMeetsRequirements = 'Government') THEN EarnedCredits ELSE 0.0 END) AS Government,
SUM(CASE WHEN (ClassMeetsRequirements = 'Mathematics') THEN EarnedCredits ELSE 0.0 END) AS Mathematics,
SUM(CASE WHEN (ClassMeetsRequirements = 'Natural Science') THEN EarnedCredits ELSE 0.0 END) AS Science,
SUM(CASE WHEN (ClassMeetsRequirements IN ('Computer Science','ComputerScience', 'Keyboarding', 'Computer Applications')) THEN EarnedCredits ELSE 0.0 END) AS ComputerScience,
SUM(CASE WHEN (IsWorkCredit=0 AND (ClassMeetsRequirements NOT IN ('Language Arts', 'Social Studies', 'US History', 'Economics', 'Government', 'Mathematics',
'Natural Science', 'ComputerScience', 'Computer Applications', 'Keyboarding' )))
THEN EarnedCredits ELSE 0 END) AS Electives,
SUM(CASE WHEN (IsWorkCredit=1) THEN EarnedCredits ELSE 0.0 END) AS WorkCredits
FROM @UnlinkedCreditsClassification
GROUP BY PersonID

DECLARE @result AS REAL
--------Recalculate Language Arts Hours--------
SET @result = (SELECT SUM(LanguageArts) AS LanguageArts FROM @AllocatedCredits) - @LanguageArtsMinCred

IF (@result > 0)
BEGIN
UPDATE @AllocatedCredits
SET Electives = (SELECT SUM(Electives) FROM @AllocatedCredits) + @result, LanguageArts = @LanguageArtsMinCred
END

--------Recalculate SocialStudies Hours--------
SET @result = (SELECT SUM(SocialStudies) FROM @AllocatedCredits) - @SocialStudiesMinCred

IF (@result > 0)
BEGIN
UPDATE @AllocatedCredits
SET Electives = (SELECT SUM(Electives) FROM @AllocatedCredits) + @result, SocialStudies = @SocialStudiesMinCred
END

--------Recalculate Economics hours--------
SET @result = (SELECT SUM(Economics) FROM @AllocatedCredits) - @EconomicsMinCred

IF (@result > 0)
BEGIN
UPDATE @AllocatedCredits
SET Electives = (SELECT SUM(Electives) FROM @AllocatedCredits) + @result, Economics = @EconomicsMinCred
END

--------Recalculate Government Hours--------
SET @result = (SELECT SUM(Government) FROM @AllocatedCredits) - @GovernmentMinCred

IF (@result > 0)
BEGIN
UPDATE @AllocatedCredits
SET Electives = (SELECT SUM(Electives) FROM @AllocatedCredits) + @result, Government = @GovernmentMinCred
END

--------Recalculate Mathematics Hours--------
SET @result = (SELECT SUM(Mathematics) FROM @AllocatedCredits) - @MathematicsMinCred

IF (@result > 0)
BEGIN
UPDATE @AllocatedCredits
SET Electives = (SELECT SUM(Electives) FROM @AllocatedCredits) + @result, Mathematics = @MathematicsMinCred
END

--------Recalculate Science Hours--------
SET @result = (SELECT SUM(Science) FROM @AllocatedCredits) - @ScienceMinCred

IF (@result > 0)
BEGIN
UPDATE @AllocatedCredits
SET Electives = (SELECT SUM(Electives) FROM @AllocatedCredits) + @result, Science = @ScienceMinCred
END

--------Recalculate ComputerScience Hours--------
SET @result = (SELECT SUM(ComputerScience) FROM @AllocatedCredits) - @ComputerScienceMinCred

IF (@result > 0)
BEGIN
UPDATE @AllocatedCredits
SET Electives = (SELECT SUM(Electives) FROM @AllocatedCredits) + @result, ComputerScience = @ComputerScienceMinCred
END

--The above update statements for some reason add another row in the @AllocatedCredits table at the top
--this affects the calculations below since it duplicates some column values

--SELECT * from @AllocatedCredits
IF ((SELECT COUNT(*) FROM @AllocatedCredits ) > 1)
DELETE TOP (1) PERCENT FROM @AllocatedCredits

SELECT A.*, B.TotalCredits, dbo.fnGetAttemptedCredits(A.PersonID) AS TotalCredits_WithoutWC
FROM
(SELECT
PersonID,
SUM(LanguageArts) AS LanguageArts,
SUM(SocialStudies) AS SocialStudies,
SUM(USHistory) AS USHistory,
SUM(Economics) AS Economics,
SUM(Government) AS Government,
SUM(Mathematics) AS Mathematics,
SUM(Science) AS Science,
SUM(ComputerScience) AS ComputerScience,
SUM(Electives) AS Electives,
SUM(WorkCredits) AS WorkCredits
FROM @AllocatedCredits
GROUP BY PersonID) AS A
LEFT OUTER JOIN
(SELECT
PersonID,
SUM(LanguageArts + SocialStudies + USHistory + Economics +
Government + Mathematics + Science + ComputerScience +
Electives + WorkCredits) AS TotalCredits
FROM @AllocatedCredits
GROUP BY PersonID) AS B
ON A.PersonID = B.PersonID

/*
SELECT * FROM @AllocatedCredits
SELECT
SocialStudies - @SocialStudiesMinCred,
USHistory - @USHistoryMinCred,
Economics - @EconomicsMinCred,
Government - @GovernmentMinCred,
Mathematics - @MathematicsMinCred,
Science - @ScienceMinCred,
ComputerScience - @ComputerScienceMinCred

FROM @AllocatedCredits

DELETE ReportOnSelectionTempID
DELETE TempAgency

INSERT INTO ReportOnSelectionTempID (ID) VALUES (59918)
INSERT INTO TempAgency (AgencyID) VALUES (47596716)

Min credit values for these areas of study:
LanguageArts (4)
SocialStudies (1)
USHistory (1)
Economics (0.5)
Government (0.5)
Mathematics (4)
Science (3)
ComputerScience (1)
Electives
*/

Have at it.....
--Nick

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-05-24 : 16:33:54

I just did a quick scan, but it appears that you the UNION on the insert into @AllocatedCredits would give you 2 rows per person. Is that what is supposed to happen?

-Ryan

ramoneguru
Yak Posting Veteran

69 Posts

Posted - 2007-05-24 : 17:02:27

Yeah, you are correct Ryan. Its the update that messes up my calculations. Its updating both rows because I didn't specify which row to update (I thought only one row was in the table). Great, thanks again SQL Team!!

--Nick

ramoneguru
Yak Posting Veteran

69 Posts

Posted - 2007-05-24 : 17:22:18

Heads up for a quick fix (the boss needed this asap so no time to redo the thing which is what I wanted):

create a new variable table:
DECLARE @ReCalcAllocatedCredits TABLE (
PersonID INT,
LanguageArts REAL,
SocialStudies REAL,
USHistory REAL,
Economics REAL,
Government REAL,
Mathematics REAL,
Science REAL,
ComputerScience REAL,
ELECTIVES REAL,
WorkCredits REAL,
TotalCredits REAL,
TotalCredits_WithoutWC REAL
)

Then insert the sum of both rows into new table thusly:
INSERT @ReCalcAllocatedCredits
SELECT A.*, B.TotalCredits, dbo.fnGetAttemptedCredits(A.PersonID) AS TotalCredits_WithoutWC
FROM
(SELECT
PersonID,
SUM(LanguageArts) AS LanguageArts,
SUM(SocialStudies) AS SocialStudies,
SUM(USHistory) AS USHistory,
SUM(Economics) AS Economics,
SUM(Government) AS Government,
SUM(Mathematics) AS Mathematics,
SUM(Science) AS Science,
SUM(ComputerScience) AS ComputerScience,
SUM(Electives) AS Electives,
SUM(WorkCredits) AS WorkCredits
FROM @AllocatedCredits
GROUP BY PersonID) AS A
LEFT OUTER JOIN
(SELECT
PersonID,
SUM(LanguageArts + SocialStudies + USHistory + Economics +
Government + Mathematics + Science + ComputerScience +
Electives + WorkCredits) AS TotalCredits
FROM @AllocatedCredits
GROUP BY PersonID) AS B
ON A.PersonID = B.PersonID

Then do recalc:
DECLARE @result AS REAL
--------Recalculate Language Arts Hours--------
SET @result = (SELECT SUM(LanguageArts) AS LanguageArts FROM @AllocatedCredits) - @LanguageArtsMinCred

IF (@result > 0)
BEGIN
UPDATE @AllocatedCredits
SET Electives = (SELECT SUM(Electives) FROM @AllocatedCredits) + @result, LanguageArts = @LanguageArtsMinCred
END

--------Recalculate SocialStudies Hours--------
SET @result = (SELECT SUM(SocialStudies) FROM @AllocatedCredits) - @SocialStudiesMinCred

IF (@result > 0)
BEGIN
UPDATE @AllocatedCredits
SET Electives = (SELECT SUM(Electives) FROM @AllocatedCredits) + @result, SocialStudies = @SocialStudiesMinCred
END

--------Recalculate Economics hours--------
SET @result = (SELECT SUM(Economics) FROM @AllocatedCredits) - @EconomicsMinCred

IF (@result > 0)
BEGIN
UPDATE @AllocatedCredits
SET Electives = (SELECT SUM(Electives) FROM @AllocatedCredits) + @result, Economics = @EconomicsMinCred
END

--------Recalculate Government Hours--------
SET @result = (SELECT SUM(Government) FROM @AllocatedCredits) - @GovernmentMinCred

IF (@result > 0)
BEGIN
UPDATE @AllocatedCredits
SET Electives = (SELECT SUM(Electives) FROM @AllocatedCredits) + @result, Government = @GovernmentMinCred
END

--------Recalculate Mathematics Hours--------
SET @result = (SELECT SUM(Mathematics) FROM @AllocatedCredits) - @MathematicsMinCred

IF (@result > 0)
BEGIN
UPDATE @AllocatedCredits
SET Electives = (SELECT SUM(Electives) FROM @AllocatedCredits) + @result, Mathematics = @MathematicsMinCred
END

--------Recalculate Science Hours--------
SET @result = (SELECT SUM(Science) FROM @AllocatedCredits) - @ScienceMinCred

IF (@result > 0)
BEGIN
UPDATE @AllocatedCredits
SET Electives = (SELECT SUM(Electives) FROM @AllocatedCredits) + @result, Science = @ScienceMinCred
END

--------Recalculate ComputerScience Hours--------
SET @result = (SELECT SUM(ComputerScience) FROM @AllocatedCredits) - @ComputerScienceMinCred

IF (@result > 0)
BEGIN
UPDATE @AllocatedCredits
SET Electives = (SELECT SUM(Electives) FROM @AllocatedCredits) + @result, ComputerScience = @ComputerScienceMinCred
END

Then select * from recalc to match with what the report needs:
SELECT * FROM @ReCalcAllocatedCredits

Then rewrite entire sql statement this weekend so they don't yell at me during code review :-)

--Nick

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources