2

i have a table that has a column name date_tran that shows the date on which the user has made any transaction. is there any way how i can check if 3 transactions are made by the user is in the same month or 3 continuos month?

i am using the following snippet:

SELECT CUST_ID,  DATE_TRAN FROM  FAKE_CUST F WHERE 
          EXISTS( 
                SELECT 1 from FAKE_CUST where (TO_CHAR(DATE_TRAN,'MM') = TO_CHAR(F.DATE_TRAN,'MM')-1 OR TO_CHAR(DATE_TRAN,'MM') = TO_CHAR(F.DATE_TRAN,'MM')) AND 
                                        CUST_ID= F.CUST_ID  AND TO_CHAR(DATE_TRAN,'YYYY')=TO_CHAR(F.DATE_TRAN,'YYYY'))
      and exists(
                SELECT 1 from FAKE_CUST where (TO_CHAR(DATE_TRAN,'MM') = TO_CHAR(F.DATE_TRAN,'MM')-2 OR TO_CHAR(DATE_TRAN,'MM') = TO_CHAR(F.DATE_TRAN,'MM')) AND 
                                        CUST_ID= F.CUST_ID AND TO_CHAR(DATE_TRAN,'YYYY')=TO_CHAR(F.DATE_TRAN,'YYYY'))

but is is showing all the entries in the table and not the required result.

ritika
  • 61
  • 1
  • 10

1 Answers1

0

  1. To Get 3 transactions are made by the user in the same month

    Something like... Group By getmonth(DATE_TRN) having count(getmonth(DATE_TRN)) >= 3

MySql :

SELECT CUST_ID, DATE_TRAN FROM XXX where cust_id in (SELECT cust_id from XXX Group By DATE_FORMAT(DATE_TRAN, '%m') having count(DATE_FORMAT(DATE_TRAN, '%m')) > 2)

  1. To Get 3 transactions are made by the user in 3 cons months

    Group By getmonth(DATE_TRN) having count(getmonth(DATE_TRN)) > 1, count(getmonth(DATE_TRN)-1) > 1, count(getmonth(DATE_TRN)-2) > 1

Raghunandan Krishnamurthy
  • 675
  • 5
  • 11
  • i am writing the query in oracle. I modified your query to but this is not working – ritika Jan 31 '14 at 09:40
  • SELECT CUST_ID, DATE_TRAN FROM fake_cust where cust_id in ( SELECT cust_id from fake_cust Group By EXTRACT(MONTH FROM DATE_TRaN) having ( count(EXTRACT(MONTH FROM DATE_TRAN)) > 1 AND count(EXTRACT(MONTH FROM DATE_TRAN)-1) > 1 AND count(EXTRACT(MONTH FROM DATE_TRAN)-2) > 1 ) ) – ritika Jan 31 '14 at 09:45