Prior week

slc1axj

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?

Jim_Knicely

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)

slc1axj

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

Jim_Knicely

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)

slc1axj

The problem with that though is when weeks pass over years