计算最早达标人、时间

[maniacstone]

如下表结构：
memberid name time money
1 A 2019-01-01 12:12:12 100
1 A 2019-01-02 12:12:12 200.1
1 A 2019-01-12 12:12:12 500.1
2 B 2019-01-01 11:12:12 300.1
2 B 2019-01-01 11:12:12 320.0
2 B 2019-01-10 11:12:12 320.0
3 C 2019-01-01 11:12:12 20.0
.
.
.

计算在2019年1月份，每个人总业绩达到500的最早时间


请各位大神支招。

[ruanjianerhao]

看不明白0000

[maniacstone]

引用 1 楼 ruanjianerhao 的回复:

看不明白0000

说简单点就是计算业绩最早达标的人和时间

[TIGER0579]

select [memberid],[name],min([time]) 最早达标时间 from
(select [memberid],[name],[time],[money] from test2 a WHERE (select sum([money]) q from test2 b where b.[memberid]=a.[memberid] and b.[time]<=a.[time])>500) x
group by [memberid],[name]

[Hello World,]

DECLARE @t TABLE(memberid INT NOT NULL, name VARCHAR(10), time DATETIME, money DECIMAL(10,2));

INSERT  @t(memberid, name, time, money)
VALUES(1, 'A', '2019-01-01 12:12:12', 100),
      (1, 'A', '2019-01-02 12:12:12', 200.1),
      (1, 'A', '2019-01-12 12:12:12', 500.1),
      (2, 'B', '2019-01-01 11:12:12', 300.1),
      (2, 'B', '2019-01-01 11:12:12', 320.0),
      (2, 'B', '2019-01-10 11:12:12', 320.0),
      (3, 'C', '2019-01-01 11:12:12', 20.0);

DECLARE @memberid INT = 0,
        @total DECIMAL(10, 2) = 0;

SELECT      memberid,
            name,
            time,
            money,
            CAST(0 AS DECIMAL(10, 2)) total
INTO        #t
FROM        @t
ORDER BY    memberid,
            time;

UPDATE  #t
SET     @total = CASE WHEN @memberid = memberid THEN money + @total ELSE money END,
        @memberid = memberid,
        total = @total;

SELECT  *
FROM    (SELECT memberid, name FROM #t GROUP BY memberid, name)a
        CROSS APPLY(SELECT      TOP(1)time,
                                money,
                                total
                    FROM        #t
                    WHERE       total >= 500 AND memberid = a.memberid
                    ORDER BY    time)b;

DROP TABLE #t;