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

    Join Date
    May 2013
    Posts
    12
    Rep Power
    0

    [solved] Combine 2 queries


    Hello All,

    What would be the easiest way to combine 2 queries?

    Timesheet 2013:
    Code:
    SELECT MSP_TimesheetResource.ResourceName, MSP_TimesheetTask.TaskName, MSP_TimesheetProject.ProjectName, MSP_TimesheetActual.TimeByDay, MSP_TimesheetActual.ActualWorkBillable, MSP_TimesheetActual.ActualOvertimeWorkBillable
    FROM MSP_TimesheetActual 
    INNER JOIN ((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_EpmAssignment ON (MSP_TimesheetResource.ResourceUID = MSP_EpmAssignment.ResourceUID) AND (MSP_TimesheetTask.TaskUID = MSP_EpmAssignment.TaskUID) AND (MSP_TimesheetProject.ProjectUID = MSP_EpmAssignment.ProjectUID)) ON MSP_TimesheetActual.TimesheetLineUID = MSP_TimesheetLine.TimesheetLineUID
    WHERE (((MSP_TimesheetProject.ProjectName) Like '2013 CSX%'));
    Assignment work by Day:
    Code:
    SELECT MSP_EpmProject.ProjectName, MSP_EpmTask.TaskName, MSP_EpmAssignmentByDay.TimeByDay, MSP_EpmAssignmentByDay.AssignmentWork, MSP_EpmAssignmentByDay.AssignmentActualWork, MSP_EpmResource.ResourceName, MSP_ResourceManager.Resource_Manager, MSP_GroupManager.Group_Manager
    FROM (MSP_GroupManager 
    INNER JOIN MSP_ResourceManager ON MSP_GroupManager.Resource_Manager = MSP_ResourceManager.Resource_Manager) 
    INNER JOIN (((MSP_EpmAssignmentByDay 
    INNER JOIN (MSP_EpmAssignment 
    INNER JOIN MSP_EpmResource ON (MSP_EpmAssignment.ResourceUID = MSP_EpmResource.ResourceUID) AND (MSP_EpmAssignment.ResourceUID = MSP_EpmResource.ResourceUID)) 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)) ON MSP_ResourceManager.fullname = MSP_EpmResource.ResourceName
    WHERE (((MSP_EpmProject.ProjectName) Like '2013 CSX%') AND ((MSP_EpmAssignmentByDay.TimeByDay)>='1/1/2013' And (MSP_EpmAssignmentByDay.TimeByDay)<='9/1/2013'));
    Into something like:
    Code:
    SELECT [Assignment work by day].ProjectName, [Assignment work by day].ResourceName, [Assignment work by day].TaskName, [Assignment work by day].TimeByDay, [Assignment work by day].AssignmentWork, [Assignment work by day].AssignmentActualWork, [Timesheet 2013].ActualWorkBillable, [Timesheet 2013].ActualOvertimeWorkBillable, [Assignment work by day].Resource_Manager, [Assignment work by day].Group_Manager
    FROM [Assignment work by day] 
    LEFT JOIN [Timesheet 2013] ON ([Assignment work by day].ResourceName = [Timesheet 2013].ResourceName) AND ([Assignment work by day].TimeByDay = [Timesheet 2013].TimeByDay) AND ([Assignment work by day].TaskName = [Timesheet 2013].TaskName) AND ([Assignment work by day].ProjectName = [Timesheet 2013].ProjectName);
    Last edited by ZiadZ; May 2nd, 2013 at 11:36 AM. Reason: Problem Solved!
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,238
    Rep Power
    4279
    Code:
    SELECT [Assignment work by day].ProjectName
         , [Assignment work by day].ResourceName
         , [Assignment work by day].TaskName
         , [Assignment work by day].TimeByDay
         , [Assignment work by day].AssignmentWork
         , [Assignment work by day].AssignmentActualWork
         , [Timesheet 2013].ActualWorkBillable
         , [Timesheet 2013].ActualOvertimeWorkBillable
         , [Assignment work by day].Resource_Manager
         , [Assignment work by day].Group_Manager
      FROM ( 
           -- assignment query goes here
           ) AS [Assignment work by day] 
    LEFT OUTER
      JOIN ( 
           -- timesheet query goes here
           ) AS [Timesheet 2013] 
        ON [Timesheet 2013].ResourceName = [Assignment work by day].ResourceName 
       AND [Timesheet 2013].TimeByDay    = [Assignment work by day].TimeByDay   
       AND [Timesheet 2013].TaskName     = [Assignment work by day].TaskName    
       AND [Timesheet 2013].ProjectName  = [Assignment work by day].ProjectName

    Comments on this post

    • ZiadZ agrees : Excellent work!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    12
    Rep Power
    0
    Thank you for the fast reply!
    I'm getting the following errors :
    Incorrect syntax near the keyword 'AS'.
    Incorrect syntax near 'MSP_EpmProject'.
    Incorrect syntax near 'MSP_EpmAssignmentByDay'.
    Incorrect syntax near ','.

    I think i'm missing some ;



    Code:
    SELECT [Assignment work by day].ProjectName
    	, [Assignment work by day].ResourceName
    	, [Assignment work by day].TaskName
    	, [Assignment work by day].TimeByDay
    	, [Assignment work by day].AssignmentWork
    	, [Assignment work by day].AssignmentActualWork
    	, [Timesheet 2013].ActualWorkBillable
    	, [Timesheet 2013].ActualOvertimeWorkBillable
    	, [Assignment work by day].Resource_Manager
    	, [Assignment work by day].Group_Manager
    FROM ( 
    	MSP_EpmAssignmentByDay.AssignmentWork
    	, MSP_EpmAssignmentByDay.AssignmentActualWork
    	, MSP_EpmResource.ResourceName
    	, MSP_ResourceManager.Resource_Manager
    	, MSP_GroupManager.Group_Manager
    FROM (
    	MSP_GroupManager 
    	INNER JOIN MSP_ResourceManager ON MSP_GroupManager.Resource_Manager = MSP_ResourceManager.Resource_Manager) 
    	INNER JOIN (((MSP_EpmAssignmentByDay 
    	INNER JOIN (MSP_EpmAssignment 
    	INNER JOIN MSP_EpmResource ON (MSP_EpmAssignment.ResourceUID = MSP_EpmResource.ResourceUID) AND (MSP_EpmAssignment.ResourceUID = MSP_EpmResource.ResourceUID)) 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)) ON MSP_ResourceManager.fullname = MSP_EpmResource.ResourceName
    WHERE (((MSP_EpmProject.ProjectName) Like '2013 CSX%') AND ((MSP_EpmAssignmentByDay.TimeByDay)>='1/1/2013' And (MSP_EpmAssignmentByDay.TimeByDay)<='9/1/2013'))
    ) AS [Assignment work by day] 
    LEFT OUTER JOIN ( 
    SELECT MSP_TimesheetResource.ResourceName
    	, MSP_TimesheetTask.TaskName
    	, MSP_TimesheetProject.ProjectName
    	, MSP_TimesheetActual.TimeByDay
    	, MSP_TimesheetActual.ActualWorkBillable
    	, MSP_TimesheetActual.ActualOvertimeWorkBillable
    FROM MSP_TimesheetActual 
    INNER JOIN ((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_EpmAssignment ON (MSP_TimesheetResource.ResourceUID = MSP_EpmAssignment.ResourceUID) AND (MSP_TimesheetTask.TaskUID = MSP_EpmAssignment.TaskUID) AND (MSP_TimesheetProject.ProjectUID = MSP_EpmAssignment.ProjectUID)) ON MSP_TimesheetActual.TimesheetLineUID = MSP_TimesheetLine.TimesheetLineUID
    WHERE (((MSP_TimesheetProject.ProjectName) Like '2013 CSX%'))
    ) AS [Timesheet 2013] 
        ON [Timesheet 2013].ResourceName = [Assignment work by day].ResourceName 
       AND [Timesheet 2013].TimeByDay    = [Assignment work by day].TimeByDay   
       AND [Timesheet 2013].TaskName     = [Assignment work by day].TaskName    
       AND [Timesheet 2013].ProjectName  = [Assignment work by day].ProjectName
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,238
    Rep Power
    4279
    looks like you forgot the SELECT keyword on the first query
    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
    Originally Posted by r937
    looks like you forgot the SELECT keyword on the first query
    Genius! Looks like it's working.
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    12
    Rep Power
    0
    How do I mark this as solve/closed and give you credit?
  12. #7
  13. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,238
    Rep Power
    4279
    Originally Posted by ZiadZ
    How do I mark this as solve/closed and give you credit?
    we usually don't bother marking threads closed

    but as i am also a mod, i did it for you

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo