The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.

# Prior week

I need a Vertica statement to extract the prior week's data Sun - Sat. For example, if today is 12/2/2021, I need all dates between Sunday, Nov 21st and Saturday, Nov 27th (Sun-Sat)

Select aux_date from data
where aux_date between Nov 21 and Nov 27

I can't seem to figure this out for Vertica?

Tagged:

Probably a million ways to get the results...

```dbadmin=> SELECT aux_date FROM data ORDER BY aux_date;
aux_date
------------
2021-11-20
2021-11-21
2021-11-22
2021-11-23
2021-11-24
2021-11-25
2021-11-26
2021-11-27
2021-11-28
2021-12-26
2021-12-27
2021-12-28
2021-12-29
2021-12-30
2021-12-31
2022-01-01
2022-01-02
2022-01-03
2022-01-04
(19 rows)

dbadmin=> SELECT sysdate, * FROM data WHERE aux_date BETWEEN TRUNC(SYSDATE-7, 'IW')-1 AND NEXT_DAY(TRUNC(SYSDATE-7, 'IW'), 'SATURDAY');
sysdate           |  aux_date
----------------------------+------------
2021-12-02 22:15:59.324078 | 2021-11-21
2021-12-02 22:15:59.324078 | 2021-11-22
2021-12-02 22:15:59.324078 | 2021-11-23
2021-12-02 22:15:59.324078 | 2021-11-24
2021-12-02 22:15:59.324078 | 2021-11-25
2021-12-02 22:15:59.324078 | 2021-11-26
2021-12-02 22:15:59.324078 | 2021-11-27
(7 rows)

dbadmin=> SELECT * FROM data WHERE aux_date BETWEEN TRUNC('01/02/2022'::DATE, 'IW')-1 AND NEXT_DAY(TRUNC('01/02/2022'::DATE, 'IW')-1, 'SATURDAY');
aux_date
------------
2021-12-26
2021-12-27
2021-12-28
2021-12-29
2021-12-30
2021-12-31
2022-01-01
(7 rows)

```

• This seems like it works:

select CURRENT_DATE() - DATE_PART('DOW',CURRENT_DATE())-1 as Prior Saturday
select CURRENT_DATE() - DATE_PART('DOW',CURRENT_DATE())-7 as Sunday before prior Saturday

Here's another way that I think will work for you:

```dbadmin=> SELECT aux_date FROM data;
aux_date
------------
2021-11-20
2021-11-21
2021-11-22
2021-11-23
2021-11-24
2021-11-25
2021-11-26
2021-11-27
2021-11-28
(9 rows)

dbadmin=> SELECT * FROM data WHERE WEEK(aux_date) = WEEK('12/2/2021') - 1;
aux_date
------------
2021-11-21
2021-11-22
2021-11-23
2021-11-24
2021-11-25
2021-11-26
2021-11-27
(7 rows)
```
• The problem with that though is when weeks pass over years