SQL Multiple new columns

[plip1978]

Hi all,
I'm trying to create new columns based on the elements within a column. I have two tables, the first is like this:

Tasks:

Key | Tree | Duty | Done_by |
-----------------------------
2 | 2 | Top | NULL |
3 | 2 | Plan | Bill |
4 | 2 | Done | Jane |
5 | 3 | Top | NULL |
etc

and Area:

Base | Site |
----------------
2 | Bristol |
5 | Cardiff |
8 | Bristol |
etc

and the table I want to produce looks like this:

Plans Done_by | COUNT(Bristol) | COUNT(Cardiff) |
-------------------------------------------------
Bill | 2 | 1 |
etc

As you can see the key is shown in base but refers to a specific assignment listed in Tree. Each assignment can be undertaken by different individuals. This is what I have so far:

SELECT TRIM(UPPER(tk.done_by)) AS "Plans done by", COUNT(ar.site) AS "Bristol",
COUNT(ar2.site) AS "Cardiff"
FROM Task tk
INNER JOIN Task tk2 ON tk.tree = tk2.tree
LEFT OUTER JOIN Area ar ON tk2.key = ar.base
INNER JOIN Task tk3 ON tk.tree = tk3.tree
LEFT OUTER JOIN Area ar2 ON tk3.key = ar2.base
WHERE (tk.duty = 'plan' AND ar.site = 'Bristol')
OR (ar2.site = 'Cardiff' AND tk.duty = 'plan')
GROUP BY TRIM(UPPER(tk.done_by))
ORDER BY TRIM(UPPER(tk.done_by)), COUNT(ar.site) DESC, COUNT(ar2.site) DESC

As you may be able to see from this query, I am a complete SQL novice! Please can you help!!!!

 

[simian336]

Please reexplain, I am confused.

key = base
tree= ?

Simi

 

[markros]

Something like

declare @Tasks table ([Key] int, Tree int,  Duty varchar(10), Done_by varchar(20))
insert into @tasks 
select
 2  , 2    , 'Top', NULL    
union all select 
 3, 2,    'Plan' , 'Bill'
union all select 
 4  , 2    , 'Done', 'Jane'
 union all select
 5  , 3    , 'Top', NULL    


declare @Area table (Base int, [Site] varchar(10))    
insert into @Area 
select
 2   , 'Bristol'
 union all select
 
 5   , 'Cardiff'
 union all select
 8   , 'Bristol'
 
-- Plans Done_by | COUNT(Bristol) | COUNT(Cardiff) |
---------------------------------------------------
-- Bill         |     2          |    1           |
 
 ;with cte as (select T.*, A.Site 
 from @Area A inner join @Tasks T
 on A.Base = T.[Tree] where T.Duty = 'Plan') 
 
 select Done_By as [Plans Done by],
 SUM(case when [Site] = 'Bristol' then 1 else 0 end) as [COUNT(Bristol)],
 SUM(case when [Site] = 'Cardiff' then 1 else 0 end) as [COUNT(Cardiff)]
 from cte group by Done_by 
 order by Done_by

although I'm also a bit confused. If your first table recursive?



PluralSight Learning Library

 

[plip1978]

Sorry, I've just tried to include some of the relevant bits from a larger schema. Key is the primary key of the table Tasks. Tree is obtained from another table (not shown) which stores project data. So each project (given a unique Tree value) is assigned tasks Top, Plan, Done.
Does this make more sense?
Thanks again.