0

I have a Policies table.


  • policyId
  • status ("Pending" or "Issued")
  • pendDate
  • issueDate

Now I need get all pending Policies with a pendDate in the month of October AND all issued Policies with a issueDate in the month of October.

I'm using postgreSQL 9.6. These dates are nullable so I cant use a simple AND. I need only the pending and issued in that month so ORs dont work.

I solved with two queries but now more statuses are being added and I want to consolidate somehow. Possible?

0
4

If I understand correctly you can get it by using an OR condition in WHERE clause:

create table Policies 
(
    policyId int,
    status   varchar(100),
    pendDate date,
    issueDate date
);

insert into Policies values
(1, 'Pending', '20171001', null),
(2, 'Pending', '20171002', null),
(3, 'Pending', '20171003', null),
(4, 'Pending', '20171101', null),
(5, 'Pending', '20171102', null),
(6, 'Issue', null, '20171002'),
(7, 'Issue', null, '20171003'),
(8, 'Isuue', null, '20171101');
✓

8 rows affected
select policyId,
       status,
       pendDate,
       issueDate
from   Policies
where  (status = 'Pending' and extract(year from pendDate) = 2017 and extract(month from pendDate) = 10)
or     (status = 'Issue' and extract(year from issueDate) = 2017 and extract(month from issueDate) = 10)
policyid | status  | penddate   | issuedate 
-------: | :------ | :--------- | :---------
       1 | Pending | 2017-10-01 | null      
       2 | Pending | 2017-10-02 | null      
       3 | Pending | 2017-10-03 | null      
       6 | Issue   | null       | 2017-10-02
       7 | Issue   | null       | 2017-10-03

dbfiddle here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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