CODEWITHSUNDEEP
sqloracle-database
DeE DEe
DeE DEe

Reputation: 335

Retrieve records by continuation of days in oracle

I want to retrieve records where cash deposits are more than 4 totaling to 1000000 during a day and continues for more than 5 days.

I have came up with below query.

    SELECT COUNT(a.txamt) AS "txcount"
           , SUM(a.txamt) AS "txsum"
           , b.custcd
           , a.txdate
      FROM tb_transactions a 
INNER JOIN tb_accounts b 
        ON a.acctno = b.acctno
     WHERE a.cashflowtype = 'CR'
     GROUP BY b.custcd, a.txdate 
    HAVING COUNT(a.txamt)>4 and SUM(a.txamt)>='1000000'
     ORDER BY a.txdate;

But I'm stuck on how to fetch the records if the pattern continues for 5 days.

How to achieve the desired result?

Upvotes: 1

Views: 38

Answers (1)

MT0
MT0

Reputation: 168361

Something like:

SELECT *
FROM   (
  SELECT t.*,
         COUNT( txdate ) OVER ( PARTITION BY custcd
                                ORDER BY txdate
                                RANGE BETWEEN INTERVAL '0' DAY PRECEDING
                                          AND INTERVAL '4' DAY FOLLOWING ) AS 
num_days
  FROM   (
    select count(a.txamt) as "txcount",
           sum(a.txamt) as "txsum",
           b.custcd,
           a.txdate
    from   tb_transactions a inner join tb_accounts b on a.acctno=b.acctno
    where  a.cashflowtype='CR'
    group by b.custcd, a.txdate
    having count(a.txamt)>4 and sum(a.txamt)>=1000000
  ) t
)
WHERE num_days = 5
order by a.txdate;

Upvotes: 1

Related Questions