3

I have tried searching in Google but am just not finding the right post. I have this table with fields trans_date, CID (which is customerid), and amount.

How do I find the CID exceeding an amount of say $1000 within any time period of 6 months or 180 days (eg. Jan-June or Feb-July, or November 2014-April 2015 (periods overlapping years too)) starting from the beginning of time?

trans_date             CID        amount
22-Jun-2015            123        550
24-Aug-2015            999        200    
15-Aug-2015            123        500
24-nov-2014            321        200 
08-Jan-2015            321        900
24-oct-2015            999        200     

In the result CID 123 and 321 should be the output both exceeding the total amount 1000 in a span of 6 months.

Any response would be highly appreciated.

2
  • 1
    Edit your question and add some sample data and the expected output based on that data. I am also not sure what "within any time period of 6 months" means. Any period of 6 months since the beginning of time e.g. January 2000 to June 2000 and then again between March 2003 and August 2003 and then again between October 2007 and March 2008? Or do you mean "in the last 6 months since today"? Or any customer that exceeded that amount within a specific date range that you specify when running the query?
    – user1822
    Commented Dec 23, 2015 at 11:26
  • i did...better ?
    – penjor
    Commented Dec 23, 2015 at 11:57

2 Answers 2

6

The part "within any time period of 6 months" makes things quite tricky. It is very easy to check this for a specific period:

select cid,
       sum(amount)
from transfer
where trans_date >= date '2014-11-01' 
  and trans_date < date '2015-06-01'
group by cid
having sum(amount) >= 1000;

But this is not what you want.

You want a "rolling running total" that covers the last 6 month relative to the the trans_date value of each row. This can easily be achieved using a window function (aka "analytical function")

select trans_date, 
       cid, 
       amount,
       sum(amount) over (partition by cid order by trans_date range between interval '6' month preceding and current row) as running_total
from transfer
order by trans_date;

The "trick" is the "range between ..." part: it will only evaluate the running total for the "last 6 month" relative to the month of the "current row"

The above returns the following result for your sample data:

TRANS_DATE          | CID | AMOUNT | RUNNING_TOTAL
--------------------+-----+--------+--------------
2014-11-24 00:00:00 | 321 |    200 |           200
2015-01-08 00:00:00 | 321 |    900 |          1100
2015-06-22 00:00:00 | 123 |    550 |           550
2015-08-15 00:00:00 | 123 |    500 |          1050
2015-08-24 00:00:00 | 999 |    200 |           200
2015-10-24 00:00:00 | 999 |    200 |           400

Now to get the customers you are interested in, you can wrap this query into a derived table and then get only those CIDs that have at least one running total that exceeds 1000:

select distinct cid
from (
  select trans_date, 
         cid, 
         amount,
         sum(amount) over (partition by cid order by trans_date range between interval '6' month preceding and current row) as running_total
  from transfer
) t
where running_total > 1000;

The distinct is necessary because a customer can have more than one "6 month period" where the total amount exceeds 1000.

This is probably not the most efficient query though - but currently I can't think of anything better.

Here is a SQLFiddle example: http://sqlfiddle.com/#!4/1e26f/2

0
-1

You can write such a function :

create or replace function get_period(v_date date)
return varchar2
is period varchar2(10);
yr varchar2(4);
mnt number;
begin
     yr := to_char(v_date ,'yyyy');
     mnt := to_number(to_char(v_date,'mm'));
     if (mnt > 0 && mnt < 7) then
         period := concat(yr ,'_1');
     end if;
     if (mnt > 6 && mnt < 13) then
         period := concat(yr ,'_2');
     end if;
     return period; 
end;

then in query :

select CID , get_period(trans_date) , sum(amount)
from table_name
group by CID , get_period(trans_date)
having sum(amount) > 1000
13
  • thank you for the answer but what i am looking for is not the last 180 days, but transactions of any customer in any interval of 180 days. For eg the current month is Dec, but i would like the query to find out between Appril and June if he exceeded 1000 spendings.
    – penjor
    Commented Dec 23, 2015 at 11:13
  • if you need to check this AMOUNT exceeding for each period of 180 days then i would write a function which get the trans_date and return the period which this record is belong too.
    – alonk
    Commented Dec 23, 2015 at 11:14
  • you can use case as well :
    – alonk
    Commented Dec 23, 2015 at 11:16
  • 1
    year(trans_date)+'-' is invalid SQL. + is used to add numbers, not to concatenate strings. For character values you have to use || in SQL. There is also no year() or month() function in Oracle. And the expression case true is invalid in Oracle as well. But even if it was valid, it would make no sense at all because the second part of the case will never be executed
    – user1822
    Commented Dec 23, 2015 at 11:25
  • 1
    The question was tagged oracle right from the beginning.
    – user1822
    Commented Dec 23, 2015 at 12:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.