All Forums Database
jclasen 5 posts Joined 11/05
07 Jan 2008
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 usingwhere 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.

Jim
Fred 1096 posts Joined 08/04
07 Jan 2008

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...

jclasen 5 posts Joined 11/05
07 Jan 2008

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.

Jim

Fred 1096 posts Joined 08/04
10 Jan 2008

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

jclasen 5 posts Joined 11/05
10 Jan 2008

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

Jim

You must sign in to leave a comment.