Lag Lead to calculate the beginning and ending number of subscriptions in a period

Question

I am trying to build a output table as given below. Can some one please help how I can use the lag lead or some other window function to solve it?

Start= End from previous month
Net= new- churn
End = Start + Net

INPUT 

month   year    new churn   
2      2012     114     0   
3      2012     143     20  
4      2012     221     43  
5      2012     197     74  
6      2012     234     122 
7      2012     276     138 
8      2012     278     200 


OUTPUT


month   year    Start   new churn   Net End
2      2012     0       114     0   114 114
3      2012     114     143     20  123 237
4      2012     237     221     43  181 418
5      2012     418     197     74  125 543
6      2012     543     234     122 116 659
7      2012     659     276     138 150 809
8      2012     809     278     200 90  899

`221 - 43 = 178`, not `181`. I think your calculations may be off. — Gordon Linoff, Apr 25 '18 at 11:47

score 0 · Answer 1 · answered Apr 25 '18 at 06:06

0

Not sure how the start is calculated. You said Start will be end of previous month but didn't mention which column it needs to be. But I think you need some thing as below.

select [month],[YEAR],LAG (new,1) OVER (ORDER BY [Month]) AS Start,
    new, churn,new-chrun as Net, start+net as end
From TableA

answered Apr 25 '18 at 06:06

SQLHelp

41
4

Start = End from the previous month. For first month , Start =0 since there is no lag to the first row – user286546 Apr 25 '18 at 19:08

score 0 · Answer 2 · answered Apr 25 '18 at 06:35

Try this:

DECLARE @tab1 TABLE(month INT, year INT,new INT, churn INT)

INSERT INTO @tab1 VALUES
(2,2012,114,0  )
,(3,2012,143,20 )
,(4,2012,221,43 )
,(5,2012,197,74 )
,(6,2012,234,122)
,(7,2012,276,138)
,(8,2012,278,200)

SELECT [T1].[month],[T1].[Year]
    ,ISNULL(SUM([T2].new-[T2].churn) OVER(ORDER BY [T1].[Year], [T1].[month]),0)Start
    ,[T1].new, [T1].churn, [T1].new-[T1].churn AS net
    ,([T1].new-[T1].churn)+ISNULL(SUM([T2].new-[T2].churn) OVER(ORDER BY [T1].[Year], [T1].[month]),0) [End]
From @tab1 T1
LEFT JOIN @tab1 T2 ON T1.month = t2.month+1

Output:

month   Year    Start   new churn   net End
2       2012    0       114 0       114 114
3       2012    114     143 20      123 237
4       2012    237     221 43      178 415
5       2012    415     197 74      123 538
6       2012    538     234 122     112 650
7       2012    650     276 138     138 788
8       2012    788     278 200     78  866

score 0 · Answer 3 · answered Apr 25 '18 at 09:33

You can try this query.

;WITH CTE AS(
    SELECT *,(new- churn) as 'Net' 
    from T1
)
SELECT t1.[month],
       t1.[Year],
       coalesce(lag([End]) OVER(order by [year],[month]),0) 'Start',
       t1.new,
       t1.churn,
       t1.Net,
       t1.[End]
FROM (
    SELECT t1.*,coalesce(SUM(t2.Net) over(order by t2.[year],t2.[month]),0) +t1.net as 'End'
    From  CTE t1
    LEFT JOIN CTE t2 ON T1.[month] = t2.[month]+1
) t1

sqlfiddle:http://sqlfiddle.com/#!18/6a544/1

score 0 · Answer 4 · answered Apr 25 '18 at 11:43

0

I think you want:

select i.*,
       sum(new) over (order by year, month) as start,
       (new - churn) as net,
       sum(new - churn) over (order by year, month) as end
from input i;

Here is a SQL Fiddle.

answered Apr 25 '18 at 11:43

Gordon Linoff

1,242,037
58
646
786

Lag Lead to calculate the beginning and ending number of subscriptions in a period

4 Answers4