UNION ALL 和 Order by 同用

时间:2022-02-28 15:45:38


Create PROCEDURE [dbo].[usp_JRM_JR_History]     
 (
 @accountID INT,
 @entityID INT,
 @jRID  INT
)
AS     
SET NOCOUNT ON     

BEGIN

 DECLARE @TemplateID  INT
 
 SELECT @TemplateID=WorkflowTemplateID FROM JRM_JR_Info WHERE AccountID=@AccountID AND EntityID=@EntityID AND JRID=@jRID
 SELECT *FROM (
  SELECT JJF.JRID AS JRID,DateAdd(hour,8,JJF.CreatedDate) AS ActionDate,RequestName AS HandledBy,dbo.UDF_JRM_JR_GetActionStatusName(JJF.ActionID) AS ActionStatus,dbo.UDF_JRM_JR_GetStageName(@accountID,@entityID,JWD.JRStageID) AS StageID
  FROM JRM_JR_ForwardDetails JJF
  LEFT JOIN JRM_WorkflowTemplate_Details JWD
  ON JJF.AccountID=JWD.AccountID AND JJF.EntityID=JWD.EntityID AND JWD.TemplateID=@TemplateID AND JJF.JRID=@JRID AND JJF.StepID=JWD.StepID
  WHERE JJF.AccountID=@AccountID AND JJF.EntityID=@EntityID AND JJF.JRID=@JRID
  UNION ALL
  SELECT JRID,DateAdd(hour,8,ISNULL(FinalApproveDate,LastModifiedDate))AS ActionDate,FinalApproverName AS HandledBy,dbo.UDF_JRM_JR_GetActionStatusName(5) AS ActionStatus,dbo.UDF_JRM_JR_GetStageName(@accountID,@entityiD,StageID) AS StageID
   FROM JRM_JR_Info WITH (NOLOCK) WHERE AccountID=@accountID AND EntityID=@entityID AND JRID=@jRID AND ISNULL(FinalApproverID,0) <>0
  UNION  ALL
  SELECT JRID,DateAdd(hour,8,ISNULL(TransferDate,LastModifiedDate))AS ActionDate,TransferorName AS HandledBy,dbo.UDF_JRM_JR_GetActionStatusName(12)AS ActionStatus,dbo.UDF_JRM_JR_GetStageName(@accountID,@entityID,StageID)AS StageID
   FROM JRM_JR_Info WITH(NOLOCK) WHERE AccountID=@accountID AND EntityID=@entityID AND JRID=@jRID AND ISNULL(TransferorID,0) <> 0) AS History
  Order by History.ActionDate ASC
END