sql-server-2012 - 在 SQL Server 中用基于集合的方法替换 while 循环

Question

我有这张表，它显示了每天的提供者和活跃客户总数。
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

结果应该是这样的。意味着我们需要显示每个月上半月和下半月的平均客户数。
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

我正在使用 while 循环来显示结果。并更新开始日期和当前结束日期值。例如:11 月上半月，开始日期=11/01，当前结束日期=11/15。

结束日期=月底。

这是代码:

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

您能否建议如何删除此 while 循环并将代码转换为基于集合的方法。

Answer 1

您可以将一些子查询交叉应用于您的 dbo.DailyClientPopulation使用 DateAdded日期值以生成 BiWeekly 开始和结束日期，然后在 where 子句中包含 between 逻辑。我已将几个月的示例数据添加到临时表中，以显示日期函数如何将月份划分为 BiWeekly 范围:

-- 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