Replace while loop with set-based approach in SQL Server

Question

I have this table which displays providers and total active clients on each day.

DailyClientPopulation table:

Provider   Activeclients DateAdded
-------    ------------- ---------
p1          10           2016-11-01
p1          15           2016-11-02
p2          14           2016-11-01
.
.
p1          70           2016-11-30
p2          50           2016-11-30

The result should be like this.means we need to display average number of client placed on first half and second half of every month.

TEMPBIWEEKLYCENSUS table:

Provider    Avg(activeclients)  Biweeklyrange
--------    -----------------   -------------
p1          30                   11/01-11/15
p2          20                   11/01-11/15  
p1          40                   11/15-11/30
p2          30                   11/15-11/30

I am using while loop to display the results.and updating the start date and current end date values. for ex: for first half of november, start date=11/01 and current end date=11/15.

End date=end of the month.

This is the code:

DECLARE @STARTDATE DATETIME
DECLARE @ENDDATE DATETIME
DECLARE @CURRENTENDDATE DATETIME
DECLARE @MONTHLASTDATE DATETIME
DECLARE @DAYSTOADD INT
DECLARE @TEMPSTARTDATE DATETIME

SET @STARTDATE= CONVERT(DATE, DATEADD(DAY, -@NoOfCharts*15,GETDATE()))
--PRINT @STARTDATE
SET @STARTDATE  = DATEADD(MONTH,DATEDIFF(MONTH, 0, @STARTDATE),0 )
--PRINT @STARTDATE
SET @ENDDATE = CONVERT(DATE,DATEADD(MONTH,1,GETDATE()))
SET @ENDDATE = DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, @ENDDATE),0))
--PRINT @ENDDATE

DECLARE @TEMPBIWEEKLYCENSUS table (ProviderName NVARCHAR(500), ActiveClients  INT, BiWeeklyRange NVARCHAR(50)  );
-- SET @MONTHLASTDATE =DATEADD(DAY,-1, DATEADD(MONTH,1,@STARTDATE))

WHILE (@STARTDATE <= @ENDDATE)
BEGIN
    SET @MONTHLASTDATE = DATEADD(DAY, -1, DATEADD(MONTH, 1, DATEADD(MONTH, DATEDIFF(MONTH, 0, @STARTDATE), 0)))
    -- PRINT DATEDIFF(DAY,@STARTDATE, @MONTHLASTDATE)

    IF DATEDIFF(DAY, @STARTDATE, @MONTHLASTDATE) > 15
    BEGIN 
        IF DATEDIFF(DAY, @STARTDATE, @MONTHLASTDATE) / 2 = 15
        BEGIN
            SET @DAYSTOADD = 15
        END
        ELSE 
        BEGIN 
            SET @DAYSTOADD = 14
        END 
    END
    ELSE IF DATEDIFF(DAY, @STARTDATE, @MONTHLASTDATE) < 15
    BEGIN  
        SET @DAYSTOADD = DATEDIFF(DAY,@STARTDATE, @MONTHLASTDATE)
    END  

    SET @CURRENTENDDATE = CONVERT(DATE,DATEADD(DAY,@DAYSTOADD,@STARTDATE))
    --PRINT '**************************************'
    --PRINT 'STARTDATE'
    --PRINT @STARTDATE
    --PRINT 'CURRENTENDDATE'
    --PRINT @CURRENTENDDATE 
    --PRINT '**************************************'

    INSERT INTO @TEMPBIWEEKLYCENSUS 
        SELECT 
            [ProviderName], 
            AVG(ActiveClients),  
            CONVERT(VARCHAR(10), DATEPART(MONTH, @STARTDATE)) + '/' + CONVERT(VARCHAR(10), DATEPART(DAY, @STARTDATE)) + '-' +  CONVERT(VARCHAR(10), DATEPART(MONTH, @CURRENTENDDATE)) + '/' + CONVERT(VARCHAR(10), DATEPART(DAY, @CURRENTENDDATE))   
        FROM 
            [dbo].[DailyClientPopulation] 
        WHERE 
            CONVERT(DATE, DateAdded) >= @STARTDATE 
            AND CONVERT(DATE, DateAdded) <= @CURRENTENDDATE
        GROUP BY 
            ProviderName

    SET @STARTDATE = CONVERT(DATE,DATEADD(DAY,1,@CURRENTENDDATE))
END

SELECT 
    ProviderName, ActiveClients, BiWeeklyRange  
FROM 
    @TEMPBIWEEKLYCENSUS

Could you please suggest how to remove this while loop and convert the code to set-based approach.

Answer 1

You can cross apply some subqueries to your dbo.DailyClientPopulation using the DateAdded date values to produce BiWeekly begin and end dates, and then include between logic in the where clause. I have added a few months of sample data to a temp table to show how the date functions divide the months into BiWeekly ranges:

-- insert sample data
if object_id('tempdb..#DailyClientPopulation') is not null
    drop table #DailyClientPopulation
go
create table #DailyClientPopulation
    (
    Provider char(2),
    Activeclients int,
    DateAdded datetime
    )
insert into #DailyClientPopulation
    values
        ('p1',10,'2016-11-01'),
        ('p1',15,'2016-11-02'),
        ('p2',14,'2016-11-01'),
        ('p1',70,'2016-11-30'),
        ('p2',50,'2016-11-30'),
        ('p1',10,'2016-12-01'),
        ('p1',15,'2016-12-02'),
        ('p2',14,'2016-12-01'),
        ('p1',70,'2016-12-30'),
        ('p2',50,'2016-12-30'),
        ('p1',10,'2017-01-01'),
        ('p1',15,'2017-01-02'),
        ('p2',14,'2017-01-01'),
        ('p1',70,'2017-01-30'),
        ('p2',50,'2017-01-30'),
        ('p1',10,'2017-02-01'),
        ('p1',15,'2017-02-02'),
        ('p2',14,'2017-02-01'),
        ('p1',70,'2017-02-28'),
        ('p2',50,'2017-02-28')

-- return AvgActiveClients per BiWeeklyRange
select
    dcp.Provider,
    avg(dcp.ActiveClients) as AvgActiveClients,
    convert(varchar(10),bed.begin_date,101) + ' - ' + convert(varchar(10),bed.end_date,101) as BiWeeklyRange
from #DailyClientPopulation dcp
    cross apply (values(dateadd(mm,datediff(mm,0,dcp.DateAdded),0))) bom(bom_date) -- begin of month
    cross apply (values(dateadd(dd,-1,dateadd(mm,1,bom.bom_date)))) eom(eom_date) -- end of month
    cross apply (values(dateadd(dd,day(eom.eom_date)/2,bom.bom_date))) bosh(bosh_date) -- begin of second half
    cross apply (values(dateadd(dd,-1,bosh.bosh_date))) eofh(eofh_date) -- end of first half
    cross apply (values(bom.bom_date,eofh.eofh_date),
                       (bosh.bosh_date,eom.eom_date)) bed(begin_date,end_date) -- begin / end dates
where dcp.DateAdded between bed.begin_date and bed.end_date
group by
    dcp.Provider,
    bed.begin_date,
    bed.end_date
order by
    bed.begin_date,
    dcp.Provider