==========
Please can you help me as I am having a brain freeze this morning and cant see the wood for the trees
==========
I need to write a report :
INPUT: %1 user id %2 coordinator id
| OUTPUT: Project | Projectno | Participant | userid | Role ISCOORDINATOR |
eg.
| PROJECT 1 | 49228682 | fred.bloggs | 42520178 | Members YES | |
| PROJECT 2 | 44224281 | fred.bloggs | 42520178 | Members NO | |
| PROJECT 3 | 55074060 | fred.bloggs | 42520178 | Members YES |
Which informs me which projects a particular user (%1) is a member of and if coordinator id (%2) is a coordinator of that project.
------------------
I already have a report which returns a list of all the projects a user is a member of:
SELECT DTree.Name AS Project, DTree.Dataid AS Projectno, KUAF1.Name AS Participant, KUAF1.id as userid , KUAF.Name AS Role FROM KUAF INNER JOIN DTree ON KUAF.Type = DTree.DataID INNER JOIN KUAFChildren ON KUAF.ID = KUAFChildren.ID INNER JOIN KUAF KUAF1 ON KUAFChildren.ChildID = KUAF1.ID WHERE (KUAF.Name = 'Members') and KUAF1.id=%1 order by dtree.name
%1= User id
Output:
| PROJECT 1 | 49228682 | fred.bloggs | 42520178 | Members | |
| PROJECT 2 | 44224281 | fred.bloggs | 42520178 | Members | |
| PROJECT 3 | 55074060 | fred.bloggs | 42520178 | Members |
Also, I have a function on the database that returns YES or NO when you pass the project id and the coordinators' id to it..
ALTER FUNCTION [Admin] . [user_is_coordinator] ( @ProjectID INT , @coordid INT )
RETURNS varChar ( 8 ) AS
BEGIN
Declare @iscoord varChar ( 8 )
if @projectID <> 0
Begin
IF exists(
select kuaf . id from admin . kuaf , admin . kuafchildren
where kuafchildren . childid = kuaf . id
and kuafchildren . id =( select id from admin . kuaf where type = @projectID
and name like 'coordinators' )
and kuaf . id = @coordid )
BEGIN
set @iscoord = 'YES'
END
ELSE
set @iscoord = 'NO'
END
RETURN @iscoord
end
---------------------
Can anybody point me in the right direction please?
Many thanks
