How to calculate MAX not NULL in table?-CodePudding

How can I get that MAX 3 months with not NULL?

As result I need UserId, UserAgreementId and MAX Not NULL And Group BY UserId

month	Id	UserId	UserAgreementId	SubsriptionDate	Amount
1	NULL	NULL	NULL	NULL	NULL
2	12	222	33333	2020-02-02 00:00:00.000	40000
3	NULL	NULL	NULL	NULL	NULL
4	13	222	33333	2020-04-03 00:00:00.000	5000
5	NULL	NULL	NULL	NULL	NULL
6	15	222	33333	2020-06-04 00:00:00.000	7000
7	16	222	33333	2020-07-02 00:00:00.000	8000
8	17	222	33333	2020-08-09 00:00:00.000	4000
9	NULL	NULL	NULL	NULL	NULL
10	NULL	NULL	NULL	NULL	NULL
11	NULL	NULL	NULL	NULL	NULL
12	NULL	NULL	NULL	NULL	NULL

As Result must be smth like this

UserId	UserAgreementId	MAX
222	33333	3

Table AgreementId have SubsriptionDate in rows and I need to calculate period with max not empty Amount

UserAgreementId	SubsriptionDate	Amount
33333	2020-02-02 00:00:00.000	40000
33333	2020-04-03 00:00:00.000	5000
33333	2020-06-04 00:00:00.000	7000
33333	2020-07-02 00:00:00.000	8000
33333	2020-08-09 00:00:00.000	4000

CodePudding user response：

What you're asking for is too complex for a query - it will take a long time to run.

For example, I've put together a draft below that just about gets the longest duration for each user, yet it references the same table 5 times.

This is barely readible, convuluted, and will tax the server more than you want.

Instead, fetch the records and process them in a more suitable programming language.

SELECT t.user_id, MAX(t.monthsTotal - s.monthsTotal   1) AS longestDuration
FROM (SELECT a.*, YEAR(a.subscriptionDate) * 12   a.month AS monthsTotal
     FROM table_name) as t
INNER JOIN (SELECT a.*, YEAR(a.subscriptionDate) * 12   a.month AS monthsTotal
    FROM table_name AS a
    LEFT OUTER JOIN table_name AS preceeding
        ON a.user_id = preceeding.user_id
        AND YEAR(a.subscriptionDate) * 12   a.month = YEAR(preceeding.subscriptionDate) * 12   preceeding.month   1
    WHERE preceedingMonth.id IS NULL) AS s
    ON t.user_id = s.user_id
    AND t.monthsTotal >= s.monthsTotal
LEFT OUTER JOIN (SELECT a.*, YEAR(a.subscriptionDate) * 12   a.month AS monthsTotal
    FROM table_name AS a
    LEFT OUTER JOIN table_name AS preceeding
        ON a.user_id = preceeding.user_id
        AND YEAR(a.subscriptionDate) * 12   a.month = YEAR(preceeding.subscriptionDate) * 12   preceeding.month   1
    WHERE preceedingMonth.id IS NULL) AS dummy
    ON t.user_id = dummy.user_id
    AND dummy.monthsTotal > s.monthsTotal
    AND t.monthsTotal > dummy.monthsTotal
WHERE dummy.id IS NULL
GROUP BY t.user_id