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

FWiW: While there are just 52 weeks _ending on_ a Saturday in 2015, there is additionally, a 53rd week ending on Thursday 31-Dec-2015 that has a _previous Sunday_ of 27-Dec-2015; i.e. there are a total of 53 weeks that either start on a Sunday or /start on/ 01-Jan of the year. If an ending date beyond 27-Dec-2015 is ignored or does not exist, then the issue from the OP regarding subtracting 6 DAYS from EndDt would exist only for the first end-of-week at the "start of year", and therefore seems probable, that the 52 ROWS ONLY in the query just above [from the most recent quoted message] is too limiting.?

FWiW: To limit the query to just two builtin scalar functions [perhaps per use of a release without the NEXT_DAY and LAST_DAY scalars], and necessarily should reduce the complexity of the query, the following expressions should suffice:

SELECT
idate.idate(enddt) as end_date
, idate.idate(enddt)
-( case when dayofweek_iso(idate.idate(enddt))
>= dayofyear (idate.idate(enddt))
then dayofyear(idate.idate(enddt)) - 1
else dayofweek_iso(idate.idate(enddt))
end ) DAYS as str_date /* prior Sun, or 01-Jan */
from week_table

I am unsure if the following revisions to the prior query might limit the number of times the User Defined Function (UDF) iDate [used to convert the non-date value to a DATE data type] gets invoked; though perhaps the query engine is smart enough to achieve the effect with the prior version of the query?:

SELECT
dt as end_date
, dt
-( case when dayofweek_iso(dt)
>= dayofyear (dt)
then dayofyear(dt) - 1
else dayofweek_iso(dt)
end ) DAYS
as str_date /* prior Sunday, or 01-Jan */
from ( select idate.idate(enddt) as dt
from week_table
) as tdv