Question: I have the query below. How come with the invoice\_dt set between 1/1/2021 - 5/31/2021... the output display ALL date ranges. How would I set it to look for dups based on pay\_cte within the date range?

```
with pay_cte( vendor_id,invoice_id, pay_amt,pay_cnt ) as (
select vendor_id,invoice_id, pay_amt,count(*)
from ps_voucher
where invoice_dt between '01-Jan-2021' and '31-May-2021'
group by vendor_id,invoice_id, pay_amt
having count(*)>1)
select t.vendor_id, t.voucher_id,t.INVOICE_ID,t.gross_amt, t.INVOICE_DT
from ps_voucher t
join pay_cte p on t.vendor_id=p.vendor_id and t.invoice_id = p.invoice_id
and t.gross_amt=p.pay_amt

```

Answer: 
The reason your query is returning all dates is due to the fact that your actual select statement that uses the `WITH` clause does not have any filtering for dates. It is likely that your `ps_voucher` table has records in it that have `INVOICE_DT` values that are out of the range of dates about which you are curious.

There are two ways you can filter results; the first is in the (inner) `JOIN` clause, and the second is by adding a `WHERE` clause in the query.

Option 1:

```
with pay_cte( vendor_id,invoice_id, pay_amt,pay_cnt ) as (
    select 
        vendor_id,
        invoice_id, 
        pay_amt,
        count(*)
    from ps_voucher
    where 
        invoice_dt between '01-Jan-2021' and '31-May-2021'
    group by 
        vendor_id,
        invoice_id,
        pay_amt
    having 
        count(*)>1
)
select 
    t.vendor_id, 
    t.voucher_id,
    t.INVOICE_ID,
    t.gross_amt,
    t.INVOICE_DT
from ps_voucher t
join pay_cte p 
    on t.vendor_id = p.vendor_id 
    and t.invoice_id = p.invoice_id
    and t.gross_amt=p.pay_amt
    and t.INVOICE_DT between '01-Jan-2021' and '31-May-2021'

```

Option 2:

```
with pay_cte( vendor_id,invoice_id, pay_amt,pay_cnt ) as (
    select 
        vendor_id,
        invoice_id, 
        pay_amt,
        count(*)
    from ps_voucher
    where 
        invoice_dt between '01-Jan-2021' and '31-May-2021'
    group by 
        vendor_id,
        invoice_id,
        pay_amt
    having 
        count(*)>1
)
select 
    t.vendor_id, 
    t.voucher_id,
    t.INVOICE_ID,
    t.gross_amt,
    t.INVOICE_DT
from ps_voucher t
join pay_cte p 
    on t.vendor_id = p.vendor_id 
    and t.invoice_id = p.invoice_id
    and t.gross_amt=p.pay_amt
where
    t.INVOICE_DT between '01-Jan-2021' and '31-May-2021'

```
You have to change your date for this format "yyyy-mm-dd"