Re: SQL, return last sundays date...

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

On Mon, Jun 29, 2015 at 11:21 PM, Birgitta Hauser <Hauser@xxxxxxxxxxxxxxx>
wrote:

Yet another way:

Select ...
Case When DayOfWeek_ISO(YourDate) <= DayOfYear(YourDate)
then Next_Day(YourDate - 8 days, 'SUN')
else Last_Day(YourDate - 1 Month) + 1 Day End
From ...

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"

-----Ursprüngliche Nachricht-----
Von: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von
Glenn Gundermann
Gesendet: Monday, 29.6 2015 23:43
An: Midrange Systems Technical Discussion
Betreff: Re: SQL, return last sundays date...

select *
from( values(
case
when dayofweek(current_date) = 1 then current_date
when dayofweek(current_date) > 1 and year(current_date -
(DAYOFWEEK(current_date)
- 1) DAYS) = year(current_date) then current_date -
(DAYOFWEEK(current_date)
- 1) DAYS
else date(year(current_date) concat '-01-01')
end)) t ("Last Sunday") ;


Yours truly,

Glenn Gundermann
Email: glenn.gundermann@xxxxxxxxx
Work: (416) 675-9200 ext. 89224
Cell: (416) 317-3144

Please consider sponsoring me for the Friends For Life Bike Rally
<
https://secure.e2rm.com/registrant/FundraisingPage.aspx?SID=5323935&LangPre
f=en-CA>
in support of the Toronto People with AIDS Foundation.


On 29 June 2015 at 17:29, Gerald Magnuson <gmagqcy.midrange@xxxxxxxxx>
wrote:

yes. that is what I need...


On Mon, Jun 29, 2015 at 4:28 PM, John Yeung
<gallium.arsenide@xxxxxxxxx>
wrote:

On Mon, Jun 29, 2015 at 4:54 PM, Gerald Magnuson
<gmagqcy.midrange@xxxxxxxxx> wrote:

now I just need to figure out how to get the beginning of week,
when

end

is

< 01-07...

Like Charles, I don't know what you mean by this.

But I will take a stab. My guess is that when the "input date" is
too close to the beginning of the year (so that there is no previous
Sunday in that year), you want to instead return Jan 1 of that year.

John Y.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe,
or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at http://archive.midrange.com/midrange-l.


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.