asked on

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.

Kevin Cross

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

ETFairfax

ASKER

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

ETFairfax

ASKER

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

ASKER CERTIFIED SOLUTION

Kevin Cross

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Kevin Cross

And you are most welcome and glad to have you here at EE.

ETFairfax

ASKER

Nice one, your solution worked a treat. Thanks very much.