CODEWITHSUNDEEP
sqlteradata
ak1976
ak1976

Reputation: 5

SQL query for records based on columns value

I have two tables joining, I am looking to query records that have more than 3/three pmt_cde within last 60 days

TABLE1:
 ID_CHAIN
 PMT_CDE
 PMT_DATE
 PMT_AMOUNT

and

TABLE2:
 ID_CHAIN

I am looking for ID_CHAIN that has more than 3 of PMT_CDE = 536 in last 60 days.

P.S. this is a new question, I had a similar case earlier today but trust me this is totally new. Thanks.

Upvotes: 0

Views: 57

Answers (1)

MT0
MT0

Reputation: 168691

Since you haven't specified which RDBMS - this is an Oracle answer (but it should be relatively simple to translate the date arithmetic to any other RDBMS):

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE TABLE1 (
  ID_CHAIN    NUMBER,
  PMT_CDE     NUMBER,
  PMT_DATE    DATE,
  PMT_AMOUNT  NUMBER
);

INSERT INTO TABLE1 VALUES ( 1, 536, DATE '2015-07-23', 0 );
INSERT INTO TABLE1 VALUES ( 1, 536, DATE '2015-07-22', 0 );
INSERT INTO TABLE1 VALUES ( 1, 536, DATE '2015-07-21', 0 );
INSERT INTO TABLE1 VALUES ( 1, 536, DATE '2015-07-20', 0 );
INSERT INTO TABLE1 VALUES ( 2, 536, DATE '2015-07-22', 0 );
INSERT INTO TABLE1 VALUES ( 2, 536, DATE '2015-07-21', 0 );
INSERT INTO TABLE1 VALUES ( 2, 536, DATE '2015-05-01', 0 );
INSERT INTO TABLE1 VALUES ( 3, 536, DATE '2015-07-22', 0 );
INSERT INTO TABLE1 VALUES ( 3, 536, DATE '2015-07-21', 0 );
INSERT INTO TABLE1 VALUES ( 4, 536, DATE '2015-02-03', 0 );
INSERT INTO TABLE1 VALUES ( 4, 536, DATE '2015-02-02', 0 );
INSERT INTO TABLE1 VALUES ( 4, 536, DATE '2015-02-01', 0 );

Query 1:

SELECT ID_CHAIN
FROM   TABLE1
WHERE  PMT_DATE >= CURRENT_DATE - INTERVAL '60' DAY
AND    PMT_CDE  = 536
GROUP BY ID_CHAIN
HAVING COUNT(ID_CHAIN) > 3

Results:

| ID_CHAIN |
|----------|
|        1 |

Upvotes: 1

Related Questions