Sql Calculation

CREATE TABLE [dbo].[Task]
(
      [id] [int] IDENTITY(1,1) NOT NULL,
      [name] [nvarchar](50) NOT NULL,
      [completetionStatus] [nvarchar](10) NOT NULL,
      CONSTRAINT [PK_Task] PRIMARY KEY CLUSTERED ([id] ASC)
      WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[SubTask]
(
      [id] [int] IDENTITY(1,1) NOT NULL,
      [taskId] [int] NOT NULL,
      [subTaskCompletionStatus] [int] NOT NULL,
      CONSTRAINT [PK_SubTask] PRIMARY KEY CLUSTERED ([id] ASC)
      WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
)
ON [PRIMARY]

ALTER TABLE [dbo].[SubTask]  WITH CHECK ADD  CONSTRAINT [FK_SubTask_Task] FOREIGN KEY([taskId])
REFERENCES [dbo].[Task] ([id])
ALTER TABLE [dbo].[SubTask] CHECK CONSTRAINT [FK_SubTask_Task]

INSERT INTO [Task] ([name], [completetionStatus]) VALUES ('Task 01', 'S');
INSERT INTO [SubTask] ([taskId], [subTaskCompletionStatus]) VALUES (1, 0);
INSERT INTO [SubTask] ([taskId], [subTaskCompletionStatus]) VALUES (1, 0);
INSERT INTO [SubTask] ([taskId], [subTaskCompletionStatus]) VALUES (1, 0);
INSERT INTO [SubTask] ([taskId], [subTaskCompletionStatus]) VALUES (1, 0);
INSERT INTO [SubTask] ([taskId], [subTaskCompletionStatus]) VALUES (1, 0);
INSERT INTO [SubTask] ([taskId], [subTaskCompletionStatus]) VALUES (1, 0);
INSERT INTO [SubTask] ([taskId], [subTaskCompletionStatus]) VALUES (1, 0);
INSERT INTO [SubTask] ([taskId], [subTaskCompletionStatus]) VALUES (1, 0);
INSERT INTO [SubTask] ([taskId], [subTaskCompletionStatus]) VALUES (1, 0);

INSERT INTO [Task] ([name], [completetionStatus]) VALUES ('Task 02', 'S');
INSERT INTO [SubTask] ([taskId], [subTaskCompletionStatus]) VALUES (2, 0);
INSERT INTO [SubTask] ([taskId], [subTaskCompletionStatus]) VALUES (2, 0);
INSERT INTO [SubTask] ([taskId], [subTaskCompletionStatus]) VALUES (2, 0);
INSERT INTO [SubTask] ([taskId], [subTaskCompletionStatus]) VALUES (2, 0);
INSERT INTO [SubTask] ([taskId], [subTaskCompletionStatus]) VALUES (2, 0);
INSERT INTO [SubTask] ([taskId], [subTaskCompletionStatus]) VALUES (2, 0);
INSERT INTO [SubTask] ([taskId], [subTaskCompletionStatus]) VALUES (2, 0);
INSERT INTO [SubTask] ([taskId], [subTaskCompletionStatus]) VALUES (2, 0);
INSERT INTO [SubTask] ([taskId], [subTaskCompletionStatus]) VALUES (2, 0);

Given the tables and data above (a simplified version of what actually exists), I need a stored procedure which, when passed a task Id, will update the [completetionStatus] of the [Task] table, with a value calculated from the values stored in the [SubTask] table.

Psuedo code for the calculation...

if (100% of subTaskCompletionStatus = 2)
{

      set Task table completetionStatus to 'CO'

}
else if (100% of subTaskCompletionStatus = 3)
{

      set Task table completetionStatus to 'CL'

}
else if (100% of subTaskCompletionStatus = 2 AND 3)
{

      set Task table completetionStatus to 'CL'

}
else if (>0% but <100% of subTaskCompletionStatus = 2 or 3)
{

      set Task table completetionStatus to 'PC'

}
else if(100% of subTaskCompletionStatus >= 4)
{

      set Task table completetionStatus to 'F'

}

Ultimatly I need a solution for SQL Server 2005, but it would be great if a solution for SQL 2000 could also be provided. I would also like to avoid the use of triggers.

This needs to be as slick as possible as it will be a routine being hit on a very regular basis.  Any solutions I come up with are either to "clunky" or I am not sure how to implement.

Let me know if you are unsure of what I am going on about!!

Many thanks for any help given.
ETFairfaxAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kevin CrossChief Technology OfficerCommented:
Hello ETFairfax,

Try this (see code snippet).

Usage:
EXEC dbo.updateTask 1

Hopefully that helps and is clear.

Regards,

mwvisa1
CREATE PROCEDURE dbo.updateTask
	-- Add the parameters for the stored procedure here
	@taskID INT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON; 
    -- Insert statements for procedure here
	UPDATE t
	SET t.[completetionStatus] =
	--SELECT [completetionStatus] =
		CASE 
			WHEN s.countOf2 = 100.0 THEN 'CO'
			-- since countOf3 = 100.0 is same status of 'CL' just consolidated
			-- as if countOf3 = 100.0 then countOf2 = 0.0, so countOf2 + countOf3 = 100.0 still true
			WHEN (s.countOf2 + s.countOf3) = 100.0 THEN 'CL'
			WHEN (s.countOf2 + s.countOf3) > 0.0 THEN 'PC'
			WHEN s.countOfGTE4 = 100.0 THEN 'F'
			ELSE t.[completetionStatus] /* left alone here, but could be any of cases above to fit your need */
		END
	FROM Task t 
	INNER JOIN (
		SELECT taskID
		, SUM(CASE WHEN subTaskCompletionStatus <= 1 THEN 1 ELSE 0 END) * 100.0/COUNT(id) As countOfLTE1
		, SUM(CASE WHEN subTaskCompletionStatus = 2 THEN 1 ELSE 0 END) * 100.0/COUNT(id) As countOf2
		, SUM(CASE WHEN subTaskCompletionStatus = 3 THEN 1 ELSE 0 END) * 100.0/COUNT(id) As countOf3
		, SUM(CASE WHEN subTaskCompletionStatus >= 4 THEN 1 ELSE 0 END) * 100.0/COUNT(id) As countOfGTE4
		FROM SubTask 
		GROUP BY taskID
	) s ON t.id = s.taskId
	WHERE t.id = @taskID
END
GO

Open in new window

0
ETFairfaxAuthor Commented:
Hi mwvisa1

Thanks for the quick reply.  I can't test your solution right now, but as soon as I have I will let you know how it has gone.

Thanks

ETFairFax
0
ETFairfaxAuthor Commented:
mwvisa1

This looks like it will do just the trick, but needs a little honing.

Running the Sql in the code snippet below results in a task a task.completionStatus of 'PC' where it should be 'CL'.

I think this is down to an issue with rounding in the...

SELECT taskID
, SUM(CASE WHEN subTaskCompletionStatus <= 1 THEN 1 ELSE 0 END) * 100.0 / COUNT(id) As countOfLTE1
, SUM(CASE WHEN subTaskCompletionStatus = 2 THEN 1 ELSE 0 END) * 100.0 / COUNT(id) As countOf2
, SUM(CASE WHEN subTaskCompletionStatus = 3 THEN 1 ELSE 0 END) * 100.0 / COUNT(id) As countOf3
, SUM(CASE WHEN subTaskCompletionStatus >= 4 THEN 1 ELSE 0 END) * 100.0 / COUNT(id) As countOfGTE4
FROM SubTask
GROUP BY taskID

... part of the procedure.  I could be wrong, but from my tests so far I am getting a load of 99.999999999999 results instead of 100.00.

Can you please provide a fix for that to?

I must say I am glad I signed up to EE.  This is my first post and am impressed with the reponse.

Many thanks,

ETFairfax
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_SubTask_Task]') AND parent_object_id = OBJECT_ID(N'[dbo].[SubTask]'))
ALTER TABLE [dbo].[SubTask] DROP CONSTRAINT [FK_SubTask_Task]
 
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SubTask]') AND type in (N'U'))
DROP TABLE [dbo].[SubTask]
 
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Task]') AND type in (N'U'))
DROP TABLE [dbo].[Task]
 
