Best practices for comparing ToDo tasks to completed tasks when the ToDo tasks c

  • ckuroda

    SSC-Addicted

    Points: 461

    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?

  • Phil Parkin

    SSC Guru

    Points: 243688

    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.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • ckuroda

    SSC-Addicted

    Points: 461

    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.

     

     

  • ScottPletcher

    SSC Guru

    Points: 98119

    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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply