Help Looping While statement - UPDATED & apologies

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)

Help Looping While statement - UPDATED & apologies

Author

Topic

Buzzard724
Yak Posting Veteran

66 Posts

Posted - 2009-08-11 : 18:50:36

APOLOGIES to anyone who looked at this earlier - it was late night and sloppy - now updated to fully demonstrate the issue - hopefully without SQL errors except in the Function

I am trying to return a single value calculated from different subsets and using a while loop to total one of the subsets - any help much appreciated
Code to reproduce is as follows:

Database, table and record creation
CREATE TABLE [dbo].[PEOPLE](
[PEOPLE_ID] [uniqueidentifier] NOT NULL,
[LASTNAME] [nvarchar](30) NULL,
[FIRSTNAME] [nvarchar](30) NULL,
[REFERENCENUMBER] [nvarchar](30) NULL,
[FULLNAME] AS ([FIRSTNAME] + ' ' + [LASTNAME]),
CONSTRAINT [PK_PEOPLE] PRIMARY KEY CLUSTERED
(
[PEOPLE_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY],
CONSTRAINT [IX_PEOPLE_UNIQUE] UNIQUE NONCLUSTERED
(
[REFERENCENUMBER] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[JOBDETAIL](
[JOBDETAIL_ID] [uniqueidentifier] NOT NULL,
[JOBTITLE] [uniqueidentifier] NULL,
[PEOPLE_ID] [uniqueidentifier] NOT NULL,
[CREATEDBY] [nvarchar](20) NOT NULL,
[CURRENTRECORD] [nvarchar](5) NOT NULL,
[WORKINGHOURSPERYEAR] [INT] NOT NULL,
CONSTRAINT [PK_JOBDETAIL] PRIMARY KEY NONCLUSTERED
(
[JOBDETAIL_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[JOBDETAIL] WITH NOCHECK ADD CONSTRAINT [FK_JOBDETAIL_PEOPLE] FOREIGN KEY([PEOPLE_ID])
REFERENCES [dbo].[PEOPLE] ([PEOPLE_ID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[JOBDETAIL] CHECK CONSTRAINT [FK_JOBDETAIL_PEOPLE]

CREATE TABLE [dbo].[POST](
[POST_ID] [uniqueidentifier] NOT NULL,
[PERCENTAGETEACHING] [int] NOT NULL,
[REFERENCENUMBER] [int] NOT NULL,
CONSTRAINT [PK_POST] PRIMARY KEY CLUSTERED
(
[POST_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY],
CONSTRAINT [IX_POST_UNIQUE] UNIQUE NONCLUSTERED
(
[REFERENCENUMBER] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF

INSERT RECORDS:
--Create one Person Record
Insert into People (People_id, LastName, FirstName)
VALUES (newid(), 'Peter', 'Charles')
--Select * from People
--Create 2 post records
Insert into Post (Post_ID, PERCENTAGETEACHING, REFERENCENUMBER)
VALUES (newid(), 40, 1001)
Insert into Post (Post_ID, PERCENTAGETEACHING, REFERENCENUMBER)
VALUES (newid(), 20, 1002)
--select * from Post
Insert into JobDetail (JobDetail_id, JobTitle, CreatedBy, CurrentRecord, WorkingHoursPerYear)
Select top 1 newid(), Po.Post_ID, 'Peter', 'Yes', 100
from Post PO where PO.PERCENTAGETEACHING = 40
--Create 2 Job records - both for the same person but for different posts
Insert into JobDetail (JobDetail_id, JobTitle, CreatedBy, CurrentRecord, WorkingHoursPerYear, People_id)
Select newid(), Po.Post_ID, 'Peter', 'Yes', 100, (select top 1 People_id from People)
from Post PO
--Select * from jobdetail

--CREATE Function
USE [TEST1]
GO
/****** Object: UserDefinedFunction [dbo].[RMET_PercentageTeaching] Script Date: 08/11/2009 20:55:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[RMET_PercentageTeaching]
(
@Id uniqueidentifier
)
RETURNS INTEGER
AS
BEGIN
DECLARE @CountJobs INTEGER
SET @CountJobs = 0
DECLARE @TotalWHPY INTEGER
SET @TotalWHPY = 0
DECLARE @Counter Integer
SET @Counter = 0
DECLARE @PCTeaching INTEGER
SET @PCTeaching = 0
DECLARE @RESULT INTEGER
SET @RESULT = 0
--Count of Jobs where PercentageTeaching > 0 - used by the While loop
Select @CountJobs = Count(J.CreatedBy) FROM JobDetail J
INNER JOIN POST PO ON PO.Post_ID = J.JobTitle
Where J.People_ID = @id AND J.CurrentRecord = 'Yes' AND PO.PercentageTeaching > 0
-- following subset required in order to obtain total hours for ALL jobs
SELECT @TotalWHPY = (Select SUM(J2.WorkingHoursPerYear) from JobDetail J2
WHERE J2.People_id = J.People_ID and J2.CurrentRecord = 'Yes'
Group By J2.People_id) FROM JobDetail J
INNER JOIN POST PO ON PO.Post_ID = J.JobTitle
Where J.People_ID = @id AND J.CurrentRecord = 'Yes'
--The following set statement to show that @CountJobs and @TotalWHPY are working
--SET @RESULT = @CountJobs + @TotalWHPY
--This is what we really want
--Now loop through the teaching jobs and total their hours and percentages
--using the values from @countJobs and @TotalWHPY
SELECT @RESULT = WHILE @counter <= @CountJobs
BEGIN
SET @counter = @counter + 1
SET @PCTeaching = @PCTeaching +
(PO.PercentageTeaching * (J.WorkingHoursPerYear/@TotalWHPY))
from JobDetail J
INNER JOIN POST PO ON PO.Post_ID = J.JobTitle
Where J.People_ID = @id AND J.CurrentRecord = 'Yes' AND PO.PercentageTeaching > 0
END
RETURN @Result
END
--Select statement to show the result
--select p.fullname, dbo.RMET_PercentageTeaching(people_id) from people p
-- Expected Result Peter Charles 30

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-08-11 : 19:03:33

That query is very broken. Can you provide some sample data and expected ouput? This link will help you prepare the data for us to help you:
[url]http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx[/url]

Buzzard724
Yak Posting Veteran

66 Posts

Posted - 2009-08-11 : 19:56:10

Thank you for the pointers

please see original post for updated code

again thank you

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-08-12 : 06:08:32

I might be missing something but I think your WHILE part can actually be rewritten like this

SELECT
	@PCTeaching = SUM(PO.PercentageTeaching * (J.WorkingHoursPerYear/@TotalWHPY))
FROM
	JobDetail J
	INNER JOIN POST PO ON PO.Post_ID = J.JobTitle
WHERE
	J.People_ID = @id
	AND J.CurrentRecord = 'Yes'
	AND PO.PercentageTeaching > 0

RETURN @PCTeaching

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Buzzard724
Yak Posting Veteran

66 Posts

Posted - 2009-08-12 : 06:11:20

thank you - I will try it out and let you know ...

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-08-12 : 06:20:04

You are aware that you are returning an integer?

for a percentage like this you will be loosing some precision.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Buzzard724
Yak Posting Veteran

66 Posts

Posted - 2009-08-12 : 06:21:04

Sorry - I should explain the reason for the loop. The person has 2 active jobs and I need to carry out the calculation on each job and then add the results together e.g. in the records I put in the original post

The post TeachingPercentage are 40 for one JobDetail record and 20 for the other
The JobDetail WorkingHoursPerYear records for this one person are both 100 hours - so a total of 200 hours in the year

So what I want to achieve is

40 * 100/200
+
20 * 100/200

result 30

OR in an example with different hours

40 * 100/200
20 * 300/400

result 35

thanks for you help

Buzzard724
Yak Posting Veteran

66 Posts

Posted - 2009-08-12 : 06:22:06

about precision - yes - I am happy to lose the accuracy

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-08-12 : 06:23:28

In fact -- you are doing integer division throughout.

Try these select statements to see if that's what you really really want.


DECLARE @intA INTEGER                  SET @intA = 59
DECLARE @intB INTEGER                  SET @intB = 4

-- This == 14 (which is the FLOOR of 14.75)
SELECT @intA / @intB

DECLARE @floatA FLOAT                  SET @floatA = 59
DECLARE @floatB FLOAT                  SET @floatB = 4

-- THis == 14.75
SELECT @floatA / @floatB

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Buzzard724
Yak Posting Veteran

66 Posts

Posted - 2009-08-12 : 06:38:35

Thank you Charlie - I think you are right - I will do the calculation as a Float and then round to whole numbers at the end

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-08-12 : 06:40:50

Yes -- the problem is here:


PO.PercentageTeaching * (J.WorkingHoursPerYear/@TotalWHPY)

J.WorkingHoursPerYear/@TotalWHPY

in your data is this:

100 / 300

which equals 0

With that changed to a float (or a decimal or whatever) you should be good. The SUM method should work.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Buzzard724
Yak Posting Veteran

66 Posts

Posted - 2009-08-12 : 06:58:58

OK ... so we need the float - and I think we also need the While Loop e.g.

Declare @PC Float Set @PC = 40
Declare @PC2 Float Set @PC2 = 20
Declare @WorkingHours Float Set @WorkingHours = 100
Declare @WorkingHours2 Float Set @WorkingHours2 = 300
Declare @TotalWorkingHours Float Set @TotalWorkingHours = 400

--the equivalent of a loop
Select (@PC * (@WorkingHours / @TotalWorkingHours))
+ (@PC2 * (@WorkingHours2 / @TotalWorkingHours))
-- Returns 25

--just using totals across the two records
Select ((@PC+@PC2) * (@WorkingHours+@WorkingHours2)) / @TotalWorkingHours
--Returns 60

Buzzard724
Yak Posting Veteran

66 Posts

Posted - 2009-08-12 : 07:04:49

thank you Charlie - So if we add in the float and the while loop I have error messages about the while loop - function and error messages below
BEST VERSION of the function
USE [TEST1]
GO
/****** Object: UserDefinedFunction [dbo].[RMET_PercentageTeaching] Script Date: 08/11/2009 20:55:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[RMET_PercentageTeaching]
(
@Id uniqueidentifier
)
RETURNS INTEGER
AS
BEGIN
DECLARE @CountJobs INTEGER
SET @CountJobs = 0
DECLARE @TotalWHPY INTEGER
SET @TotalWHPY = 0
DECLARE @Counter Integer
SET @Counter = 0
DECLARE @PCTeaching Float
SET @PCTeaching = 0
DECLARE @RESULT Float
SET @RESULT = 0
--Count of Jobs where PercentageTeaching > 0 - used by the While loop
Select @CountJobs = Count(J.CreatedBy) FROM JobDetail J
INNER JOIN POST PO ON PO.Post_ID = J.JobTitle
Where J.People_ID = @id AND J.CurrentRecord = 'Yes' AND PO.PercentageTeaching > 0
-- following subset required in order to obtain total hours for ALL jobs
SELECT @TotalWHPY = (Select SUM(J2.WorkingHoursPerYear) from JobDetail J2
WHERE J2.People_id = J.People_ID and J2.CurrentRecord = 'Yes'
Group By J2.People_id) FROM JobDetail J
INNER JOIN POST PO ON PO.Post_ID = J.JobTitle
Where J.People_ID = @id AND J.CurrentRecord = 'Yes'
--The following set statement to show that @CountJobs and @TotalWHPY are working
--SET @RESULT = @CountJobs + @TotalWHPY
--This is what we really want
--Now loop through the teaching jobs and total their hours and percentages
--using the values from @countJobs and @TotalWHPY
SELECT @RESULT = WHILE @counter <= @CountJobs
BEGIN
SET @counter = @counter + 1
SET @PCTeaching = @PCTeaching +
(PO.PercentageTeaching * (J.WorkingHoursPerYear/@TotalWHPY))
from JobDetail J
INNER JOIN POST PO ON PO.Post_ID = J.JobTitle
Where J.People_ID = @id AND J.CurrentRecord = 'Yes' AND PO.PercentageTeaching > 0
END
RETURN @Result
END
--Select statement to show the result
--select p.fullname, dbo.RMET_PercentageTeaching(people_id) from people p
-- Expected Result Peter Charles 30

RETURNS THESE ERROR MESSAGES
Msg 156, Level 15, State 1, Procedure RMET_PercentageTeaching, Line 33
Incorrect syntax near the keyword 'WHILE'.
Msg 156, Level 15, State 1, Procedure RMET_PercentageTeaching, Line 38
Incorrect syntax near the keyword 'from'.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-08-12 : 10:19:03

I think you are making this way more complicated than it needs to be:

Does this do the job:


USE [TEST1]
GO
/****** Object: UserDefinedFunction [dbo].[RMET_PercentageTeaching] Script Date: 08/11/2009 20:55:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[RMET_PercentageTeaching]
(
@Id UNIQUEIDENTIFIER
)
RETURNS FLOAT
AS
BEGIN

	DECLARE @TotalWHPY FLOAT               SET @TotalWHPY = 0 
	DECLARE @PCTeaching FLOAT              SET @PCTeaching = 0

	-- following subset required in order to obtain total hours for ALL jobs
	SELECT
		@TotalWHPY = SUM(J.WorkingHoursPerYear)
	FROM
		JobDetail J
		INNER JOIN POST PO ON PO.Post_ID = J.JobTitle
	WHERE
		J.People_ID = @id
		AND J.CurrentRecord = 'Yes'

	SELECT
		@PCTeaching = SUM(PO.PercentageTeaching * (J.WorkingHoursPerYear/@TotalWHPY))
	FROM
		JobDetail J
		INNER JOIN POST PO ON PO.Post_ID = J.JobTitle
	WHERE
		J.People_ID = @id
		AND J.CurrentRecord = 'Yes'
		AND PO.PercentageTeaching > 0

	RETURN @PCTeaching
END

NB -- I've changed the return type to FLOAT -- that seemed to make sense. You were still trying to use integer division before.

If you don't want a FLOAT returned then you can ROUND or CAST the result to an INT and then return that instead.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Buzzard724
Yak Posting Veteran

66 Posts

Posted - 2009-08-12 : 10:58:16

Hi Charlie - thank you for persisting with this
I really appreciate code that is elegant and does the job with minimum effort - precisely what your suggestion does - thank you
(not quite sure why I was hooked on the loop - I think I assumed that the SUM would happen on the individual elements of the calculation - clearly a blind alley)
(and I will Round the result - thank you)

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-08-12 : 11:25:20

NP. Glad I could help.

CYA

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources