Re: date processing for end of week

["David Wright" <opendave@xxxxxxxxx>]

This would be an all SQL solution, using the CURRENT DATE and a field called
NetDays.  The example below shows the case statements to show the first two
weekly buckets.  The first half of each CASE calculates the Friday after the
Current Date plus Net Days.  The second half calculates the current Friday,
then next Friday, etc...

You could use SUM() around the CASE statements if you are just looking for
totals.  You would need to duplicate the 2nd CASE statement for each
additional bucket you want, incrementing the number of days you add to the
current week.

select
  case when
   (current date + NetDys days +
      case when dayofweek(current date + NetDys days) < 7 then
        6-dayofweek(current date + NetDys days) else 6 end days) =
   (current date +
      case when dayofweek(current date) < 6 then
        6-dayofweek(current date) else 0 end days)
   then AmtDue else 0 end as Week1,
  case when
   (current date + NetDys days +
      case when dayofweek(current date + NetDys days) < 7 then
        6-dayofweek(current date + NetDys days) else 6 end days) =
   (current date + 7 days +
      case when dayofweek(current date) < 6 then
        6-dayofweek(current date) else 0 end days)
   then AmtDue else 0 end as Week2
from
  OpenOrds

Hope this helps,
Dave

On Wed, Nov 12, 2008 at 9:02 AM, Adam West <adamster@xxxxxxxxx> wrote:

> HI I have a need to look at a file of back orders, and assume, for the sake
> of the report, that each back order will be shipped today and the customer
> will pay by the end of the terms date that they have, e.g. net 10 days, net
> 30 days etc.
>
> This will then be printed as a report to the week ending day of Friday for
> that particular week in the future.
>
> So if order A, is due in 20 days, it would be part of a bucket for that
> week. But you never know your starting point, it could be 10,20,25, 30,etc
> days into the future. How then can you know when Friday is in that
> particular week in an automated fashion?
> TIA,
>
>
>
> --
> This is the RPG programming on the AS400 / 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.