SELECT
        [S].[Acronym] AS StudyAcronym
      , [O].[Name] AS OrganisationName
      , ( CASE WHEN [I].[LastName] IS NULL THEN [I].[FirstName]
               WHEN [I].[FirstName] IS NULL THEN [I].[LastName]
               ELSE [I].[LastName] + ', ' + [I].[FirstName]
          END ) AS InvestigatorName
      , [AcS].[SiteName] AS AccrualSiteName
      , [m].[StudyEntryYear]
      , [m].[StudyEntryMonth]
      , ISNULL([AccrualFigures].[Accrual], 0) AS Accrual
    FROM
        (
          -- This subquery gets details of all investigators to feature in this report, irrespective of whether they have any accrual for the chosen year.
              SELECT DISTINCT
                [ABI].[StudyId]
              , [ABI].[InvestigatorId]
              , [ABI].[AccrualSiteId]
              , [ABI].[OrganisationId]
              FROM
                ukcrn_reporting.dbo.AccrualByInvestigator ABI
                INNER JOIN @studyInvestigators SI ON ( [ABI].[StudyId] = [SI].[StudyId]
                                                       AND [ABI].[InvestigatorId] = [SI].[InvestigatorId]
                                                       AND [ABI].[AccrualSiteId] = [SI].[AccrualSiteID]
                                                     )
                INNER JOIN @Studies S ON [ABI].[StudyId] = [S].[StudyId]
                INNER JOIN @Lrns L ON [ABI].[OrganisationId] = [L].[LrnId]
              WHERE
                [ABI].[TopicId] = @topicId
                OR @topicID = -1
        ) AllInvestigators
        CROSS JOIN @months m
        LEFT OUTER JOIN (
                          -- This subquery gets details of investigators who have actually recruited during the year
                              SELECT
                                [ABI].[StudyId]
                              , [ABI].[InvestigatorId]
                              , [ABI].[AccrualSiteId]
                              , [ABI].[OrganisationId]
                              , DATEPART(yy, [ABI].[StudyEntryDate]) StudyEntryYear
                              , DATEPART(mm, [ABI].[StudyEntryDate]) StudyEntryMonth
                              , COUNT(*) AS Accrual
                              FROM
                                ukcrn_reporting.dbo.AccrualByInvestigator ABI
                                INNER JOIN @Studies S ON [ABI].[StudyId] = [S].[StudyId]
                                INNER JOIN @lrns L ON [ABI].[OrganisationId] = [L].[LrnId]
                                INNER JOIN @studyInvestigators SI ON ( [ABI].[StudyId] = [SI].[StudyId]
                                                                       AND [ABI].[InvestigatorId] = [SI].[InvestigatorId]
                                                                       AND [ABI].[AccrualSiteId] = [SI].[AccrualSiteID]
                                                                     )
                                INNER JOIN @recruitTypes RT ON [ABI].[RecruitType] = [RT].[RecruitTypeId]
                              WHERE
                                [ABI].[StudyEntryDate] BETWEEN @DateFrom AND @DateTo
                                AND ( [ABI].[TopicId] = @TopicID
                                      OR @TopicID = -1
                                    )
                              GROUP BY
                                [ABI].[StudyId]
                              , [ABI].[InvestigatorId]
                              , [ABI].[AccrualSiteId]
                              , [ABI].[OrganisationId]
                              , DATEPART(yy, [ABI].[StudyEntryDate])
                              , DATEPART(mm, [ABI].[StudyEntryDate])
                        ) AccrualFigures ON ( [AllInvestigators].[StudyId] = [AccrualFigures].[StudyId]
                                              AND [AllInvestigators].[InvestigatorId] = [AccrualFigures].[InvestigatorId]
                                              AND [AllInvestigators].[AccrualSiteId] = [AccrualFigures].[AccrualSiteId]
                                              AND [AllInvestigators].[OrganisationId] = [AccrualFigures].[OrganisationId]
                                              AND [m].[StudyEntryYear] = [AccrualFigures].[StudyEntryYear]
                                              AND [m].[StudyEntryMonth] = [AccrualFigures].[StudyEntryMonth]
                                            )
        INNER JOIN [dbo].[Study] S ON [AllInvestigators].[StudyId] = [S].[Id]
        INNER JOIN [dbo].[Investigator] I ON [AllInvestigators].[InvestigatorId] = [I].[Id]
        INNER JOIN [dbo].[Organisation] O ON [AllInvestigators].[OrganisationId] = [O].[Id]
        INNER JOIN [dbo].[AccrualSite] AcS ON [AllInvestigators].[AccrualSiteId] = [AcS].[Id]
    ORDER BY
        [S].[Acronym]
      , [O].[Name]
      , [AcS].[SiteName]

quote:

---

Originally posted by Kwisatz

WIll do , in this case there are only 5 columns so should be ok I guess?

---