How to show only results that had positive money transfer to the bank at least once in 6 months-CodePudding

I would like to ask if you have an idea how would you get only those id´s from data which have positive money transfer to the bank at least 1 time in 6 months prior for each month for example for year 2022.

I have column date (day/month/year), id of a client and how much does he/she send money to a bank for each day(client_cr). And I want that SQL looks at a client in each month (from 01/2022 till 12/2022) and checks if a client sent money to the bank at least once in 6 moths so for 01/2022 it would check if client from 07/2021 up till 01/2022 had any transfer to the bank if so it will show in in the table and same for each month of 2022

for example:

select id,
       to_char(to_date(time, 'J'), 'YYYY/MM') time_month,
from   table x
where  to_char(to_date(time, 'J'), 'YYYY/MM') >= '2021/07'
and    client_cr???(here would be probably some clever condition which i cannot figure out)

It doesn't have to be the whole script even rough idea or which function I can use would be appreciated

CodePudding user response：

Does your data always have an entry of some kind for each day? Or only when there are transfers/activity and so there may be missing days?

If there it always has an entry of some kind for every day, then you can simply do:

SELECT id
  FROM (SELECT id,
               month,
               SUM(money_sent_count) OVER (PARTITION BY id ORDER BY month ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) money_sent_in_prev_6_months
          FROM (SELECT id,
                       TO_CHAR(datefield,'YYYY-MM') month,
                       SUM(CASE WHEN (money_was_sent) THEN 1 ELSE 0 END) money_sent_count
                  FROM table
                 GROUP BY id,
                          TO_CHAR(datefield,'YYYY-MM')))
 WHERE money_sent_in_prev_6_months > 0

If however your table only contains transactions and so there could be missing days or whole months from an ID's history, then you will have to use a calendar table or generate one on the fly (one row per month from earliest to latest represented in your table) and outer join that to your table. If you need help doing that on the fly that'd be another question that we can help with. But once you do that, the same concept applies as above for querying for what you want.

CodePudding user response：

You can check if exists any record in the past 6 months with the same ID with positive money, try to use the idea of the query below

SELECT a.id,
       a.time
FROM table x a
WHERE TRUNC(a.time) >= TO_DATE('01/01/2022','DD/MM/YYYY') 
AND EXISTS (SELECT 1 
              FROM table x b
             WHERE a.id = b.id
               AND b.time BETWEEN add_months(a.time, -6) AND a.time --Last 6 months
               AND b.client_cr > 0) --Positive Money