Subtract total counts from previous row

[ppetronas]

Hello,

I have the following code:

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
--AND d.cncl_mth < '2018-11-01'

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

which is returning the below output.

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

It is displaying the correct value for active count for mth=1 . Though for mth 2 it should be (230-26=204) and for mth 3 it should be (204-23 =181).
How do I about getting it ?

Thanks,
PamPetronas

[harishgg1]

hi

please provide data script

drop table #data
go
create table #data
(
id int,
age varchar(100)
)
go

insert into #data select 1,40
insert into #data select 2,23
go

if we have sample data to play around with
we can provide solution EASILY

[ppetronas]

Hi harishgg1,

Thank you for your response. I have posted the two scripts for the summary and detail tables that is used in my script.

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

Thanks,
Pam

[harishgg1]

Hi Pam

Its also possible to do this using LEAD LAG Functions in 2012
with SQL .. no need for cursors etc

Also possible with SQL .. no need for cursors etc
without using LEAD LAG

I dont have 2012 but I can show

Also possible with SQL .. no need for cursors etc
without using LEAD LAG

Please find my TRYING below

I am able to get the ActiveCount part

The retention , attribute rate I am not able to get
Dont understand what you are trying to do ??

Drop Create Data

USE tempdb 

go 

DROP TABLE #summary 

go 

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

Drop Create Final Table Attr

DROP TABLE #attr
go 

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

Cursor And Output Part

-- #Detail  
DECLARE @enroll_mth DATETIME, 
        @cncl_mth   DATETIME, 
        @mth        INT, 
        @tot_cancel NUMERIC 
        
-- #Summary          
DECLARE @tot_orders NUMERIC 

-- Other Two Calculations         
DECLARE @attr_rate NUMERIC(6, 2), 
        @retn_rate NUMERIC(6, 2) 
        
-- Variable To Do          
DECLARE @active_count INT 
-- Running Variable To Do
DECLARE @run_active_count INT 

-- Cursor Declare          
DECLARE ok_cursor CURSOR FOR 
  SELECT a.enroll_month, 
         a.cncl_mnth, 
         a.mth, 
         a.tot_cancel, 
         b.tot_orders 
  FROM   #detail a 
         JOIN #summary b 
           ON a.enroll_month = b.enroll_month 

-- Open Cursor   
OPEN ok_cursor 

-- Fecth First Time from Cursor   
FETCH next FROM ok_cursor INTO @enroll_mth, @cncl_mth, @mth, @tot_cancel, 
@tot_orders 

WHILE @@FETCH_STATUS = 0 
  BEGIN 
      IF @mth = 1 
        BEGIN 
            SET @active_count = @tot_orders - @tot_cancel 
            SET @run_active_count = @active_count 
        END 
      ELSE 
        BEGIN 
            SET @run_active_count = @run_active_count - @tot_cancel 
        END 

      SELECT @retn_rate = ( @run_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_cancel, 
                    @tot_orders, 
                    @run_active_count, 
                    @attr_rate, 
                    @retn_rate) 

      FETCH next FROM ok_cursor INTO @enroll_mth, @cncl_mth, @mth, @tot_cancel, 
      @tot_orders 
  END 

CLOSE ok_cursor 

DEALLOCATE ok_cursor 

SELECT * FROM #attr

Result

[ppetronas]

@harishgg1

