Finding the last full week

Database
Enthusiast

Finding the last full week

I am trying to get data from the previous week for a weekly report...for example on Monday the report would run for the previous week (Sunday - Saturday).
I am currently using

where week (trans_dt) = week (date) - 1
and
year (trans_dt) = year (trans_dt)

Not very eloquent, but it works except for when there is a new year.
4 REPLIES
Teradata Employee

Re: Finding the last full week

How about something like this:

FROM
...
(Select calendar_date-day_of_week-6 as min_date, calendar_date-day_of_week as max_date
from sys_calendar.calendar where calendar_date = current_date) date_range
...
WHERE trans_date between date_range.min_date and date_range.max_date
...
Enthusiast

Re: Finding the last full week

that does give me the dates I need, but I am having trouble incorporating this into my query.

How do I incorporate it into my query the way you wrote it? I'm not sure what I would join on.
Teradata Employee

Re: Finding the last full week

Is this join syntax clearer?
...
INNER JOIN (Select calendar_date-day_of_week-6 as min_date, calendar_date-day_of_week as max_date
from sys_calendar.calendar where calendar_date = current_date) date_range
ON trans_date between date_range.min_date and date_range.max_date
...
Enthusiast

Re: Finding the last full week

Yes, not sure what my major malfunction was...but thank you very much.