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

  • ckuroda

    SSC-Addicted

    Points: 461

    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:

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

    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?

  • Site Owners

    SSC Guru

    Points: 80385

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

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

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