-- #Detail DECLARE @enroll_mth DATETIME, @cncl_mth DATETIME, @mth INT, @tot_cancel NUMERIC -- #Summary DECLARE @tot_orders NUMERIC -- Other Two Calculations DECLARE @attr_rate NUMERIC(6, 2), @retn_rate NUMERIC(6, 2) -- Variable To Do DECLARE @active_count INT -- Running Variable To Do DECLARE @run_active_count INT -- Cursor Declare DECLARE ok_cursor CURSOR FOR SELECT a.enroll_month, a.cncl_mnth, a.mth, a.tot_cancel, b.tot_orders FROM #detail a JOIN #summary b ON a.enroll_month = b.enroll_month -- Open Cursor OPEN ok_cursor -- Fecth First Time from Cursor FETCH next FROM ok_cursor INTO @enroll_mth, @cncl_mth, @mth, @tot_cancel, @tot_orders WHILE @@FETCH_STATUS = 0 BEGIN IF @mth = 1 BEGIN SET @active_count = @tot_orders - @tot_cancel SET @run_active_count = @active_count END ELSE BEGIN SET @run_active_count = @run_active_count - @tot_cancel END SELECT @retn_rate = ( @run_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_cancel, @tot_orders, @run_active_count, @attr_rate, @retn_rate) FETCH next FROM ok_cursor INTO @enroll_mth, @cncl_mth, @mth, @tot_cancel, @tot_orders END CLOSE ok_cursor DEALLOCATE ok_cursor SELECT * FROM #attr

Thank you Hari for providing the solution with the cursor. Will try out and let you know how it works. How can I achieve getting the active count by using lead and lag function? Very much appreciate your help!

Thank you again,
Pam

[harishgg1]

Basically the idea is LEAD and LAG functions give you access to previous and next rows

Please see the below link .. if you still dont understand please let me know .. I will put Diagrams

SQL Server 2012 Functions - Lead and Lag

[harishgg1]

Hi PAM

I am using SQL for the same result
without using LEAD and LAG functions

Please note 3 is hardcoded .. can make it dynamic

Also without creating the #abc table
#abc table is this table = Create the TABLE using join detail summary"
I can get the result in 1 single SQL

Drop Create Data

USE tempdb 

go 

DROP TABLE #summary 

go 

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

Create the TABLE using join detail summary

DROP TABLE #ABC
GO 

SELECT a.enroll_month, 
                a.cncl_mnth, 
                a.mth, 
                a.tot_cancel, 
                b.tot_orders  INTO #ABC 
         FROM   #detail a 
                JOIN #summary b 
                  ON a.enroll_month = b.enroll_month
go

SQL using recursive cte

;WITH rec_cte 
     AS (SELECT *, 
                tot_orders - tot_cancel AS ok 
         FROM   #abc 
         WHERE  mth = 1 
         UNION ALL 
         SELECT E.*, 
                rec_cte.ok - e.tot_cancel 
         FROM   #abc e 
                JOIN rec_cte 
                  ON rec_cte.mth < 3 
                     AND e.mth = rec_cte.mth + 1) 
SELECT * 
FROM   rec_cte 

go

Result

[ppetronas]

Hi Harish,

Thank you for spending the time to provide this solution . When I run your code as is it works well.
In my code I am replacing the #summary table with Datasummary table and #Detail table with Datadetail tables respectively. The structure of these tables is exactly the same as the #summary and #detail table . The Datasummary table contains enroll date from Jan 2017 onwwards until Sept 2018 with total counts,It contains 21 rows. The Datadetail table contains enroll date from Jan 2017 onwards until Sept 2018 and for each of enroll date it has a corresponding cancel date with cancel counts . It contains 250 rows. (below is a sceenshot)

When I use these tables the result set is inaccurate. Below is my code with the new tables:

IF OBJECT_ID('tempdb.dbo.#Attr') <> 0 DROP TABLE #attr
CREATE TABLE #Attr (

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

DECLARE @enroll_mth DATETIME,
@cncl_mth DATETIME,
@mth INT,
@tot_cancel NUMERIC

-- #Summary
DECLARE @tot_orders NUMERIC

-- Other Two Calculations
DECLARE @attr_rate NUMERIC(6, 2),
@retn_rate NUMERIC(6, 2)

-- Variable To Do
DECLARE @active_count INT
-- Running Variable To Do
DECLARE @run_active_count INT

-- Cursor Declare
DECLARE ok_cursor CURSOR FOR
SELECT a.enroll_month,
a.cncl_mnth,
a.mth,
a.tot_cancel,
b.tot_orders
FROM cohortdetail a
JOIN cohortsummary b
ON a.enroll_month = b.enroll_month

