Sql group by last recurring area

Question

I don’t even know what a good name is for this question.

But I have a table:

create table trans 
(
    [transid] INT          IDENTITY (1, 1) NOT NULL,
    [customerid] int not null,
    [points] decimal(10,2) not null,
    [date] datetime not null
)

and records:

--cus1
INSERT INTO trans ( customerid , points , date )
VALUES ( 1, 10, '2016-01-01' ) , ( 1, 20, '2017-02-01' ) , ( 1, 22, '2017-03-01' ) ,
       ( 1, 24, '2018-02-01' ) , ( 1, 50, '2018-02-25' ) , ( 2, 44, '2016-02-01' ) ,
       ( 2, 20, '2017-02-01' ) , ( 2, 32, '2017-03-01' ) , ( 2, 15, '2018-02-01' ) ,
       ( 2, 10, '2018-02-25' ) , ( 3, 10, '2018-02-25' ) , ( 4, 44, '2015-02-01' ) ,
       ( 4, 20, '2015-03-01' ) , ( 4, 32, '2016-04-01' ) , ( 4, 15, '2016-05-01' ) ,
       ( 4, 10, '2017-02-25' ) , ( 4, 10, '2018-02-27' ) ,( 4, 20, '2018-02-28' ) , 
       ( 5, 44, '2015-02-01' ) , ( 5, 20, '2015-03-01' ) , ( 5, 32, '2016-04-01' ) , 
       ( 5, 15, '2016-05-01' ) ,( 5, 10, '2017-02-25' );

-- selecting the data
select * from trans

It produces:

transid     customerid  points                                  date
----------- ----------- --------------------------------------- -----------------------
1           1           10.00                                   2016-01-01 00:00:00.000
2           1           20.00                                   2017-02-01 00:00:00.000
3           1           22.00                                   2017-03-01 00:00:00.000
4           1           24.00                                   2018-02-01 00:00:00.000
5           1           50.00                                   2018-02-25 00:00:00.000
6           2           44.00                                   2016-02-01 00:00:00.000
7           2           20.00                                   2017-02-01 00:00:00.000
8           2           32.00                                   2017-03-01 00:00:00.000
9           2           15.00                                   2018-02-01 00:00:00.000
10          2           10.00                                   2018-02-25 00:00:00.000
11          3           10.00                                   2018-02-25 00:00:00.000
12          4           44.00                                   2015-02-01 00:00:00.000
13          4           20.00                                   2015-03-01 00:00:00.000
14          4           32.00                                   2016-04-01 00:00:00.000
15          4           15.00                                   2016-05-01 00:00:00.000
16          4           10.00                                   2017-02-25 00:00:00.000
17          4           10.00                                   2018-02-27 00:00:00.000
18          4           20.00                                   2018-02-28 00:00:00.000
19          5           44.00                                   2015-02-01 00:00:00.000
20          5           20.00                                   2015-03-01 00:00:00.000
21          5           32.00                                   2016-04-01 00:00:00.000
22          5           15.00                                   2016-05-01 00:00:00.000
23          5           10.00                                   2017-02-25 00:00:00.000

I try to group all customers and summarize their points. But here's the catch. If the trance is not active for 1 year (the next transition is 1 year or more), the points will expire.

In this case: Points for each client should be:

Customer1 20+22+24+50
Customer2 20+32+15+10
Customer3 10
Customer4 10+20
Customer5 0

Here is what I still have:

select 
    t1.transid as transid1,
    t1.customerid as customerid1,
    t1.date as date1,
    t1.points as points1,
    t1.rank1 as rank1,
    t2.transid as transid2,
    t2.customerid as customerid2,
    t2.points as points2,
    isnull(t2.date,getUTCDate()) as date2,
    isnull(t2.rank2,t1.rank1+1) as rank2,
    cast(case when(t1.date > dateadd(year,-1,isnull(t2.date,getUTCDate()))) Then 0 ELSE 1 END as bit) as ShouldExpire
    from 
    (
        select transid,CustomerID,Date,points,
        RANK() OVER(PARTITION BY CustomerID ORDER BY date ASC) AS RANK1
        from trans
    )t1
    left join
    (
        select transid,CustomerID,Date,points,
        RANK() OVER(PARTITION BY CustomerID ORDER BY date ASC) AS RANK2
        from trans
    )t2 on t1.RANK1=t2.RANK2-1 
    and t1.customerid=t2.customerid

what gives

from the above table, how can I check that the ShouldExpire field with max (rank1) for the client, if it is 1, then totalpoints will be 0, otherwise sum all consecutive 0 until there are more records or 1 met?

Or is there a better approach to this problem?

Answer 1

LEAD, CustomerID:

;WITH CTE AS (
   SELECT transid, CustomerID, [Date], points,
          LEAD([Date]) OVER (PARTITION BY CustomerID 
                             ORDER BY date ASC) AS nextDate,
          CASE 
             WHEN [date] > DATEADD(YEAR, 
                                   -1, 
                                   -- same LEAD() here as above
                                   ISNULL(LEAD([Date]) OVER (PARTITION BY CustomerID 
                                                             ORDER BY date ASC),
                                          getUTCDate()))
                THEN 0 
             ELSE 1
          END AS ShouldExpire
   FROM trans
)
SELECT transid, CustomerID, [Date], points, nextDate, ShouldExpire 
FROM CTE
ORDER BY CustomerID, [Date]

:

transid CustomerID  Date        points  nextDate    ShouldExpire
-------------------------------------------------------------
1       1           2016-01-01  10.00   2017-02-01  1 <-- last exp. for 1
2       1           2017-02-01  20.00   2017-03-01  0
3       1           2017-03-01  22.00   2018-02-01  0
4       1           2018-02-01  24.00   2018-02-25  0
5       1           2018-02-25  50.00   NULL        0

6       2           2016-02-01  44.00   2017-02-01  1 <-- last exp. for 2
7       2           2017-02-01  20.00   2017-03-01  0
8       2           2017-03-01  32.00   2018-02-01  0
9       2           2018-02-01  15.00   2018-02-25  0
10      2           2018-02-25  10.00   NULL        0

11      3           2018-02-25  10.00   NULL        0 <-- no exp. for 3

12      4           2015-02-01  44.00   2015-03-01  0
13      4           2015-03-01  20.00   2016-04-01  1
14      4           2016-04-01  32.00   2016-05-01  0
15      4           2016-05-01  15.00   2017-02-25  0
16      4           2017-02-25  10.00   2018-02-27  1 <-- last exp. for 4
17      4           2018-02-27  10.00   2018-02-28  0
18      4           2018-02-28  20.00   NULL        0

19      5           2015-02-01  44.00   2015-03-01  0
20      5           2015-03-01  20.00   2016-04-01  1
21      5           2016-04-01  32.00   2016-05-01  0
22      5           2016-05-01  15.00   2017-02-25  0
23      5           2017-02-25  10.00   NULL        1 <-- last exp. for 5

, .

CTE , :

;WITH CTE AS (
   ... above query here ...
)
SELECT CustomerID, 
       SUM(CASE WHEN rnk = 0 THEN points ELSE 0 END) AS sumOfPoints
FROM (
   SELECT transid, CustomerID, [Date], points, nextDate, ShouldExpire,
          SUM(ShouldExpire) OVER (PARTITION BY CustomerID ORDER BY [Date] DESC) AS rnk
   FROM CTE
) AS t
GROUP BY CustomerID

:

CustomerID  sumOfPoints
-----------------------
1           116.00
2           77.00
3           10.00
4           30.00
5           0.00

Answer 2

:

SELECT customerid, 
       Sum(t1.points) 
FROM   trans t1 
WHERE  NOT EXISTS (SELECT 1 
                   FROM   trans t2 
                   WHERE  Datediff(year, t1.date, t2.date) >= 1) 
GROUP  BY t1.customerid 

, !

Answer 3

:

select customerid,Sum(points)  
from trans where Datediff(year, date, GETDATE()) < 1
group by customerid

:

1 - 74,00

2 - 25.00

3 - 10.00

4 - 30.00

Answer 4

- , , . , , , ?

№ 5, , , " ", ?

:

WITH ordered AS (
    SELECT
        *,
        ROW_NUMBER() OVER (PARTITION BY customerid ORDER BY [date]) AS order_id
    FROM
        trans),
max_transid AS (
    SELECT
        customerid,
        MAX(transid) AS max_transid
    FROM
        trans
    GROUP BY
        customerid),
not_expired AS (
    SELECT
        t1.customerid,
        t1.points,
        t1.[date] AS t1_date,
        CASE
            WHEN m.customerid IS NOT NULL THEN GETDATE()
            ELSE t2.[date] 
        END AS t2_date
    FROM
        ordered t1
        LEFT JOIN ordered t2 ON t2.customerid = t1.customerid AND t1.transid != t2.transid AND t2.order_id = t1.order_id + 1 AND t1.[date] > DATEADD(YEAR, -1, t2.[date])
        LEFT JOIN max_transid m ON m.customerid = t1.customerid AND m.max_transid = t1.transid
),
max_not_expired AS (
    SELECT
        customerid,
        MAX(t1_date) AS max_expired
    FROM
        not_expired
    WHERE
        t2_date IS NULL
    GROUP BY
        customerid)
SELECT 
    n.customerid,
    SUM(n.points) AS points
FROM 
    not_expired n
    LEFT JOIN max_not_expired m ON m.customerid = n.customerid 
WHERE
    ISNULL(m.max_expired, '19000101') < n.t1_date
GROUP BY
    n.customerid;

It can be reorganized to be simpler, but I wanted to show the steps to get the final answer:

customerid points
1   116.00
2   77.00
3   10.00
4   30.00
5   57.00