Вычесть общее количество из столбца в предыдущей строке, используя SQL

Question

У меня есть следующий код:

create table #attr( enroll_month datetime ,cncl_mth datetime,
mth int,
tot_orders int,tot_cancel int, active_count int, attr_rate int , retn_rate 
int
)

DECLARE
@enroll_mth datetime ,@cncl_mth datetime, @mth int ,
@tot_orders numeric, @tot_cancel numeric,
@attr_rate numeric(6,2), @retn_rate numeric(6,2),
@active_count int
DECLARE att_cursor CURSOR FOR
SELECT
d.Enroll_Month, d.cncl_mth, d.mth,
s.tot_orders, d.tot_cancel
FROM #Summary s with (nolock),
#Detail d with (nolock)
WHERE
s.Enroll_Month = d.Enroll_Month


OPEN att_cursor
FETCH NEXT FROM att_cursor INTO @enroll_mth, @cncl_mth, @mth, @tot_orders, 
@tot_cancel

DECLARE
@old_enroll_mth datetime,
@old_cncl_mth datetime, @old_mth int, @month datetime ,
@intial varchar(1),
@old_active_cnt int, @old_tot_cancel int,
@old_retn_rate numeric(6,2), @old_attr_rate numeric(6,2),
@counter int

SELECT @old_enroll_mth = ''
SELECT @intial = 'Y'

WHILE @@FETCH_STATUS = 0
BEGIN
IF (@old_enroll_mth <> @enroll_mth)
BEGIN
SELECT @active_count = @active_count - @tot_cancel
SELECT @intial = 'N'
END
ELSE
BEGIN
SELECT @active_count = @tot_orders - @tot_cancel
SELECT @intial = 'Y'
END

SELECT @retn_rate = (@active_count / @tot_orders) * 100
SELECT @attr_rate = 100 - @retn_rate

INSERT INTO #Attr (
 enroll_month, cncl_mth, mth, tot_orders, tot_cancel, 
active_count, attr_rate, retn_rate ) 
VALUES (
@enroll_mth, @cncl_mth, @mth, @tot_orders, @tot_cancel,
@active_count, @attr_rate, @retn_rate)





SELECT @old_enroll_mth = @enroll_mth
SELECT @old_mth = @mth
SELECT @old_retn_rate = @retn_rate
SELECT @old_attr_rate = @attr_rate
SELECT @old_active_cnt = @active_count
SELECT @old_cncl_mth = @cncl_mth
SELECT @old_tot_cancel = @tot_cancel

FETCH NEXT FROM att_cursor INTO @enroll_mth, @cncl_mth, @mth, @tot_orders, 
@tot_cancel
END

CLOSE att_cursor
DEALLOCATE att_cursor

select * from #attr

который возвращает приведенный ниже вывод.

enroll_month cncl_mth mth tot_orders tot_cancel active_count attr_rate retn_rate
01/01/17    01/01/17    1   390         160        230           41       58
01/01/17   02/01/17     2   390          26        364            6       93
01/01/17    03/01/17    3   390          23         594          -52      152

Он отображает правильное значение для активного счета для mth=1 . Хотя для mth = 2 это должно быть (230-26=204), а для mth = 3 оно должно быть (204-23 =181).

Я опубликовал два сценария для заполнения сводных и подробных таблиц, которые используются в моем сценарии выше.

create table #summary
(
enroll_month datetime,
tot_orders int
)
go
insert into #summary(enroll_month, tot_orders)
values ('2017-01-01 00:00:00.000', 390)

insert into #summary(enroll_month, tot_orders )
values ('2017-02-01 00:00:00.000', 615)
go

drop table #Detail
go
create table #detail
(
enroll_month datetime,
cncl_mnth datetime,
mth int,
tot_cancel int
)
go
insert into #detail(enroll_month,cncl_mnth,mth,tot_cancel)
values ('2017-01-01 00:00:00.000', '2017-01-01 00:00:00.000', 1, 160)

insert into #detail(enroll_month,cncl_mnth,mth,tot_cancel )
values ('2017-01-01 00:00:00.000','2017-02-01 00:00:00.000', 2, 26)

insert into #detail(enroll_month,cncl_mnth,mth,tot_cancel)
values ('2017-01-01 00:00:00.000','2017-03-01 00:00:00.000', 3, 23)
go

Можете ли вы помочь?

Спасибо Пэм

Answer 1

Ваша проблема в этой части:

IF (@old_enroll_mth <> @enroll_mth)
BEGIN
SELECT @active_count = @active_count - @tot_cancel
SELECT @intial = 'N'
END
ELSE
BEGIN
SELECT @active_count = @tot_orders - @tot_cancel
SELECT @intial = 'Y'
END

вы перевернули логику, в которой вы нуждались;).. поэтому вам нужно, чтобы она была такой:

IF (@old_enroll_mth <> @enroll_mth)
BEGIN
    SELECT @active_count = @tot_orders - @tot_cancel
    SELECT @intial = 'Y'
END
ELSE
BEGIN
    SELECT @active_count = @active_count - @tot_cancel
    SELECT @intial = 'N'
END

ОБНОВЛЕНО

ПРИМЕР ДАННЫХ

DECLARE @summary TABLE (enroll_month datetime,  tot_orders int)
DECLARE @detail TABLE (enroll_month datetime, cncl_mnth datetime, mth int, tot_cancel int)
DECLARE @attr TABLE (enroll_month datetime ,cncl_mth datetime, mth int, tot_orders int,tot_cancel int, active_count int, attr_rate int , retn_rate int)

insert into @summary(enroll_month, tot_orders) values 
('2017-01-01 00:00:00.000', 390),
('2017-02-01 00:00:00.000', 615)

insert into @detail(enroll_month,cncl_mnth,mth,tot_cancel) values 
('2017-01-01 00:00:00.000', '2017-01-01 00:00:00.000', 1, 160),
('2017-01-01 00:00:00.000','2017-02-01 00:00:00.000', 2, 26),
('2017-01-01 00:00:00.000','2017-03-01 00:00:00.000', 3, 23)

Используя приведенный выше пример, и ваш CURSOR (с изменением вышеуказанной логики - перевернуто):

DECLARE
@enroll_mth datetime ,@cncl_mth datetime, @mth int ,
@tot_orders numeric, @tot_cancel numeric,
@attr_rate numeric(6,2), @retn_rate numeric(6,2),
@active_count int


DECLARE att_cursor CURSOR FOR
    SELECT
    d.Enroll_Month, d.cncl_mnth, d.mth,
    s.tot_orders, d.tot_cancel
    FROM @summary s, @Detail d
    WHERE
    s.Enroll_Month = d.Enroll_Month


OPEN att_cursor
FETCH NEXT FROM att_cursor INTO @enroll_mth, @cncl_mth, @mth, @tot_orders, @tot_cancel

    DECLARE
    @old_enroll_mth datetime,
    @old_cncl_mth datetime, @old_mth int, @month datetime ,
    @intial varchar(1),
    @old_active_cnt int, @old_tot_cancel int,
    @old_retn_rate numeric(6,2), @old_attr_rate numeric(6,2),
    @counter int

SELECT @old_enroll_mth = ''
SELECT @intial = 'Y'

WHILE @@FETCH_STATUS = 0
BEGIN
IF (@old_enroll_mth <> @enroll_mth)
BEGIN
    SELECT @active_count = @tot_orders - @tot_cancel
    SELECT @intial = 'Y'
END
ELSE
BEGIN
    SELECT @active_count = @active_count - @tot_cancel
    SELECT @intial = 'N'
END

SELECT @retn_rate = (@active_count / @tot_orders) * 100
SELECT @attr_rate = 100 - @retn_rate

INSERT INTO @attr (enroll_month, cncl_mth, mth, tot_orders, tot_cancel, active_count, attr_rate, retn_rate ) 
           VALUES (@enroll_mth, @cncl_mth, @mth, @tot_orders, @tot_cancel, @active_count, @attr_rate, @retn_rate)

SELECT @old_enroll_mth = @enroll_mth
SELECT @old_mth = @mth
SELECT @old_retn_rate = @retn_rate
SELECT @old_attr_rate = @attr_rate
SELECT @old_active_cnt = @active_count
SELECT @old_cncl_mth = @cncl_mth
SELECT @old_tot_cancel = @tot_cancel

FETCH NEXT FROM att_cursor INTO @enroll_mth, @cncl_mth, @mth, @tot_orders, @tot_cancel
END

CLOSE att_cursor
DEALLOCATE att_cursor

SELECT * FROM @attr

Если вы хотите принять другой метод без CURSOR или же CTE, тогда вы можете использовать это вместо:

SELECT
    D.Enroll_Month
,   cncl_mnth
,   mth
,   tot_orders
,   tot_cancel
,   tot_orders - total_cancel active_count
,   100 - CAST(CAST((tot_orders - total_cancel) AS DECIMAL(18,2) ) / (tot_orders) * 100  AS DECIMAL(18,2) )  attr_rate
,   CAST(CAST((tot_orders - total_cancel) AS DECIMAL(18,2) ) / (tot_orders) * 100  AS DECIMAL(18,2) ) retn_rate
FROM (
    SELECT
        d.Enroll_Month
    ,   d.cncl_mnth
    ,   d.mth
    ,   s.tot_orders
    ,   d.tot_cancel
    ,   SUM(d.tot_cancel) OVER(PARTITION BY d.Enroll_Month ORDER BY mth ROWS UNBOUNDED PRECEDING) total_cancel
    FROM @summary s
    JOIN @Detail d ON s.Enroll_Month = d.Enroll_Month
) D