-- Open Cursor
OPEN ok_cursor

-- Fecth First Time from Cursor
FETCH next FROM ok_cursor INTO @enroll_mth, @cncl_mth, @mth, @tot_cancel,
@tot_orders

WHILE @@FETCH_STATUS = 0
BEGIN
IF @mth = 1
BEGIN
SET @active_count = @tot_orders - @tot_cancel
SET @run_active_count = @active_count
END
ELSE
BEGIN
SET @run_active_count = @run_active_count - @tot_cancel

    END 

  SELECT @retn_rate = ( @run_active_count / @tot_orders ) * 100 

  SELECT @attr_rate = 100 - @retn_rate 

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

  FETCH next FROM ok_cursor INTO @enroll_mth, @cncl_mth, @mth, @tot_cancel,
  @tot_orders 

END

CLOSE ok_cursor

DEALLOCATE ok_cursor

SELECT *
FROM #attr
order by 1,2,3

Below is the result set for the above query. Active counts are correct only when mth=1 but does not for the rest

Can you please help! Thank you so much!!

Thanks,
Pam

[ppetronas]

Hi Harish,

I totally appreciate you providing with one more solution. I tried this one as well and replaced it with Datasummary and DataDetail table. Below is my updated query. I am using 24 instead of the 3 as I need to pull 24 months of data. The query ran for 40 mins. It was still running when I stopped it as it was taking way too long. Is there a way this can be optimized.

DROP TABLE #ABC
GO

SELECT a.enroll_month,
a.cncl_mnth,
a.mth,
a.tot_cancel,
b.tot_orders INTO #ABC
FROM cohortdetail a
INNER JOIN cohortsummary b
ON a.enroll_month = b.enroll_month
go

;WITH rec_cte
AS (SELECT ,
tot_orders - tot_cancel AS ok
FROM #abc
WHERE mth = 1
UNION ALL
SELECT E.,
rec_cte.ok - e.tot_cancel
FROM #abc e
Inner JOIN rec_cte
ON rec_cte.mth < 24
AND e.mth = rec_cte.mth + 1)
SELECT *
FROM rec_cte

Thank you so very much!
Pam

[harishgg1]

Hi Pam

Hi Harish,

I totally appreciate you providing with one more solution. I tried this one as well and replaced it with Datasummary and DataDetail table. Below is my updated query. I am using 24 instead of the 3 as I need to pull 24 months of data. The query ran for 40 mins. It was still running when I stopped it as it was taking way too long. Is there a way this can be optimized.

DROP TABLE #ABC
GO

SELECT a.enroll_month,
a.cncl_mnth,
a.mth,
a.tot_cancel,
b.tot_orders INTO #ABC
FROM cohortdetail a

I can try to optimize it

Is it possible to give me the create data scripts
for all the data in the 2 tables ???

Will make it much easier for me to optimize

Please check with your company Manager first !!!
i mean about giving me all the data in the 2 tables

[harishgg1]

hi Looking at the data

looks like we need to partition by
first enroll_month
then by mth = 1 , 2 etc

need to change the cursor logic

also for the other solution also need to change logic

Would be a great help
if you could give me actual data create script

[harishgg1]

Hi Pam

To recount

I looked at the data
We need to Partition by Enroll_Month first then by mth

I have made some changes to the cursor logic

Please see if this cursor works

New Cursor

-- #Detail   
DECLARE @enroll_mth DATETIME, 
        @cncl_mth   DATETIME, 
        @mth        INT, 
        @tot_cancel NUMERIC 
-- #Summary           
DECLARE @tot_orders NUMERIC 
DECLARE @attr_rate NUMERIC(6, 2), 
        @retn_rate NUMERIC(6, 2) 
-- Variable To Do           
DECLARE @active_count INT 
-- Running Variable To Do 
DECLARE @run_active_count INT 
-- Cursor Declare           
DECLARE ok_cursor CURSOR FOR 
  SELECT a.enroll_month, 
         a.cncl_mnth, 
         a.mth, 
         a.tot_cancel, 
         b.tot_orders 
  FROM   #detail a 
         JOIN #summary b 
           ON a.enroll_month = b.enroll_month 
  ORDER  BY a.enroll_month, 
            a.mth 

