August 3, 2005 at 11:15 am
I have a table as such:
EmpID WeekID Result
1 1 1
1 2 1
1 3 0
1 4 1
2 1 1 etc...
What I need to do is update all records prior to the 'zero' entry for each EmpID. By prior, I mean weeks with an id less than the greatest 'zero' entry for that employee. So in the above example weeks 1 and 2 would be set to 0.
I have a working stored procedure, but it runs over 2 minutes, and the table only has 27k rows!!
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgAugust 3, 2005 at 11:30 am
Can U post a sample create table script, some sample data, and the update procedure your using, and what you want the end result to be, and I'm sure someone can help,
Your description is very difficult to understand.
August 3, 2005 at 12:03 pm
TABLE SCRIPT
-------------------------------------------------------------------------------------
CREATE TABLE [dbo].[tblRTPs] (
[RtpID] [int] IDENTITY (1, 1) NOT NULL ,
[PersonID] [int] NOT NULL ,
[WeekID] [int] NOT NULL ,
[PointsEarned] [int] NOT NULL
) ON [PRIMARY]
GO
-------------------------------------------------------------------------------------
MY WORKING SP
-------------------------------------------------------------------------------------
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER PROCEDURE AdjustPointsAwardedAccordingToFailures
AS
SET NOCOUNT ON
DECLARE @EmpID int,
@WeekID int
-- Grab any employees with Failures
DECLARE EmployeeCursor CURSOR FOR
SELECT DISTINCT PersonID
FROM tblRTPs
WHERE PointsEarned = 0
ORDER BY PersonID ASC
OPEN EmployeeCursor
FETCH NEXT FROM EmployeeCursor INTO @EmpID
WHILE @@FETCH_STATUS = 0
BEGIN
-- Grab the latest week with a failure
SELECT @WeekID = MAX(WeekID)
FROM tblRTPs
WHERE PointsEarned = 0 AND PersonID = @EmpID AND WeekID > 1
-- Update the pointsEarned to 0 for all weeks prior,
-- this way when a sum is done on the table, the values are correct
UPDATE tblRTPs
SET PointsEarned = 0
WHERE PersonID = @EmpID AND WeekID <= @WeekID AND PointsEarned = 1
FETCH NEXT FROM EmployeeCursor INTO @EmpID
END
DEALLOCATE EmployeeCursor
SET NOCOUNT OFF
RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-------------------------------------------------------------------------------------
THE BASIC NEED:
The table "tblRTPs" will have one entry for each PersonID for each WeekID (the weekID's are in ascending order, so weekID 4 is the week after 3).
What the procedure does is grab all PersonIDs that have a '0' in their group of weeks. Then using this list it updates all weeks prior to (or with an ID less than) the latest week (or MAX weekID) that has a zero, to a value of zero.
To put it another way, each week you get a 1 for passing or 0 for failing.
I need to retrieve the number of consectutive passes to date.
Therefore if your records were:
WeekID 1 2 3 4 5 6 7 8
PointsEarned 1 1 1 1 0 1 1 1
Then the number retrieved would be 3.
I may be going about this wrong by updating all of the records rather than doing some sort of count, but this is the only way I could think of.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgAugust 3, 2005 at 1:18 pm
Someone will probably be able to come up with a better way to handle those PersonID's that do not have a zero, but here is my crack at it. This does not require an update or a Cursor. I tried to include data with multiple zero's (PersonID = 4) and data with no zero's (PersonID = 2).
CREATE TABLE #tblRTPs(
RtpID integer IDENTITY (1, 1) NOT NULL ,
PersonID integer NOT NULL ,
WeekID integer NOT NULL ,
PointsEarned integer NOT NULL)
INSERT INTO #tblRTPs( PersonID, WeekID, PointsEarned) VALUES( 1, 1, 1)
INSERT INTO #tblRTPs( PersonID, WeekID, PointsEarned) VALUES( 1, 2, 1)
INSERT INTO #tblRTPs( PersonID, WeekID, PointsEarned) VALUES( 1, 3, 0)
INSERT INTO #tblRTPs( PersonID, WeekID, PointsEarned) VALUES( 1, 4, 1)
INSERT INTO #tblRTPs( PersonID, WeekID, PointsEarned) VALUES( 2, 1, 1)
INSERT INTO #tblRTPs( PersonID, WeekID, PointsEarned) VALUES( 2, 2, 1)
INSERT INTO #tblRTPs( PersonID, WeekID, PointsEarned) VALUES( 2, 3, 1)
INSERT INTO #tblRTPs( PersonID, WeekID, PointsEarned) VALUES( 2, 4, 1)
INSERT INTO #tblRTPs( PersonID, WeekID, PointsEarned) VALUES( 3, 1, 1)
INSERT INTO #tblRTPs( PersonID, WeekID, PointsEarned) VALUES( 3, 2, 0)
INSERT INTO #tblRTPs( PersonID, WeekID, PointsEarned) VALUES( 3, 3, 1)
INSERT INTO #tblRTPs( PersonID, WeekID, PointsEarned) VALUES( 3, 4, 1)
INSERT INTO #tblRTPs( PersonID, WeekID, PointsEarned) VALUES( 4, 1, 1)
INSERT INTO #tblRTPs( PersonID, WeekID, PointsEarned) VALUES( 4, 2, 1)
INSERT INTO #tblRTPs( PersonID, WeekID, PointsEarned) VALUES( 4, 3, 0)
INSERT INTO #tblRTPs( PersonID, WeekID, PointsEarned) VALUES( 4, 4, 1)
INSERT INTO #tblRTPs( PersonID, WeekID, PointsEarned) VALUES( 4, 5, 0)
INSERT INTO #tblRTPs( PersonID, WeekID, PointsEarned) VALUES( 4, 6, 1)
INSERT INTO #tblRTPs( PersonID, WeekID, PointsEarned) VALUES( 4, 7, 1)
INSERT INTO #tblRTPs( PersonID, WeekID, PointsEarned) VALUES( 4, 8, 1)
SELECT #tblRTPs.PersonID, SUM( #tblRTPs.PointsEarned) AS TotalPointEarned
INTO #OutPut
FROM #tblRTPs
JOIN( SELECT PersonID, MAX( RtpID) AS RtpID, MAX( WeekID) AS MinWeekID
FROM #tblRTPs WHERE PointsEarned = 0 GROUP BY PersonID) RTP
ON( #tblRTPs.PersonID = RTP.PersonID AND #tblRTPs.WeekID > RTP.MinWeekID)
GROUP BY #tblRTPs.PersonID
INSERT INTO #OutPut
SELECT #tblRTPs.PersonID, SUM( #tblRTPs.PointsEarned) AS TotalPointEarned
FROM #tblRTPs
WHERE #tblRTPs.PersonID NOT IN( SELECT #OutPut.PersonID FROM #OutPut)
GROUP BY #tblRTPs.PersonID
SELECT * FROM #OutPut
DROP TABLE #tblRTPs
DROP TABLE #OutPut
I wasn't born stupid - I had to study.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply