Re: date processing for end of week

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.