-- Open Cursor    
OPEN ok_cursor 

-- Fecth First Time from Cursor    
FETCH next FROM ok_cursor INTO @enroll_mth, @cncl_mth, @mth, @tot_cancel, 
@tot_orders 

-- Starting Month  
DECLARE @starting_enroll_mth DATETIME = @enroll_mth 

WHILE @@FETCH_STATUS = 0 
  BEGIN 
      IF @enroll_mth <> @starting_enroll_mth 
        SET @starting_enroll_mth = @enroll_mth 

      IF @enroll_mth = @starting_enroll_mth 
        BEGIN 
            IF @mth = 1 
              BEGIN 
                  SET @active_count = @tot_orders - @tot_cancel 
                  SET @run_active_count = @active_count 
              END 
            ELSE 
              BEGIN 
                  SET @run_active_count = @run_active_count - @tot_cancel 
              END 

            SELECT @retn_rate = ( @run_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_cancel, 
                          @tot_orders, 
                          @run_active_count, 
                          @attr_rate, 
                          @retn_rate) 
        END 

      FETCH next FROM ok_cursor INTO @enroll_mth, @cncl_mth, @mth, @tot_cancel, 
      @tot_orders 
  END 

CLOSE ok_cursor 

DEALLOCATE ok_cursor 

SELECT * 
FROM   #attr 
ORDER  BY 1

[harishgg1]

Hi Pam

Regarding the SQL option that I tried
you said its taking too long 40 minutes still going on

How much data are we talking about ?
count of rows in tables .. please let me know

If i can have the actual data to work with
I can TRY

please provide ALL the data scripts
there is a way to generate ALL the data scripts from SSMS

[jeffw8713]

To get a running total - you need to use SUM windowing function:

Declare @summary Table ( 
        enroll_month datetime
      , tot_orders 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); 

