how can i make this proc better?

i want to know how i can rewrite this SQL into a single select using joins. i have a long drawn out way as seen below to basically get the min date of a "project incep开发者_C百科tion milestone" and max date for a "production go-live" milestone.

some background is the sql is for a project management application that tracks projects milestones against a release baseline set of milestones. I need to have a proc that takes a CSV list of projectIDs and have it select the min startDate for the project inception milestone (StatusCode.cid =37) and the max production go-live milestone(StatusCode.cid =77)

here is my dummy SQL i have working now:

    CREATE PROC rpt_rainbow  
 @ProjectIDs NVARCHAR(1000)  
AS  

DECLARE @MinBRSProjectStartDate DATETIME  
DECLARE @MinBRSReleaseStartDate DATETIME  

DECLARE @MaxProdProjectEndDate DATETIME  
DECLARE @MaxProdReleaseEndDate DATETIME  

SELECT @MinBRSProjectStartDate = MIN (pm.startDate)   
FROM StatusCode sc      
 INNER JOIN ProjectMilestone pm ON sc.CID = pm.MilestoneCID AND pm.ProjectID IN (SELECT value FROM fn_Split(@ProjectIDs, ','))   
WHERE sc.cid =37  

SELECT @MinBRSReleaseStartDate = MIN(rel.startDate)  
FROM Project p  
 INNER JOIN ReleaseSchedule rel ON rel.ReleaseID = p.ReleaseID AND rel.milestonecid IN (37)   
WHERE ProjectId IN (SELECT value FROM fn_Split(@ProjectIDs, ','))  

SELECT @MaxProdProjectEndDate = MAX (pm.endDate)   
FROM StatusCode sc      
 INNER JOIN ProjectMilestone pm ON sc.CID = pm.MilestoneCID AND pm.ProjectID IN (SELECT value FROM fn_Split(@ProjectIDs, ','))   
WHERE sc.cid =77  

SELECT @MaxProdReleaseEndDate = MAX(rel.endDate)  
FROM Project p  
 INNER JOIN ReleaseSchedule rel ON rel.ReleaseID = p.ReleaseID AND rel.milestonecid IN (77)   
WHERE ProjectId IN (SELECT value FROM fn_Split(@ProjectIDs, ','))  


select isnull(@MinBRSProjectStartDate, @MinBRSReleaseStartDate) as MinBRS_StartDate,  
    isnull(@MaxProdProjectEndDate, @MaxProdReleaseEndDate) as MaxProd_EndDate  

here is my split function:

CREATE FUNCTION dbo.Split   
   (  @Delimiter varchar(5),   
      @List      varchar(8000)  
   )   
   RETURNS @TableOfValues table   
      (  RowID   smallint IDENTITY(1,1),   
         [Value] varchar(50)   
      )   
AS   
   BEGIN  

      DECLARE @LenString int   

      WHILE len( @List ) > 0   
         BEGIN   

            SELECT @LenString =   
               (CASE charindex( @Delimiter, @List )   
                   WHEN 0 THEN len( @List )   
                   ELSE ( charindex( @Delimiter, @List ) -1 )  
                END  
               )   

            INSERT INTO @TableOfValues   
               SELECT substring( @List, 1, @LenString )  

            SELECT @List =   
               (CASE ( len( @List ) - @LenString )   
                   WHEN 0 THEN ''   
                   ELSE right( @List, len( @List ) - @LenString - 1 )   
                END  
               )   
         END  

      RETURN   

   END   

and here are the definitions for the tables involved:

CREATE TABLE [dbo].[ProjectMilestone](
    [ProjectMilestoneId] [int] NOT NULL,
    [ProjectId] [int] NOT NULL,
    [MilestoneCID] [int] NOT NULL,
    [StartDate] [datetime] NOT NULL,
    [EndDate] [datetime] NOT NULL,
    [RAGStatusCID] [int] NOT NULL,
    [CompletionStatusCID] [int] NOT NULL,
    [StatusText] [nvarchar](max) NOT NULL,
    [ReportingPriority] [int] NULL,
    [Owner] [nvarchar](50) NOT NULL,
    [Added] [datetime] NOT NULL,
    [LastUpdate] [datetime] NOT NULL,
    [UpdateBy] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_ProjectMilestone] PRIMARY KEY CLUSTERED 
