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.

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... %.

Open in new window