#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    12
    Rep Power
    0

    Join Two Queries- Continued


    This is a tough one- anyone who's up for a challenge should continue.

    Currently my sql structure is :

    Code:
    SELECT *
    FROM 
    (Project info Query) AS [PROJECT INFO]
    LEFT OUTER JOIN
    (Timesheet Query) AS [TIMESHEET INFO]
    ON ResourceName
    AND DateTime
    AND TaskName
    AND ProjectName
    My issue is the following: There is 1 project from [TIMESHEET INFO] that doesn't match a project in [PROJECT INFO], and since I'm using a Left Outer Join it drops that 1 project from timesheet. The particular project is a system generated project to capture 'other tasks i.e. Sick/Vacation/Administration on the timesheets. However people do get sick etc and report time on their timesheet, but the info is lost because of the Join.

    I've thought about swapping Timesheet and Project info, however I require [PROJECT INFO] first because I use it to return all the resources that are needed for the projects. Not all resources submit timesheets- so if I where to put that first I'd lose a bit of detail about resources.

    I need that project info. How do I bring that info in?
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,385
    Rep Power
    391
    Use a full outer join
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    12
    Rep Power
    0
    That helps- It does bring the row of data, but when I group or report using the Columns- since the values are in different columns it reports blank.

    Ie.
    Code:
    ID|Res Name|Proj.ProjName|TS.ProjName
    1   Ziad        Test 1            Test 1
    2   Ziad         (blank)          Test 2
    3   Ziad        Test 3            (blank)
    So if I Report on All Projects Ziad is working on (display Proj.ProjName it will say
    He's working on Test 1 and Test 3
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    use COALESCE(Proj.ProjName,TS.ProjName) AS projname in your SELECT clause
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    12
    Rep Power
    0
    I did something to my query and now it's giving me 'Incorrect syntax near the keyword 'AS'

    And for the life of me I can't figure out.

    Please be gentle looking at the query as I'm sure you can tell from looking at it I'm a begginer at best:

    Code:
    SELECT COALESCE([Assignment work by day].ProjectName,[Timesheet 2013].ProjectName)
    	, COALESCE([Assignment work by day].ResourceName, [Timesheet 2013].ResourceName)
    	, COALESCE([Assignment work by day].TaskName,[Timesheet 2013].TaskName)
    	, [Assignment work by day].TimeByDay
    	, [Assignment work by day].AssignmentWork
    	, [Assignment work by day].AssignmentActualWork
    	, [Assignment work by day].[Resource Departments]
    	, [Timesheet 2013].ActualWorkBillable
    	, [Timesheet 2013].ActualOvertimeWorkBillable
    	, [Assignment work by day].[Resource Manager]
    	, [Assignment work by day].GroupManagerFullName
    FROM (
    		(SELECT MSP_EpmProject.ProjectUID, MSP_EpmAssignmentByDay.TaskUID, MSP_EpmAssignmentByDay.TimeByDay, 				MSP_EpmAssignmentByDay.AssignmentWork, MSP_EpmAssignmentByDay.AssignmentActualWork, MSP_EpmResource_UserView.ResourceUID, MSP_EpmResource_UserView.ResourceIsActive, MSP_EpmResource_UserView.[Resource Departments], CSX_GroupManager.GroupManagerFullName, CSX_ResourceManager.[Resource Manager]
    		FROM (((MSP_EpmAssignmentByDay INNER JOIN MSP_EpmAssignment ON MSP_EpmAssignmentByDay.AssignmentUID = MSP_EpmAssignment.AssignmentUID) INNER JOIN MSP_EpmProject ON (MSP_EpmAssignmentByDay.ProjectUID = MSP_EpmProject.ProjectUID) AND (MSP_EpmAssignment.ProjectUID = MSP_EpmProject.ProjectUID)) INNER JOIN MSP_EpmTask ON (MSP_EpmProject.ProjectUID = MSP_EpmTask.ProjectUID) AND (MSP_EpmAssignmentByDay.TaskUID = MSP_EpmTask.TaskUID)) INNER JOIN MSP_EpmResource_UserView ON MSP_EpmAssignment.ResourceUID = MSP_EpmResource_UserView.ResourceUID
    		WHERE (((MSP_EpmResource_UserView.ResourceIsActive)=True) AND ((MSP_EpmResource_UserView.[Resource Departments])='CSX')))
    	LEFT OUTER JOIN (
    		SELECT CSX_ResourceManager.ResourceFullName, CSX_ResourceManager.[Resource Manager], CSX_GroupManager.GroupManagerFullName
    		FROM CSX_GroupManager RIGHT JOIN CSX_ResourceManager ON CSX_GroupManager.ResourceManagerFullName = CSX_ResourceManager.[Resource Manager]) 
    	ON CSX_ResourceManager.ResourceFullName = MSP_EpmResource_Userview.ResourceName
    ) AS [Assignment work by day] 
    FULL OUTER JOIN ( 
    	SELECT MSP_EpmResource_UserView.ResourceUID, MSP_EpmResource_UserView.ResourceName, MSP_EpmResource_UserView.[Resource Departments], MSP_EpmResource_UserView.ResourceIsActive, [Timesheet_By_Day].TaskUID, [Timesheet_By_Day].TaskName, [Timesheet_By_Day].ProjectUID, [Timesheet_By_Day].ProjectName, [Timesheet_By_Day].ActualWorkBillable, [Timesheet_By_Day].ActualOvertimeWorkBillable, [Timesheet_By_Day].PlannedWork, [Timesheet_By_Day].TimeByDay
    	FROM MSP_EpmResource_UserView
    		LEFT OUTER JOIN (
    		SELECT MSP_TimesheetResource.ResourceUID, MSP_TimesheetTask.TaskUID, MSP_TimesheetTask.TaskName, MSP_TimesheetProject.ProjectUID, MSP_TimesheetProject.ProjectName, MSP_TimesheetActual.ActualWorkBillable, MSP_TimesheetActual.ActualOvertimeWorkBillable, MSP_TimesheetActual.PlannedWork, MSP_TimesheetActual.TimeByDay
    		FROM (MSP_TimesheetResource INNER JOIN (((MSP_TimesheetLine INNER JOIN MSP_TimesheetProject ON MSP_TimesheetLine.ProjectNameUID = MSP_TimesheetProject.ProjectNameUID) INNER JOIN MSP_TimesheetTask ON MSP_TimesheetLine.TaskNameUID = MSP_TimesheetTask.TaskNameUID) INNER JOIN MSP_Timesheet ON MSP_TimesheetLine.TimesheetUID = MSP_Timesheet.TimesheetUID) ON MSP_TimesheetResource.ResourceNameUID = MSP_Timesheet.OwnerResourceNameUID) INNER JOIN MSP_TimesheetActual ON MSP_TimesheetLine.TimesheetLineUID = MSP_TimesheetActual.TimesheetLineUID) AS [Timesheet_By_Day]
    			ON MSP_EpmResource_UserView.ResourceUID = [Timesheet_By_Day].ResourceUID
    			WHERE MSP_EpmResource_UserView.[Resource Departments]='CSX' AND MSP_EpmResource_UserView.ResourceIsActive= 'True'  AND ([Timesheet_By_Day].TimeByDay > '12/29/2012' OR [Timesheet_By_Day].TimeByDay is NULL)) AS [Timesheet 2013] 
        ON [Timesheet 2013].ResourceUID = [Assignment work by day].ResourceUID
       AND [Timesheet 2013].TimeByDay    = [Assignment work by day].TimeByDay   
       AND [Timesheet 2013].TaskUID     = [Assignment work by day].TaskUID    
       AND [Timesheet 2013].ProjectUID  = [Assignment work by day].ProjectUID
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    12
    Rep Power
    0
    Or better yet- is there some where I can write when TS.Project name = 'Admin' then coalesce?

    because it's this 1 project- it's a catch all timebin on the timesheet for unplanned work/sick, however it doesn't exist on the project side.
  12. #7
  13. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    your code --
    Code:
    FROM (
      (SELECT MSP_EpmProject.ProjectUID, MSP_EpmAssignmentByDay.TaskUID, MSP_EpmAssignmentByDay.TimeByDay,     MSP_EpmAssignmentByDay.AssignmentWork, MSP_EpmAssignmentByDay.AssignmentActualWork, MSP_EpmResource_UserView.ResourceUID, MSP_EpmResource_UserView.ResourceIsActive, MSP_EpmResource_UserView.[Resource Departments], CSX_GroupManager.GroupManagerFullName, CSX_ResourceManager.[Resource Manager]
      FROM (((MSP_EpmAssignmentByDay INNER JOIN MSP_EpmAssignment ON MSP_EpmAssignmentByDay.AssignmentUID = MSP_EpmAssignment.AssignmentUID) INNER JOIN MSP_EpmProject ON (MSP_EpmAssignmentByDay.ProjectUID = MSP_EpmProject.ProjectUID) AND (MSP_EpmAssignment.ProjectUID = MSP_EpmProject.ProjectUID)) INNER JOIN MSP_EpmTask ON (MSP_EpmProject.ProjectUID = MSP_EpmTask.ProjectUID) AND (MSP_EpmAssignmentByDay.TaskUID = MSP_EpmTask.TaskUID)) INNER JOIN MSP_EpmResource_UserView ON MSP_EpmAssignment.ResourceUID = MSP_EpmResource_UserView.ResourceUID
      WHERE (((MSP_EpmResource_UserView.ResourceIsActive)=True) AND ((MSP_EpmResource_UserView.[Resource Departments])='CSX')))
     LEFT OUTER JOIN (
      SELECT CSX_ResourceManager.ResourceFullName, CSX_ResourceManager.[Resource Manager], CSX_GroupManager.GroupManagerFullName
      FROM CSX_GroupManager RIGHT JOIN CSX_ResourceManager ON CSX_GroupManager.ResourceManagerFullName = CSX_ResourceManager.[Resource Manager]) 
     ON CSX_ResourceManager.ResourceFullName = MSP_EpmResource_Userview.ResourceName
    ) AS [Assignment work by day]
    your code, cleaned up (you're welcome) --
    Code:
    FROM ( ( SELECT MSP_EpmProject.ProjectUID
                  , MSP_EpmAssignmentByDay.TaskUID
                  , MSP_EpmAssignmentByDay.TimeByDay
                  , MSP_EpmAssignmentByDay.AssignmentWork
                  , MSP_EpmAssignmentByDay.AssignmentActualWork
                  , MSP_EpmResource_UserView.ResourceUID
                  , MSP_EpmResource_UserView.ResourceIsActive
                  , MSP_EpmResource_UserView.[Resource Departments]
                  , CSX_GroupManager.GroupManagerFullName
                  , CSX_ResourceManager.[Resource Manager]
               FROM (
                    (
                    (
                    MSP_EpmAssignmentByDay 
             INNER 
               JOIN MSP_EpmAssignment 
                 ON MSP_EpmAssignmentByDay.AssignmentUID = MSP_EpmAssignment.AssignmentUID
                    ) 
             INNER 
               JOIN MSP_EpmProject 
                 ON (
                    MSP_EpmAssignmentByDay.ProjectUID = MSP_EpmProject.ProjectUID
                    ) 
                AND (
                    MSP_EpmAssignment.ProjectUID = MSP_EpmProject.ProjectUID
                    )
                    ) 
              INNER 
                JOIN MSP_EpmTask 
                  ON (
                     MSP_EpmProject.ProjectUID = MSP_EpmTask.ProjectUID
                     ) 
                 AND (
                     MSP_EpmAssignmentByDay.TaskUID = MSP_EpmTask.TaskUID
                     )
                     ) 
              INNER 
                JOIN MSP_EpmResource_UserView 
                  ON MSP_EpmAssignment.ResourceUID = MSP_EpmResource_UserView.ResourceUID
               WHERE (((
                     MSP_EpmResource_UserView.ResourceIsActive
                     ) = True
                     ) 
                 AND ((
                     MSP_EpmResource_UserView.[Resource Departments]
                     ) ='CSX'
                     )
                     )
                     )
              LEFT OUTER 
                JOIN (
                     SELECT CSX_ResourceManager.ResourceFullName
                          , CSX_ResourceManager.[Resource Manager]
                          , CSX_GroupManager.GroupManagerFullName
                       FROM CSX_GroupManager 
                     RIGHT 
                       JOIN CSX_ResourceManager 
                         ON CSX_GroupManager.ResourceManagerFullName = CSX_ResourceManager.[Resource Manager]
                     ) 
                 ON CSX_ResourceManager.ResourceFullName = MSP_EpmResource_Userview.ResourceName
                     ) AS [Assignment work by day]
    as you can plainly see, your error message is the result of incorrect parenthesization

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    12
    Rep Power
    0


    Getting incorrect syntax near the keyword 'ON' now- And as always thank you for your help!

    This is what I'm using:

    Code:
    SELECT MSP_EpmProject.ProjectUID
     , MSP_EpmAssignmentByDay.TaskUID
     , MSP_EpmAssignmentByDay.TimeByDay
     , MSP_EpmAssignmentByDay.AssignmentWork
     , MSP_EpmAssignmentByDay.AssignmentActualWork
     , MSP_EpmResource_UserView.ResourceUID
     , MSP_EpmResource_UserView.ResourceIsActive
     , MSP_EpmResource_UserView.[Resource Departments]
     , CSX_GroupManager.GroupManagerFullName
     , CSX_ResourceManager.[Resource Manager]
               FROM (
                    (
                    (
                    MSP_EpmAssignmentByDay 
             INNER 
               JOIN MSP_EpmAssignment 
                 ON MSP_EpmAssignmentByDay.AssignmentUID = MSP_EpmAssignment.AssignmentUID
                    ) 
             INNER 
               JOIN MSP_EpmProject 
                 ON (
                    MSP_EpmAssignmentByDay.ProjectUID = MSP_EpmProject.ProjectUID
                    ) 
                AND (
                    MSP_EpmAssignment.ProjectUID = MSP_EpmProject.ProjectUID
                    )
                    ) 
              INNER 
                JOIN MSP_EpmTask 
                  ON (
                     MSP_EpmProject.ProjectUID = MSP_EpmTask.ProjectUID
                     ) 
                 AND (
                     MSP_EpmAssignmentByDay.TaskUID = MSP_EpmTask.TaskUID
                     )
                     ) 
              INNER 
                JOIN MSP_EpmResource_UserView 
                  ON MSP_EpmAssignment.ResourceUID = MSP_EpmResource_UserView.ResourceUID
               WHERE (((
                     MSP_EpmResource_UserView.ResourceIsActive
                     ) = True
                     ) 
                 AND ((
                     MSP_EpmResource_UserView.[Resource Departments]
                     ) ='CSX'
                     )
                     )
                     )
              LEFT OUTER 
                JOIN (
                     SELECT CSX_ResourceManager.ResourceFullName
                          , CSX_ResourceManager.[Resource Manager]
                          , CSX_GroupManager.GroupManagerFullName
                       FROM CSX_GroupManager 
                     INNER 
                       JOIN CSX_ResourceManager 
                         ON CSX_GroupManager.ResourceManagerFullName = CSX_ResourceManager.[Resource Manager]
                     ) 
                 ON CSX_ResourceManager.ResourceFullName = MSP_EpmResource_Userview.ResourceName
  16. #9
  17. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    dude, get rid of all unnecessary parentheses
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  18. #10
  19. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    12
    Rep Power
    0
    Originally Posted by r937
    dude, get rid of all unnecessary parentheses
    I must confess I used the help of MS Access to help me write the initial queries, which loves to throw in all the parentheses.

    I figured it out after cleaning up all the '( )'s

    I decided to write a query that excluded the Project 'ADMIN' ( the one that wouldn't join) then wrote a query using a UNION ALL that only included the 'ADMIN' project.

    From my initial investigation it looks like it worked. Are there any pitfalls I'm failing to see by using this method?
  20. #11
  21. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    Originally Posted by ZiadZ
    Are there any pitfalls I'm failing to see by using this method?
    none that i can, um, see
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo