June 16, 2019 at 4:07 pm
Best practices for comparing todo tasks to completed tasks when the todo tasks changes over time
I have a situation where I am required to compare a list of todo items to a list of done items and return 'Completed' when all of the todo items are complete. When there are pending todo items, 'Open' is returned. The todo items can change over time and I'd like to know what the best practices are for programming this type of situation?
The Todo Table has the following structure:
[LutTaskCd] [int] IDENTITY(1,1) NOT NULL,
[RequestType] [nvarchar](50) NULL,
[Role] [nvarchar](50) NULL,
[TaskName] [varchar](500) NULL,
[ParentTaskCd] [int] NULL,
[Description] [nvarchar](500) NULL,
[FlgDeleted] [bit] NULL,
[CreatedBy] [int] NULL,
[CreatedOn] [datetime] NULL,
[ModifiedBy] [int] NULL,
[ModifiedOn] [datetime] NULL,
[IsHidden] [bit] NULL,
[TaskProperty] [nvarchar](200) NULL,
[TaskOrder] [int] NULL,
The Completed tasks table has the following structure:?
The Completed tasks table has the following structure:
[TaskID] [int] IDENTITY(1,1) NOT NULL,
[CaseID] [int] NULL,
[PositionUpdateID] [int] NULL,
[LutTaskCd] [int] NULL,
[Description] [nvarchar](500) NULL,
[Comment] [nvarchar](max) NULL,
[CreatedBy] [int] NULL,
[CreatedOn] [datetime] NULL,
[ModifiedBy] [int] NULL,
[ModifiedOn] [datetime] NULL,
[Role] [nvarchar](50) NULL,
Scenario describing how the Todo tasks may change over time.?
Scenario describing how the Todo tasks may change over time.
Yesterday | Today | Tomorrow
Printer | |
Email | Email | Email
UserID | UserID | UserID
UserName | UserName | Username
| | Computer Set up
Requirement :
When completed tasks match the todo tasks, return 'Completed'. Otherwise, return 'open'
I have 4 cases (instances like a situation) in various stages of Todo list completion :
Scenario describing a case that has been completed yesterday:
1. The Printer, Email, UserID, UserName have been completed and the case is closed.
Scenario describing a case that was not completed yesterday:
2. The Printer, Email, UserID have been completed; the UserName is still pending
Scenario describing a case that is not yet complete Today:
3. The Email has been completed.
Scenario describing a case that will require checklist completion Tomorrow:
4. No tasks have been completed.
What are the most efficient ways in sql to accomplish the requirement?
June 16, 2019 at 4:14 pm
Well done on providing the DDL for the table.
Would you also be able to provide some sample data, along with desired results, based on that sample data?
The sample data should be in the form of INSERT statements.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
June 17, 2019 at 9:51 pm
Hi All,
Thank you very much! These are my insert statements:
_________________________________________________
INSERT STATEMENTS FOR THE LOOK UP TABLE (LutTask)
-- insert LUTtasks that existed yesterday.
INSERT INTO [HRA].[LutTask]
([RequestType] ,[Role] ,[TaskName] ,[ParentTaskCd] ,[Description] ,[FlgDeleted] ,[CreatedBy] ,[CreatedOn] ,[ModifiedBy] ,[ModifiedOn] ,[IsHidden] ,[TaskProperty] ,[TaskOrder])
VALUES
('Add New Employee - New Employee' ,'Systems' ,'PRINTER – Grant local printer access' ,0 ,null ,null ,1 ,GetDate() -1 ,null ,null ,null ,null ,1),
('Add New Employee - New Employee' ,'Systems' ,'GMAIL ACCOUNT add the value to comme' ,0 ,null ,null ,1 ,GetDate() -1 ,null ,null ,null ,null ,1),
('Add New Employee - New Employee' ,'Systems' ,'UserId - Create, ensure correct' ,0 ,null ,null ,1 ,GetDate() -1 ,null ,null ,null ,null ,1),
('Add New Employee - New Employee' ,'Systems' ,'UserName - Create, ensure correct' ,0 ,null ,null ,1 ,GetDate() -1 ,null ,null ,null ,null ,1)
GO
-- insert / deleted tasks that exist today ie the printer task is removed today.
delete hra.luttask where taskname like 'PRINTER – Grant local printer access'
-- insert new task that exists in the future, like 'computer set up'
INSERT INTO [HRA].[LutTask]
([RequestType] ,[Role] ,[TaskName] ,[ParentTaskCd] ,[Description] ,[FlgDeleted] ,[CreatedBy] ,[CreatedOn] ,[ModifiedBy] ,[ModifiedOn] ,[IsHidden] ,[TaskProperty] ,[TaskOrder])
VALUES
('Add New Employee - New Employee' ,'Systems' ,'Computer set up for someone' ,0 ,null ,null ,1 ,GetDate() -1 ,null ,null ,null ,null ,1)
GO
_______________________________________________
INSERT STATEMENTS FOR THE COMPLETED TASKS
-- SCENARIO 1: a case has been completed yesterday-- The Printer, Email, UserID, UserName have been completed and the case is closed
INSERT INTO [HRA].[Task]
([CaseID] ,[PositionUpdateID] ,[LutTaskCd] ,[Description] ,[Comment] ,[EmployeeUpdateID] ,[IsActive] ,[CreatedBy] ,[CreatedOn] ,[ModifiedBy] ,[ModifiedOn] ,[Role])
VALUES
(1 , 1 ,1 , null , null , null , 1 ,1 ,GetDate() -1 ,null , null ,'systems'),
(1 , 1 ,2 , null , null , null , 1 ,1 ,GetDate() -1 ,null , null ,'systems'),
(1 , 1 ,3 , null , null , null , 1 ,1 ,GetDate() -1 ,null , null ,'systems'),
(1 , 1 ,4 , null , null , null , 1 ,1 ,GetDate() -1 ,null , null ,'systems')
-- SCENARIO 2: Case that was not completed Yesterday -- The Printer, Email, UserID have been completed; the UserName is still pending
INSERT INTO [HRA].[Task]
([CaseID] ,[PositionUpdateID] ,[LutTaskCd] ,[Description] ,[Comment] ,[EmployeeUpdateID] ,[IsActive] ,[CreatedBy] ,[CreatedOn] ,[ModifiedBy] ,[ModifiedOn] ,[Role])
VALUES
(2 , 1 ,1 , null , null , null , 1 ,1 ,GetDate() -1 ,null , null ,'systems'),
(2 , 1 ,2 , null , null , null , 1 ,1 ,GetDate() -1 ,null , null ,'systems'),
(2 , 1 ,3 , null , null , null , 1 ,1 ,GetDate() -1 ,null , null ,'systems')
-- SCENARIO 3: -- Case has not been completed today -- the email task has been completed
INSERT INTO [HRA].[Task]
([CaseID] ,[PositionUpdateID] ,[LutTaskCd] ,[Description] ,[Comment] ,[EmployeeUpdateID] ,[IsActive] ,[CreatedBy] ,[CreatedOn] ,[ModifiedBy] ,[ModifiedOn] ,[Role])
VALUES
(2 , 1 ,2 , null , null , null , 1 ,1 ,GetDate() ,null , null ,'systems')
-- SCENARIO 4: No tasks have been completed.
-- no insert statement required.
June 18, 2019 at 8:54 pm
I'm guessing the ParentTaskCd is used to link the tasks together. Naturally adjust the code as needed to get the specific results you need, but this is a general way to see if all rows appear in another table.
SELECT TD.ParentTaskCd,
CASE WHEN COUNT(TD.LutTaskCd) = COUNT(C.LutTaskCd)
THEN 'Completed' ELSE 'Open' END AS TaskStatus
FROM dbo.ToDo TD
LEFT OUTER JOIN dbo.Completed C ON C.LutTaskCd = TD.LutTaskCd
GROUP BY TD.ParentTaskCd
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply