[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 244
  • Last Modified:

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.
0
ETFairfax
Asked:
ETFairfax
  • 3
  • 3
1 Solution
 
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
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

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

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now