Declare @detail Table (
        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) 
      , ('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); 

   With counts
     As (
 Select s.enroll_month
      , s.tot_orders
      , tot_cancel = sum(tot_cancel) over(Partition By d.enroll_month Order By mth
                                                                          rows unbounded preceding)
      , active_count = s.tot_orders - sum(tot_cancel) over(Partition By d.enroll_month Order By mth
                                                                                           rows unbounded preceding)
   From @summary        s
  Inner Join @detail    d On d.enroll_month = s.enroll_month
        )
 Select *
      , retn_rate = cast((1 - (active_count * 1.0 / tot_orders)) * 100 As decimal(5,2))
      , attr_rate = cast(100 - ((1 - (active_count * 1.0 / tot_orders)) * 100) As decimal(5,2))
   From counts;

You can reduce the number of windowing functions used this way:

Declare @summary Table ( 
        enroll_month datetime
      , tot_orders 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); 

Declare @detail Table (
        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) 
      , ('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); 

   With counts
     As (
 Select s.enroll_month
      , s.tot_orders
      , tot_cancel = sum(tot_cancel) over(Partition By d.enroll_month Order By mth
                                                                          rows unbounded preceding)
   From @summary        s
  Inner Join @detail    d On d.enroll_month = s.enroll_month
        )
 Select *
      , active_count = tot_orders - tot_cancel
      , retn_rate = cast((1 - ((tot_orders - tot_cancel) * 1.0 / tot_orders)) * 100 As decimal(5,2))
      , attr_rate = cast(100 - ((1 - ((tot_orders - tot_cancel) * 1.0 / tot_orders)) * 100) As decimal(5,2))
   From counts;

I would not utilize the second option unless you find that the first option does not perform well enough - and the second option improves that performance. It should not make any difference in performance though...but test just to be sure.

[harishgg1]

hi Pam

Looks like you .. trying with SQL I wrote before ...
took you a long time 40 minutes still not over

I created another column
Row number
In the create sample data

bitsmed .. a senior member here
has some reason why he does not use recursive CTE
I dont know why ...
but something to keep in mind

I tried this again .. recursive CTE
Please take a look
Hopefully this will be a lot shorter in time

drop create sample data

USE tempdb 

go 

DROP TABLE data 

go 

CREATE TABLE data 
  ( 
     rn        INT IDENTITY(1, 1) NOT NULL, 
     day       VARCHAR(100), 
     matchdate DATE, 
     league    VARCHAR(100), 
     countstar INT, 
     grp       INT NULL 
  ) 

go 

SET dateformat dmy 

INSERT INTO data 
SELECT 'FRI', 
       '07/08/2015', 
       'E1', 
       1, 
       NULL 

INSERT INTO data 
SELECT 'SAT', 
       '08/08/2015', 
       'E1', 
       10, 
       NULL 

INSERT INTO data 
SELECT 'SAT', 
       '08/08/2015', 
       'E2', 
       12, 
       NULL 

INSERT INTO data 
SELECT 'SAT', 
       '08/08/2015', 
       'E3', 
       12, 
       NULL 

INSERT INTO data 
SELECT 'SUN', 
       '09/08/2015', 
       'E1', 
       1, 
       NULL 

INSERT INTO data 
SELECT 'FRI', 
       '14/08/2015', 
       'E3', 
       1, 
       NULL 

INSERT INTO data 
SELECT 'SAT', 
       '15/08/2015', 
       'E1', 
       10, 
       NULL 

INSERT INTO data 
SELECT 'SAT', 
       '15/08/2015', 
       'E2', 
       11, 
       NULL 

INSERT INTO data 
SELECT 'SAT', 
       '15/08/2015', 
       'E3', 
       11, 
       NULL 

INSERT INTO data 
SELECT 'SUN', 
       '16/08/2015', 
       'E1', 
       2, 
       NULL 

INSERT INTO data 
SELECT 'SUN', 
       '16/08/2015', 
       'E2', 
       1, 
       NULL 

INSERT INTO data 
SELECT 'TUE', 
       '18/08/2015', 
       'E1', 
       7, 
       NULL 

INSERT INTO data 
SELECT 'TUE', 
       '18/08/2015', 
       'E2', 
       10, 
       NULL 

INSERT INTO data 
SELECT 'TUE', 
       '18/08/2015', 
       'E3', 
       12, 
       NULL 

INSERT INTO data 
SELECT 'WED', 
       '19/08/2015', 
       'E1', 
       4, 
       NULL 

INSERT INTO data 
SELECT 'WED', 
       '19/08/2015', 
       'E2', 
       2, 
       NULL 

INSERT INTO data 
SELECT 'FRI', 
       '21/08/2015', 
       'E1', 
       1, 
       NULL 

go 

SELECT * 
FROM   data 

go

SQL New Tried

;WITH cte 
     AS (SELECT a.rn, 
                a.enroll_month, 
                a.cncl_mnth, 
                a.mth, 
                a.tot_cancel, 
                b.tot_orders, 
                b.tot_orders - a.tot_cancel AS diff 
         FROM   detail a 
                JOIN summary b 
                  ON a.enroll_month = b.enroll_month), 
     reccte 
     AS (SELECT * 
         FROM   cte 
         WHERE  rn = 1 
         UNION ALL 
         SELECT a.rn, 
                a.enroll_month, 
                a.cncl_mnth, 
                a.mth, 
                a.tot_cancel, 
                a.tot_orders, 
                b.diff - a.tot_cancel 
         FROM   cte a 
                JOIN reccte b 
                  ON a.rn = b.rn + 1) 
SELECT * 
FROM   reccte 

go

Result

[harishgg1]

Hi Pam

I got feedback from bitsmed about recursive ctes .. I mean his reasons for not using..

Performance is one reason

I just wanted to see if recursive cte is a possible solution

I have no idea about its performance
Never experienced in work or anywhere

For your information
,