Declare
    @ReportDate as DATE
    ,@month as DATE
    ,@dayCounter as INT
    ,@monthCounter as INT
    ,@reportDateCurrentMonth as DATE


SET @ReportDate = sysdatetime()
SET @month = @ReportDate
SET @dayCounter = DATEPART(DAY,@ReportDate)
SET @monthCounter = '0'
SET @reportDateCurrentMonth = DATEADD(month,@monthCounter,@ReportDate)

CREATE TABLE #DailyVolumes
    (
       contract_name varchar(50)
       ,Volume INT
       ,date_registered DATETIME
    )

WHILE @month > DATEADD(month,-15,@ReportDate)
    BEGIN

          WHILE @dayCounter > '0'

          BEGIN

                INSERT INTO #DailyVolumes
                SELECT
                CONCAT('PREFIX-',DWRccn.contract_name) AS contract_name
                    ,count(distinct(CONCAT(rtrim(p.xxxx_id), '-', rtrim(r.lis_req_id)))) AS Volume
                    ,a.date_registered
                FROM accession a
                    Left  join value1 P on a.value1_id = p.value1_id
                    left  join [DataWarehouseReporting].[dbo].[DIM_contract_code_name] DWRccn on a.contract_code = DWRccn.contract_code
                where
                    [date_registered] = @reportDateCurrentMonth
                    AND a.lis_code = 'S'
                    AND visit_type IN ('I', 'E')
                GROUP BY
                    CONCAT('PREFIX-',DWRccn.contract_name)
                    ,a.date_registered


                -- Set new values on Daily counter & Date to grab
                SET @dayCounter = @dayCounter - '1'
                SET @reportDateCurrentMonth = DATEADD(day,-1,@reportDateCurrentMonth)
          END;

       SET @monthCounter = @monthCounter - '1'
       SET @month = DATEADD(month,-1,@month)
       SET @reportDateCurrentMonth = DATEADD(month,@monthCounter,@ReportDate)
       SET @dayCounter = DATEPART(DAY,@ReportDate)




    END;



-- SUM Daily Data into Monthly Slices
Select
    sum(Volume) AS Volume
    ,contract_name
    ,DATEADD(MONTH, DATEDIFF(MONTH, 0, date_registered), 0) AS MonthRegistered
FROM #DailyVolumes
group by
    contract_name
    ,DATEADD(MONTH, DATEDIFF(MONTH, 0, date_registered), 0)
ORDER BY
    MonthRegistered DESC
    ,contract_name
-- Clear Temp Table
DROP TABLE #DailyVolumes