Professional Web Applications Themes

Brain Drain. Please help - Microsoft SQL / MS SQL Server

Hi, I'm having a problem in grouping the following project planning related data, The data looks like this, Task Resource Actuals IsTask Planning 0 DB Schema Allen 10 1 WebDesign Raj 5 1 TestPlan Allen 3 1 Coding 0 ASPX Allen 20 1 HTML Raj 10 1 SQL Allen 25 1 The above data shows the project plan breakup If IsTask = o then Project Phase,If is IsTask = 1 Then Project Task I need to write a query which returns phase wise Resource Total of actuals ie the output should be like this, Planning Allen 13 Raj 5 Coding ...

  1. #1

    Default Brain Drain. Please help

    Hi,

    I'm having a problem in grouping the following project
    planning related data,

    The data looks like this,

    Task Resource Actuals IsTask
    Planning 0
    DB Schema Allen 10 1
    WebDesign Raj 5 1
    TestPlan Allen 3 1
    Coding 0
    ASPX Allen 20 1
    HTML Raj 10 1
    SQL Allen 25 1


    The above data shows the project plan breakup If IsTask =
    o then Project Phase,If is IsTask = 1 Then Project Task

    I need to write a query which returns phase wise Resource
    Total of actuals ie the output should be like this,


    Planning
    Allen 13
    Raj 5
    Coding
    Allen 45
    Raj 10


    Please find the DDL with sample data given below (please
    note TaskID & ResourceID are RI Data), and help me to
    solve this problem

    Happy programming,
    Holy


    CREATE TABLE [dbo].[Assignment] (
    [TaskID] [int] NULL ,
    [ResourceID] [int] NULL ,
    [Actuals] [decimal](18, 0) NULL ,
    [IsTask] [bit] NULL
    ) ON [PRIMARY]



    INSERT INTO Assignment
    (TaskID, ResourceID, Actuals, IsTask)
    VALUES (1, NULL, NULL, 0)

    INSERT INTO Assignment
    (TaskID, ResourceID, Actuals, IsTask)
    VALUES (2, 1, 10, 1)

    INSERT INTO Assignment
    (TaskID, ResourceID, Actuals, IsTask)
    VALUES (3, 2, 5, 1)

    INSERT INTO Assignment
    (TaskID, ResourceID, Actuals, IsTask)
    VALUES (4, 1, 3, 1)

    INSERT INTO Assignment
    (TaskID, ResourceID, Actuals, IsTask)
    VALUES (5, NULL, NULL, 0)

    INSERT INTO Assignment
    (TaskID, ResourceID, Actuals, IsTask)
    VALUES (6, 1, 20, 1)

    INSERT INTO Assignment
    (TaskID, ResourceID, Actuals, IsTask)
    VALUES (7, 2, 10, 1)

    INSERT INTO Assignment
    (TaskID, ResourceID, Actuals, IsTask)
    VALUES (8, 1, 25, 1)

    holysmoke Guest

  2. #2

    Default Re: Brain Drain. Please help

    You are missing one important field in your table. This field would give information about which project task belongs to what
    project phase.
    Whitout it it is impossible to group project tasks by their project phases.

    This field I called ProjectPhase. If a record represents ProjectPhase (e.g. Planning and Coding) value is null. For project tasks
    this field has a TaskID
    value of given project phase.

    Here is the code:

    CREATE TABLE [dbo].[Assignment] (
    [TaskID] [int] NULL ,
    [ResourceID] [int] NULL ,
    [Actuals] [decimal](18, 0) NULL ,
    [IsTask] [bit] NULL ,
    [ProjectPhase] int null
    ) ON [PRIMARY]

    INSERT INTO Assignment (TaskID, ResourceID, Actuals, IsTask, ProjectPhase)
    VALUES (1, NULL, NULL, 0, 1)
    INSERT INTO Assignment (TaskID, ResourceID, Actuals, IsTask, ProjectPhase)
    VALUES (2, 1, 10, 1, 1)
    INSERT INTO Assignment (TaskID, ResourceID, Actuals, IsTask, ProjectPhase)
    VALUES (3, 2, 5, 1, 1)
    INSERT INTO Assignment (TaskID, ResourceID, Actuals, IsTask, ProjectPhase)
    VALUES (4, 1, 3, 1, 1)
    INSERT INTO Assignment (TaskID, ResourceID, Actuals, IsTask, ProjectPhase)
    VALUES (5, NULL, NULL, 0, 5)
    INSERT INTO Assignment (TaskID, ResourceID, Actuals, IsTask, ProjectPhase)
    VALUES (6, 1, 20, 1, 5)
    INSERT INTO Assignment (TaskID, ResourceID, Actuals, IsTask, ProjectPhase)
    VALUES (7, 2, 10, 1, 5)
    INSERT INTO Assignment (TaskID, ResourceID, Actuals, IsTask, ProjectPhase)
    VALUES (8, 1, 25, 1, 5)

    select
    ResourceID = (case
    when ResourceID is null then 'Phase - ' + convert(varchar(30), min(TaskID))
    else convert(varchar(30), ResourceID)
    end)
    , sum(Actuals)
    from Assignment
    group by ProjectPhase, ResourceID
    order by min(TaskID)

    drop table Assignment

    --
    Dean Savovic
    www.teched.hr


    "holysmoke" <it> wrote in message news:088a01c350f4$dd0d3b20$gbl... 


    Dean Guest

  3. #3

    Default Re: Brain Drain. Please help

    Hi,

    Thanks. Now I found that phase field in this propertiery product after
    your suggestions.

    Holy


    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Holy Guest

Similar Threads

  1. Reducing RAM drain from 3 streaming sounds
    By jerrygarciuh in forum Macromedia Flash
    Replies: 0
    Last Post: June 30th, 09:03 PM
  2. Brain hurts
    By Christoffer in forum Macromedia Director Lingo
    Replies: 9
    Last Post: February 5th, 03:59 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139