(
    [ProjectMilestoneId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[ProjectMilestone]  WITH CHECK ADD  CONSTRAINT [FK_ProjectMilestone_Project] FOREIGN KEY([ProjectId])
REFERENCES [dbo].[Project] ([ProjectId])
GO
ALTER TABLE [dbo].[ProjectMilestone] CHECK CONSTRAINT [FK_ProjectMilestone_Project]


-----------------------------------------------------------------------------------------------

CREATE TABLE [dbo].[Project](
    [ProjectId] [int] NOT NULL,
    [ProjectName] [nvarchar](255) NOT NULL,
    [ProjectRegistration] [nvarchar](50) NOT NULL,
    [CaseManagerBenId] [nvarchar](50) NOT NULL,
    [ClarityId] [nvarchar](50) NOT NULL,
    [ParentProjectId] [int] NULL,
    [ReleaseId] [int] NOT NULL,
    [CompletionStatusCID] [int] NOT NULL,
    [ProjectTypeCID] [int] NOT NULL,
    [Budget] [money] NOT NULL,
    [BusinessObjective] [nvarchar](max) NOT NULL,
    [Benefit] [nvarchar](max) NOT NULL,
    [Added] [datetime] NOT NULL,
    [LastUpdate] [datetime] NOT NULL,
    [UpdateBy] [nvarchar](50) NOT NULL,
    [StakeholderList] [nvarchar](1000) NULL,
 CONSTRAINT [PK_Project] PRIMARY KEY CLUSTERED 
(
    [ProjectId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[Project]  WITH CHECK ADD  CONSTRAINT [FK_Project_Project] FOREIGN KEY([ParentProjectId])
REFERENCES [dbo].[Project] ([ProjectId])
GO
ALTER TABLE [dbo].[Project] CHECK CONSTRAINT [FK_Project_Project]
GO
ALTER TABLE [dbo].[Project]  WITH CHECK ADD  CONSTRAINT [FK_Project_Release] FOREIGN KEY([ReleaseId])
REFERENCES [dbo].[Release] ([ReleaseId])
GO
ALTER TABLE [dbo].[Project] CHECK CONSTRAINT [FK_Project_Release]

--------------------------------------------------------------------------------------------

CREATE TABLE [dbo].[StatusCode](
    [CID] [int] NOT NULL,
    [CodeName] [nvarchar](50) NOT NULL,
    [Description] [nvarchar](max) NOT NULL,
    [SCID] [int] NOT NULL,
    [ReportingPriority] [int] NULL,
 CONSTRAINT [PK_StatusCode] PRIMARY KEY CLUSTERED 
(
    [CID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

--------------------------------------------------------------------------------------------

CREATE TABLE [dbo].[ReleaseSchedule](
    [ReleaseScheduleID] [int] NOT NULL,
    [ReleaseID] [int] NOT NULL,
    [MilestoneCID] [int] NOT NULL,
    [StartDate] [datetime] NOT NULL,
    [EndDate] [datetime] NOT NULL,
    [Added] [datetime] NOT NULL,
    [LastUpdate] [datetime] NOT NULL,
    [UpdateBy] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_ReleaseSchedule] PRIMARY KEY CLUSTERED 
(
    [ReleaseScheduleID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[ReleaseSchedule]  WITH CHECK ADD  CONSTRAINT [FK_ReleaseSchedule_Release] FOREIGN KEY([ReleaseID])
REFERENCES [dbo].[Release] ([ReleaseId])
GO
ALTER TABLE [dbo].[ReleaseSchedule] CHECK CONSTRAINT [FK_ReleaseSchedule_Release]

---

There are two things of note, the first is that you could reduce the 4 queries to 2

SELECT
    @MinBRSProjectStartDate = MIN (CASE WHEN sc.cid=37 then pm.startDate END),
    @MaxProdProjectEndDate = MAX (CASE WHEN sc.cid=77 then pm.endDate END)
FROM StatusCode sc      
 INNER JOIN ProjectMilestone pm ON sc.CID = pm.MilestoneCID
    AND pm.ProjectID IN (SELECT value FROM fn_Split(@ProjectIDs, ','))  
WHERE sc.cid in (37,77)

SELECT
    @MinBRSReleaseStartDate = MIN(CASE WHEN rel.milestonecid=37 then rel.startDate end),
    @MaxProdReleaseEndDate = MAX(CASE WHEN rel.milestonecid=77 then rel.endDate end) 
FROM Project p  
 INNER JOIN ReleaseSchedule rel ON rel.ReleaseID = p.ReleaseID AND rel.milestonecid IN (37,77)
WHERE ProjectId IN (SELECT value FROM fn_Split(@ProjectIDs, ','))  

There is really no way to join between these two sets, so there is no point trying. But you could CROSS JOIN the two single-row results to get all 4 columns in a single select:

SELECT ISNULL(A,C) as MinBRS_StartDate, ISNULL(B,D) AS MaxProd_EndDate
FROM
(    
SELECT
    MIN (CASE WHEN sc.cid=37 then pm.startDate END) A,
    MAX (CASE WHEN sc.cid=77 then pm.endDate END) B
FROM StatusCode sc      
 INNER JOIN ProjectMilestone pm ON sc.CID = pm.MilestoneCID
    AND pm.ProjectID IN (SELECT value FROM fn_Split(@ProjectIDs, ','))  
WHERE sc.cid in (37,77)
) X,
(
SELECT
    MIN(CASE WHEN rel.milestonecid=37 then rel.startDate end) C,
    MAX(CASE WHEN rel.milestonecid=77 then rel.endDate end) D
FROM Project p  
 INNER JOIN ReleaseSchedule rel ON rel.ReleaseID = p.ReleaseID AND rel.milestonecid IN (37,77)
WHERE ProjectId IN (SELECT value FROM fn_Split(@ProjectIDs, ','))) Y

But since you are using ISNULL across the 2 pairs, it may be better to keep the 4 targeted index-able selects and to just subquery them. Since you are using the SPLIT values 4 times, it makes sense to cache it in a temp table. The ISNULL should be smart enough not to need to evaluate the 2nd select once the first returns a value.

declare @ids table (id int)
insert @ids SELECT distinct value FROM fn_Split(',', @ProjectIDs) V

SELECT
    ISNULL(
        (SELECT MIN (pm.startDate)   
            FROM StatusCode sc
            INNER JOIN ProjectMilestone pm ON sc.CID = pm.MilestoneCID
            INNER JOIN @ids I ON pm.ProjectID = I.ID
            WHERE sc.cid =37),
        (SELECT MIN(rel.startDate)  
            FROM Project p  
            INNER JOIN ReleaseSchedule rel ON rel.ReleaseID = p.ReleaseID
            INNER JOIN @ids I ON p.ProjectID = I.ID
            WHERE rel.milestonecid IN (37))) AS MinBRS_StartDate,
    ISNULL(
        (SELECT MAX (pm.endDate)   
            FROM StatusCode sc      
            INNER JOIN ProjectMilestone pm ON sc.CID = pm.MilestoneCID
            INNER JOIN @ids I ON pm.ProjectID = I.ID
            WHERE sc.cid =77),
        (SELECT MAX(rel.endDate)  
            FROM Project p
            INNER JOIN ReleaseSchedule rel ON rel.ReleaseID = p.ReleaseID
            INNER JOIN @ids I ON p.ProjectID = I.ID
            WHERE rel.milestonecid IN (77))) AS MaxProd_EndDate