CREATE TABLE [dbo].[Task]
(
      [id] [int] IDENTITY(1,1) NOT NULL,
      [name] [nvarchar](50) NOT NULL,
      [completetionStatus] [nvarchar](10) NOT NULL,
      CONSTRAINT [PK_Task] PRIMARY KEY CLUSTERED ([id] ASC)
      WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
 
CREATE TABLE [dbo].[SubTask]
(
      [id] [int] IDENTITY(1,1) NOT NULL,
      [taskId] [int] NOT NULL,
      [subTaskCompletionStatus] [int] NOT NULL,
      CONSTRAINT [PK_SubTask] PRIMARY KEY CLUSTERED ([id] ASC)
      WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
)
ON [PRIMARY]
 
ALTER TABLE [dbo].[SubTask]  WITH CHECK ADD  CONSTRAINT [FK_SubTask_Task] FOREIGN KEY([taskId])
REFERENCES [dbo].[Task] ([id])
 
ALTER TABLE [dbo].[SubTask] CHECK CONSTRAINT [FK_SubTask_Task]
 
INSERT INTO [Task] ([name], [completetionStatus]) VALUES ('Task 01', 'S');
INSERT INTO [SubTask] ([taskId], [subTaskCompletionStatus]) VALUES (1, 2);
INSERT INTO [SubTask] ([taskId], [subTaskCompletionStatus]) VALUES (1, 2);
INSERT INTO [SubTask] ([taskId], [subTaskCompletionStatus]) VALUES (1, 2);
INSERT INTO [SubTask] ([taskId], [subTaskCompletionStatus]) VALUES (1, 2);
INSERT INTO [SubTask] ([taskId], [subTaskCompletionStatus]) VALUES (1, 2);
INSERT INTO [SubTask] ([taskId], [subTaskCompletionStatus]) VALUES (1, 3);
INSERT INTO [SubTask] ([taskId], [subTaskCompletionStatus]) VALUES (1, 3);
INSERT INTO [SubTask] ([taskId], [subTaskCompletionStatus]) VALUES (1, 3);
INSERT INTO [SubTask] ([taskId], [subTaskCompletionStatus]) VALUES (1, 3);
 
EXEC UpdateTask 1
 
SELECT * FROM Task 

Open in new window

0
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

Kevin CrossChief Technology OfficerCommented:
Yes, sorry about that.  I should have caught that.

The problem is the precision of the values.  I see now that count of 2's can be 77.7777777777 and count of 3's can be 22.2222222222 which is 100% of all the values but you don't get right answer as that doesn't technically equal 100.0.

There are a number of options.  One is to ROUND(77.7777777777, 4) which will round the first number to the second number of decimal places OR cast to a datatype that uses lower digits of precision like MONEY in this case which will round to 4 decimal places.  I like using that with percentages as you get these kinds of numbers: 15.1625.

This should work for you.  Tested with data like above of 77.7... and 22.2... %.
ALTER PROCEDURE dbo.updateTask
	-- Add the parameters for the stored procedure here
	@taskID INT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON; 
    -- Insert statements for procedure here
	UPDATE t
	SET t.[completetionStatus] =
	--SELECT [completetionStatus] =
		CASE 
			WHEN s.pctOf2 = 100.0 THEN 'CO'
			-- since pctOf3 = 100.0 is same status of 'CL' just consolidated
			-- as if pctOf3 = 100.0 then pctOf2 = 0.0, so pctOf2 + pctOf3 = 100.0 still true
			WHEN (s.pctOf2 + s.pctOf3) = 100.0 THEN 'CL'
			WHEN (s.pctOf2 + s.pctOf3) > 0.0 THEN 'PC'
			WHEN s.pctOfGTE4 = 100.0 THEN 'F'
			ELSE t.[completetionStatus] /* left alone here, but could be any of cases above to fit your need */
		END
	FROM Task t 
	INNER JOIN (
		SELECT taskID
		, CAST(SUM(CASE WHEN subTaskCompletionStatus <= 1 THEN 1 ELSE 0 END) * 100.0/COUNT(id) AS MONEY) As pctOfLTE1
		, CAST(SUM(CASE WHEN subTaskCompletionStatus = 2 THEN 1 ELSE 0 END) * 100.0/COUNT(id) AS MONEY) As pctOf2
		, CAST(SUM(CASE WHEN subTaskCompletionStatus = 3 THEN 1 ELSE 0 END) * 100.0/COUNT(id) AS MONEY) As pctOf3
		, CAST(SUM(CASE WHEN subTaskCompletionStatus >= 4 THEN 1 ELSE 0 END) * 100.0/COUNT(id) AS MONEY) As pctOfGTE4
		FROM SubTask 
		GROUP BY taskID
	) s ON t.id = s.taskId
	WHERE t.id = @taskID
END
GO

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Kevin CrossChief Technology OfficerCommented:
And you are most welcome and glad to have you here at EE.
0
ETFairfaxAuthor Commented:
Nice one, your solution worked a treat.  Thanks very much.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.