none
Retrieving the end date of a task using SQL connection where a specific criteria is met RRS feed

  • Question

  • Hi

    I have an existing report that pulls information from Server using the following select statement

    SELECT datepart(iso_week,MSP_EpmTask_UserView.TaskFinishDate) as [Week] ,

    MSP_EpmTask_UserView.[e-Vehicle Type] as [Vehicle],

    MSP_EpmTask_UserView.[E-Section ID] as [Cont.],

    MSP_EpmTask_UserView.[e-WPO] as [WPO],

    MSP_EpmTask_UserView.[e-Customer] as [Customer],

    MSP_EpmTask_UserView.[e-MS UID] as [UID],

    MSP_EpmTask_UserView.[e-Spec] as [Spec] ,

    MSP_EpmTask_UserView.[e-Body build Location] as [Body Build] ,

    MSP_EpmTask_UserView.[e_Tendor Category] as [Tender Cat.] ,

    MSP_EpmTask_UserView.[e_CME Config] as [e_Config],

    MSP_EpmTask_UserView.[e_CME Eng] as [e_Eng],

    MSP_EpmTask_UserView.[e-CME Comments] as [Comments] FROM

    dbo.MSP_EpmProject_UserView INNER JOIN dbo.MSP_EpmTask_UserView ON

    MSP_EpmProject_UserView.ProjectUID = MSP_EpmTask_UserView.ProjectUID

    WHERE dbo.MSP_EpmTask_UserView.[e-MS UID] IN ('B0AC','B012','B010','B008','B0CH','B0AR')

    AND dbo.MSP_EpmTask_UserView.TaskFinishDate BETWEEN

    DATEADD(Week,-1, GETDATE()) and DATEADD(Week,+4, GETDATE())

    AND dbo.MSP_EpmTask_UserView.[e-CME MS Status]='0- Open'

    ORDER BY MSP_EpmProject_UserView.ProjectName, MSP_EpmTask_UserView.TaskFinishDate,

    datepart(week,MSP_EpmTask_UserView.TaskFinishDate),

    MSP_EpmTask_UserView.[e-Vehicle Type], MSP_EpmTask_UserView.[E-Section ID],

    MSP_EpmTask_UserView.[e-MS UID]


    in addition to the above I need to add in a column (if it's possible!) to pull though the dbo.MSP_EpmTask_UserView.TaskFinishDate  as [Into Line] (this has an e-MS UID of B0IB for each of the MSP_EpmTask_UserView.[E-Section ID] as [Cont.] returned.

    Effectively the statement should be do all of the above SQL AND  for each  MSP_EpmTask_UserView.[E-Section ID] return  TaskFinishDate where e-MS UID = B0IB

    Any suggestions?

    Thanks



    • Edited by orangebloss Tuesday, January 26, 2016 4:14 PM
    Tuesday, January 26, 2016 4:05 PM

All replies

  • Hi Orangebloss,

    Check this updated query. Highlighted changes with Red color. Hope this helps you.

    SELECT             

    datepart(iso_week,MSP_EpmTask_UserView.TaskFinishDate) as [Week] ,

    MSP_EpmTask_UserView.TaskFinishDate  as [Into Line],

    MSP_EpmTask_UserView.[e-Vehicle Type] as [Vehicle],

    MSP_EpmTask_UserView.[E-Section ID] as [Cont.],

    MSP_EpmTask_UserView.[e-WPO] as [WPO],   

    MSP_EpmTask_UserView.[e-Customer] as [Customer],   

    MSP_EpmTask_UserView.[e-MS UID] as [UID],      

    MSP_EpmTask_UserView.[e-Spec] as [Spec]  ,

    MSP_EpmTask_UserView.[e-Body build Location] as [Body Build] , 

    MSP_EpmTask_UserView.[e_Tendor Category] as [Tender Cat.] ,

    MSP_EpmTask_UserView.[e_CME Config] as [e_Config],

    MSP_EpmTask_UserView.[e_CME Eng] as [e_Eng],

    MSP_EpmTask_UserView.[e-CME Comments] as [Comments]

    FROM               

    dbo.MSP_EpmProject_UserView INNER JOIN dbo.MSP_EpmTask_UserView ON                   

    MSP_EpmProject_UserView.ProjectUID = MSP_EpmTask_UserView.ProjectUID  

    WHERE  

    dbo.MSP_EpmTask_UserView.[e-MS UID] IN (‘B0IB’,'B0AC','B012','B010','B008','B0CH','B0AR')   

    AND dbo.MSP_EpmTask_UserView.TaskFinishDate BETWEEN  DATEADD(Week,-1, GETDATE()) and DATEADD(Week,+4, GETDATE())

    AND dbo.MSP_EpmTask_UserView.[e-CME MS Status]='0- Open' 

    ORDER BY MSP_EpmProject_UserView.ProjectName, MSP_EpmTask_UserView.TaskFinishDate,

    datepart(week,MSP_EpmTask_UserView.TaskFinishDate),     

     MSP_EpmTask_UserView.[e-Vehicle Type], MSP_EpmTask_UserView.[E-Section ID], 

    MSP_EpmTask_UserView.[e-MS UID]


    Cheers, Badal Ratra MCTS

    Wednesday, January 27, 2016 9:53 PM