Anthony Presley wrote:

> Hello Group.....
>
> I've come across another interesting problem (aren't they all....),
> and I'm petitioning for some help. Here goes.....
>
> We have a pay / commission setup which doesn't really do any
> "time-traveling" for accounting. IE, someone pays, late in the
> commission period, we run commissions, the sales person gets a check,
> and then the next day the payment bounces. Oops. Problem being, if
> the client pays again, the sales person gets credited again. Which
> means, lost $$$'s because they got paid twice for a single client.
>
> To solve this problem, I need to be able to figure out previous
> payments, and their dates (done), and then check back-in-time to see
> if during any payperiod (for which they have payments) the client was
> fully paid. Seems simple enough, although it's a few queries, if the
> report is taking 2 minutes instead of 1, that's fine.
>
> How do I find the payperiods. Depending on the type of client, the
> payperiod could be Type 1: (1st - 15th, 16th - end of month) OR Type
> 2: (1st - end of month). Once I've fetched the date from the db, say
> 09/12/2003 and determined that it's Type 1, how do I say "ah, yes,
> this falls in Type 1"? [I think I have that solved, with a simple <=
> or >= ... anything easier?] BUT, if it falls after the 16th, or is
> Type 2, what could I use (in I4GL) to find the last date in the month
> (short of a text file or table w/ payperiods)?

To find the last day of this month, use:

MDY(MONTH(TODAY + 1 UNITS MONTH),1,YEAR(TODAY))-1

Or substitute TODAY with any date to find the last day of the month in
which that date falls.

Cheers,
--
Mark.

+----------------------------------------------------------+-----------+
| Mark D. Stock mailto:mdstock@MydasSolutions.com |//////// /|
| Mydas Solutions Ltd http://MydasSolutions.com |///// / //|
| +-----------------------------------+//// / ///|
| |We value your comments, which have |/// / ////|
| |been recorded and automatically |// / /////|
| |emailed back to us for our records.|/ ////////|
+----------------------+-----------------------------------+-----------+

sending to informix-list

[snip]
>
> MDY(MONTH(TODAY + 1 UNITS MONTH),1,YEAR(TODAY))-1
>
[snip]

Using TODAY + 1 UNITS MONTH will fail if today is 31st day of the month and
next month only has 30 days. The same happens on january 29th (if it's not a
leap year), 30th and 31st.

Gorazd

"Mark D. Stock" <mdstock@mydassolutions.com> writes:
> To find the last day of this month, use:
>
> MDY(MONTH(TODAY + 1 UNITS MONTH),1,YEAR(TODAY))-1

It's a little trickier than that to get right for all values of TODAY.
Look at this and then think about it a little more:

MDY(MONTH(TODAY) MOD 12 + 1, 1, YEAR(TODAY) + MONTH(TODAY) / 12) - 1

It would be better defensive programming, however, to assign the value
returned by TODAY to a DATE variable and then use the variable in the
above formula.

--
Forte International, P.O. Box 1412, Ridgecrest, CA 93556-1412
Ronald Cole <ronald@forte-intl.com> Phone: (760) 499-9142
President, CEO Fax: (760) 499-9152
My GPG fingerprint: C3AF 4BE9 BEA6 F1C2 B084 4A88 8851 E6C8 69E3 B00B

Thanks for the help.

I ended up not even using any of the SQL, because the specs AGAIN
changed, and commissions are now done twice monthly, based on the type
of contract and the sales person. I ended up having to create a table
of "pay periods" and search for payments within the payperiod.
However, I can say ... it works.

Thanks so much.

--Anthony

"Gorazd Hribar Rajteriè" <REMOVE_gorazd.hribar@telekom.si> wrote in message news:<VBUnb.5001$2B6.962661@news.siol.net>...
> [snip]
> >
> > MDY(MONTH(TODAY + 1 UNITS MONTH),1,YEAR(TODAY))-1
> >
> [snip]
>
> Using TODAY + 1 UNITS MONTH will fail if today is 31st day of the month and
> next month only has 30 days. The same happens on january 29th (if it's not a
> leap year), 30th and 31st.
>
> Gorazd