Re: SQL, return last sundays date...

On 30-Jun-2015 07:45 -0600, Gerald Magnuson wrote:

On 29-Jun-2015 14:14 -0600, Gerald Magnuson wrote:

I have a table of week end dates, "most" of them are Saturday,
so I could just say (date - 6),
but some of my days of week vary, start of year, end of year...

what SQL do I use to return the previous closest Sunday?

(bonus points to help me when I need to start on "01-01-xxxx",
not Sunday)

this (almost) works, except for beginning and end of year:

SELECT idate.idate(enddt) as end_date
, (idate.idate(enddt)-6 days) as start_date
from week_table
;

thanks to all who helped me...
here is my finished query,
it receives a WeekEnding date, and returns the Previous 52 week start
and end dates...

Select Begin_Date, End_Date
FROM
( SELECT
Case
When DayOfWeek_ISO(idate.idate(FWYMD))
<=DayOfYear(idate.idate(FWYMD))
then Next_Day(idate.idate(FWYMD) - 8 days, 'SUN')
else Last_Day(idate.idate(FWYMD) - 1 Month) + 1 Day
End as Begin_Date
, idate.idate(FWYMD) as End_Date
FROM TIMELIB.FWC
WHERE idate.idate(FWYMD)<= CURRENT_DATE
order by FWYMD DESC
FETCH FIRST 52 ROWS ONLY
) as subSelect
ORDER BY Begin_Date ASC

There would seem little reason to avoid storing the previously calculated values in the [apparently few rows of the] week-data table WEEK_TABLE rather than calculating the values each time a [e.g. the above] query runs.?

Seems better to have formulated the expression to calculate the prior Sunday for use in a request to UPDATE WEEK_TABLE Set BgnDt = ... statement, after a request to ALTER TABLE WEEK_TABLE ADD COLUMN BgnDt ...

BTW, in general practice, the predicate in the WHERE clause best would be coded instead [to take advantage of an index on FWYMD] as:

FWYMD<=function_DATE_into_FWYMD_type_and_fmt(CURRENT_DATE)