Answer 2

Это полезно?

    create table #summary ( enroll_month datetime, tot_orders int ) 
    insert into #summary(enroll_month, tot_orders) values ('2017-01-01 00:00:00.000', 390)
    insert into #summary(enroll_month, tot_orders ) values ('2017-02-01 00:00:00.000', 615) 


    create table #detail ( enroll_month datetime, cncl_mnth datetime, mth int, tot_cancel int ) 
    insert into #detail(enroll_month,cncl_mnth,mth,tot_cancel) values ('2017-01-01 00:00:00.000', '2017-01-01 00:00:00.000', 1, 160)
    insert into #detail(enroll_month,cncl_mnth,mth,tot_cancel ) values ('2017-01-01 00:00:00.000','2017-02-01 00:00:00.000', 2, 26)
    insert into #detail(enroll_month,cncl_mnth,mth,tot_cancel) values ('2017-01-01 00:00:00.000','2017-03-01 00:00:00.000', 3, 23)



    SELECT
    d.Enroll_Month, d.cncl_mnth, d.mth,
    s.tot_orders, d.tot_cancel
    into #tmp
    FROM #Summary s with (nolock),
    #Detail d with (nolock)
    WHERE
    s.Enroll_Month = d.Enroll_Month

    ;with cte
    As
    (
        Select Enroll_Month,cncl_mnth,mth,tot_orders,tot_cancel,
        tot_orders-tot_cancel as active_count,
        FLOOR(100-(CAST((tot_orders-tot_cancel) AS FLOAT)/CAST(tot_orders AS FLOAT))*100) as attr_rate, 
        FLOOR((CAST((tot_orders-tot_cancel) AS FLOAT)/CAST(tot_orders AS FLOAT))*100) as retn_rate
        from #tmp where mth=1

        Union All

        Select t.Enroll_Month,t.cncl_mnth,t.mth,t.tot_orders,t.tot_cancel,
        c.active_count-t.tot_cancel as active_count,
        FLOOR(100-(CAST((c.active_count-t.tot_cancel) AS FLOAT)/CAST(c.active_count AS FLOAT))*100) as attr_rate,   
        FLOOR((CAST((c.active_count-t.tot_cancel) AS FLOAT)/CAST(c.active_count AS FLOAT))*100) as retn_rate
        from #tmp t
        JOIN cte c on t.mth=c.mth+1
    )

    select * from cte

    Drop Table #tmp
    Drop table #summary
    Drop table #detail

вывод:

Enroll_Month                    cncl_mnth          mth  tot_orders  tot_cancel  active_count    attr_rate   retn_rate
2017-01-01 00:00:00.000  2017-01-01 00:00:00.000    1      390         160         230               41          58
2017-01-01 00:00:00.000  2017-02-01 00:00:00.000    2      390         26          204               11          88
2017-01-01 00:00:00.000  2017-03-01 00:00:00.000    3      390         23          181               11          88

Answer 3

Я хотел бы использовать общее табличное выражение (CTE) и получить общее с SUM и более пункт.

SQL:

WITH
summary
AS
(
    SELECT tbl.* FROM (VALUES
      ( '01-Jan-2017', 390)
    , ( '01-Feb-2017', 615)
    ) tbl ([enroll_month], [tot_orders]) 
)
, 
details
AS
(
    SELECT tbl.* FROM (VALUES
      ( '01-Jan-2017', '01-Jan-2017', 1, 160)
    , ( '01-Jan-2017', '01-Feb-2017', 2, 26)
    , ( '01-Jan-2017', '01-Mar-2017', 3, 23)
    ) tbl ([enroll_month], [cncl_mnth], [mth], [tot_cancel]) 
)
,
detail_active_count
AS
(
    SELECT
          d.[enroll_month]
        , d.[cncl_mnth]
        , s.[tot_orders]
        , d.[mth]
        , d.[tot_cancel]
        , [active_count] = s.[tot_orders] - SUM(d.[tot_cancel]) OVER (PARTITION BY d.[enroll_month] ORDER BY d.[mth]) 
    FROM 
        summary AS s
        INNER JOIN details as d ON d.[enroll_month] = s.[enroll_month]
)
SELECT
      c.* 
    , [attr_rate] = FLOOR(100 - (CAST((c.[active_count]) AS FLOAT) / CAST(c.[tot_orders] AS FLOAT)) * 100)
    , [retn_rate] = FLOOR((CAST((c.[active_count]) AS FLOAT) / CAST(c.[tot_orders] AS FLOAT)) * 100)
FROM 
    detail_active_count AS c

Результаты:

Пример плана выполнения: