Re: RPG Embedded SQL: select latest date in a week

[Michael Schutte <mschutte369@xxxxxxxxx>]

Off the top of my head. 

With cteDaysWith3recs as (
Select date
From file
Group by date
Having count(1) =3
)
,
cteWeekMaxDate as (
Select week(date), max(date) as date
From cteDaysWith3Recs
Group by week(date)
)
Select * 
from file 
where date in (select date from cteWeekMaxDate)

Sent from my iPhone

> On Feb 26, 2016, at 10:42 PM, Booth Martin <booth@xxxxxxxxxxxx> wrote:
>
> I am going in circles on this.  Perhaps its simpler than I am making it.
>
> 3 rows are added to a file most days.  Lets say, to give a perspective, that somewhere around 300 days/year there are 3 records added.  Never 4, never 2.  Its 3 or none.  I want 3 rows for every week of the year, in descending order, but only the last active day in each week. Normally it is day 7, sometimes day 6, rarely day 5.
>
>
> -- 
> This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L) mailing list
> To post a message email: RPG400-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: RPG400-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/rpg400-l.
>
> Please contact support@xxxxxxxxxxxx for any subscription related questions.