Beginning / End of week
Latimer, Richard
richard.latimer@airways.co.nz
Wed, 10 Nov 2004 10:40:29 +1300
This is a multi-part message in MIME format.
------_=_NextPart_001_01C4C6A4.BB4F9E70
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
David,
=20
I was taught / told to avoid all the datatype conversion and string
manipulation commands like the plague on the AS/400 since they are
apparently very slow to execute, hence keeping it "maths based".
Incidentally that first define only works on the 400's because few other
platforms store the date that way round.=20
=20
Elegant is not a word I would apply to the first piece (that's actually
mine !!) but the second define is extremely clever ... Every other
response to my query proposed using an intermediate subfile ....
=20
regards
Richard
-----Original Message-----
From: David Morrison - Corporate
[mailto:dmorrison@mcbrideelectric.com]=20
Sent: Wednesday, 10 November 2004 10:24 a.m.
To: Latimer, Richard; powerh-l@lists.sowder.com
Subject: RE: Beginning / End of week
=09
=09
Richard,
=20
With all due respect, the first DEFINE formula is short, but not
elegant. Speaking as a mathematician, using NCONVERT/SUBSTRING/CCONVERT
would be more elegant than the FLOOR technique (and would execute
infinitesimally faster, I'd bet).
=20
Thanks.
=20
David Morrison
McBride Electric
-----Original Message-----
From: powerh-l-admin@lists.sowder.com
[mailto:powerh-l-admin@lists.sowder.com]On Behalf Of Latimer, Richard
Sent: Tuesday, November 09, 2004 1:05 PM
To: powerh-l@lists.sowder.com
Subject: Beginning / End of week
=09
=09
Hi Viet,=20
there are no specific functions that will do exactly
what you want but:=20
- the "days" function will return an integer from a
date=20
- you can use mod(days(date),7) to get the day of the
week where 0 =3D Sunday, 1 =3D Monday etc=20
the example below is from an automated report that we
run which is for the first Tuesday of a month calculated backwards from
the date of the last day in the month.=20
"Thank you to all who took the time to respond.=20
I have adapted the extremely elegant code supplied by
Paul Howard and it works exactly the way I want !=20
I must admit it took a while to get my head around how
it worked and I ended up adding a lot of comments so I can figure it out
in the future! For what it's worth here's how I implemented it (note
that on the AS400 the date is stored as an 8 digit number yyymmdd and
can be treated as a number or a
date) :=20
ACCESS control_file
=09
; The report is expected to return movement data for
; "the 1st Tuesday of last month". This code derives the
; corresponding date to use in the eventual choose
statement. =20
; STEP 1 is to find the date of the "1st day of last
month". =20
; The last day of the month is on the control file so
start =20
; from there.
; STEP 2 is to find out which day of the week the that
was. =20
; Dividing the day count by 7 and reviewing the
remainder (MOD) =20
; gives the weekday where 0 =3D Sunday , 1 =3D Monday etc
; STEP 3 is to calculate how many days to add to the
"1st" to =20
; get to the next Tuesday. The INDEX function returns
the position ; in the string of each weekday, this number is then added
to the=20
; 1st of the month. Note that Tuesday (day 2) is not
needed in =20
; the lookup string . . .
=20
DEFINE d_days_1st num*6 &
=3D DAYS(FLOOR(dte_current_monthend / 100)*100) + 1
=09
DEFINE d_dte_tue DATE &
=3D DATE(d_days_1st +
INDEX('106543',CCON(MOD(d_days_1st,7)))) "=20
regards=20
Richard=20
-----Original Message-----=20
From: powerh-l-admin@lists.sowder.com
[mailto:powerh-l-admin@lists.sowder.com
<mailto:powerh-l-admin@lists.sowder.com> ] On Behalf Of Viet Nguyen=20
Sent: Wednesday, 10 November 2004 9:37 a.m.=20
To: Robert Edis; PH List=20
Subject: Beginning / End of week=20
Hi all,=20
Please tell me if there are functions to return the
date of Monday and Friday of the current week in Quiz? I need to autorun
reports from Including data from Monday-Friday weekly.
I am in IBM AIX=20
Thanks.=20
Viet.=20
=09
=09
--=20
=09
**********************************************************************
This electronic message together with any attachments is
confidential. If
you receive it in error: (i) you must not use, disclose,
copy or retain
it; (ii) please contact the sender immediately by reply
email and then
delete the emails. Views expressed in this email may not
be those of the
Airways Corporation of New Zealand Limited
=09
**********************************************************************
=09
------_=_NextPart_001_01C4C6A4.BB4F9E70
Content-Type: text/html;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD><TITLE>Message</TITLE>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Dus-ascii">
<META content=3D"MSHTML 6.00.2800.1400" name=3DGENERATOR></HEAD>
<BODY>
<DIV><SPAN class=3D138282821-09112004><FONT face=3DArial color=3D#0000ff =
size=3D2>David,</FONT></SPAN></DIV>
<DIV><SPAN class=3D138282821-09112004><FONT face=3DArial color=3D#0000ff =
size=3D2></FONT></SPAN> </DIV>
<DIV><SPAN class=3D138282821-09112004><FONT face=3DArial color=3D#0000ff =
size=3D2>I was=20
taught / told to avoid all the datatype conversion and string =
manipulation=20
commands like the plague on the AS/400 since they =
are apparently=20
very slow to execute, hence keeping it "maths based". Incidentally that =
first=20
define only works on the 400's because few other platforms store =
the date=20
that way round. </FONT></SPAN></DIV>
<DIV><SPAN class=3D138282821-09112004><FONT face=3DArial color=3D#0000ff =
size=3D2></FONT></SPAN> </DIV>
<DIV><SPAN class=3D138282821-09112004><FONT face=3DArial color=3D#0000ff =
size=3D2>Elegant is not a word I would apply to the first piece =
(that's=20
actually mine !!) but the second define is extremely clever ... Every =
other=20
response to my query proposed using an intermediate subfile=20
....</FONT></SPAN></DIV>
<DIV><SPAN class=3D138282821-09112004><FONT face=3DArial color=3D#0000ff =
size=3D2></FONT></SPAN> </DIV>
<DIV><SPAN class=3D138282821-09112004><FONT face=3DArial color=3D#0000ff =
size=3D2>regards</FONT></SPAN></DIV>
<DIV><SPAN class=3D138282821-09112004><FONT face=3DArial color=3D#0000ff =
size=3D2>Richard</FONT></SPAN></DIV>
<BLOCKQUOTE dir=3Dltr style=3D"MARGIN-RIGHT: 0px">
<DIV></DIV>
<DIV class=3DOutlookMessageHeader lang=3Den-us dir=3Dltr =
align=3Dleft><FONT=20
face=3DTahoma size=3D2>-----Original Message-----<BR><B>From:</B> =
David Morrison -=20
Corporate [mailto:dmorrison@mcbrideelectric.com] <BR><B>Sent:</B> =
Wednesday,=20
10 November 2004 10:24 a.m.<BR><B>To:</B> Latimer, Richard;=20
powerh-l@lists.sowder.com<BR><B>Subject:</B> RE: Beginning / End of=20
week<BR><BR></FONT></DIV>
<DIV><SPAN class=3D654291921-09112004><FONT color=3D#0000ff=20
size=3D2>Richard,</FONT></SPAN></DIV>
<DIV><SPAN class=3D654291921-09112004><FONT color=3D#0000ff=20
size=3D2></FONT></SPAN> </DIV>
<DIV><SPAN class=3D654291921-09112004><FONT color=3D#0000ff =
size=3D2>With all due=20
respect, the first DEFINE formula is short, but not elegant. =
Speaking as=20
a mathematician, using NCONVERT/SUBSTRING/CCONVERT would be more =
elegant than=20
the FLOOR technique (and would execute infinitesimally faster, I'd=20
bet).</FONT></SPAN></DIV>
<DIV><SPAN class=3D654291921-09112004><FONT color=3D#0000ff=20
size=3D2></FONT></SPAN> </DIV>
<DIV><SPAN class=3D654291921-09112004><FONT color=3D#0000ff=20
size=3D2>Thanks.</FONT></SPAN></DIV>
<DIV><SPAN class=3D654291921-09112004><FONT color=3D#0000ff=20
size=3D2></FONT></SPAN> </DIV>
<DIV><SPAN class=3D654291921-09112004><FONT color=3D#0000ff =
size=3D2>David=20
Morrison</FONT></SPAN></DIV>
<DIV><SPAN class=3D654291921-09112004><FONT color=3D#0000ff =
size=3D2>McBride=20
Electric</FONT></SPAN></DIV>
<BLOCKQUOTE>
<DIV class=3DOutlookMessageHeader dir=3Dltr align=3Dleft><FONT =
face=3DTahoma=20
size=3D2>-----Original Message-----<BR><B>From:</B>=20
powerh-l-admin@lists.sowder.com=20
[mailto:powerh-l-admin@lists.sowder.com]<B>On Behalf Of </B>Latimer, =
Richard<BR><B>Sent:</B> Tuesday, November 09, 2004 1:05 =
PM<BR><B>To:</B>=20
powerh-l@lists.sowder.com<BR><B>Subject:</B> Beginning / End of=20
week<BR><BR></FONT></DIV><!-- Converted from text/rtf format --><BR>
<P><SPAN lang=3Den-nz><FONT face=3DArial size=3D2>Hi =
Viet,</FONT></SPAN> </P>
<P><SPAN lang=3Den-nz><FONT face=3DArial size=3D2>there are no =
specific functions=20
that will do exactly what you want but:</FONT></SPAN> <BR><SPAN=20
lang=3Den-nz><FONT face=3DArial size=3D2> - the "days" function =
will return=20
an integer from a date</FONT></SPAN> <BR><SPAN lang=3Den-nz><FONT =
face=3DArial=20
size=3D2> - you can use mod(days(date),7) to get the day of the =
week=20
where 0 =3D Sunday, 1 =3D Monday etc</FONT></SPAN> </P>
<P><SPAN lang=3Den-nz><FONT face=3DArial size=3D2>the example below =
is from an=20
automated report that we run which is for the first Tuesday of a =
month=20
calculated backwards from the date of the last day in the month.=20
</FONT></SPAN></P>
<P><SPAN lang=3Den-nz><FONT face=3DArial color=3D#0000ff =
size=3D2>"Thank you to all=20
who took the time to respond. </FONT></SPAN></P>
<P><SPAN lang=3Den-nz><FONT face=3DArial color=3D#0000ff size=3D2>I =
have adapted the=20
extremely elegant code supplied by Paul Howard and it works exactly =
the way=20
I want !</FONT></SPAN> </P>
<P><SPAN lang=3Den-nz><FONT face=3DArial color=3D#0000ff size=3D2>I =
must admit it=20
took a while to get my head around how it worked and I ended up =
adding a lot=20
of comments so I can figure it out in the future! For what it's =
worth here's=20
how I implemented it (note that on the AS400 the date is stored as =
an 8=20
digit number yyymmdd and can be treated as a number or =
a</FONT></SPAN></P>
<P><SPAN lang=3Den-nz><FONT face=3DArial color=3D#0000ff =
size=3D2>date)=20
:</FONT></SPAN> </P>
<P><SPAN lang=3Den-nz><FONT face=3DArial color=3D#0000ff =
size=3D2>ACCESS=20
=
control_file &=
nbsp; &n=
bsp; &nb=
sp; =20
</FONT></SPAN><BR><SPAN lang=3Den-nz><FONT face=3DArial =
color=3D#0000ff=20
=
size=3D2> &nbs=
p;  =
; =
&=
nbsp; &n=
bsp; =20
</FONT></SPAN><BR><SPAN lang=3Den-nz><FONT face=3DArial =
color=3D#0000ff size=3D2>;=20
The report is expected to return movement data=20
=
for &nbs=
p; =20
</FONT></SPAN><BR><SPAN lang=3Den-nz><FONT face=3DArial =
color=3D#0000ff size=3D2>;=20
"the 1st Tuesday of last month". This code derives=20
the =20
</FONT></SPAN><BR><SPAN lang=3Den-nz><FONT face=3DArial =
color=3D#0000ff size=3D2>;=20
corresponding date to use in the eventual choose=20
statement. </FONT></SPAN><BR><SPAN =
lang=3Den-nz><FONT=20
face=3DArial color=3D#0000ff size=3D2>; STEP 1 is to find the date =
of the "1st day=20
of last month". =
</FONT></SPAN><BR><SPAN=20
lang=3Den-nz><FONT face=3DArial color=3D#0000ff size=3D2>; The last =
day of the month=20
is on the control file so start =20
</FONT></SPAN><BR><SPAN lang=3Den-nz><FONT face=3DArial =
color=3D#0000ff size=3D2>;=20
from=20
=
there. &=
nbsp; &n=
bsp; &nb=
sp; &nbs=
p; =20
</FONT></SPAN><BR><SPAN lang=3Den-nz><FONT face=3DArial =
color=3D#0000ff size=3D2>;=20
STEP 2 is to find out which day of the week the that=20
was. </FONT></SPAN><BR><SPAN=20
lang=3Den-nz><FONT face=3DArial color=3D#0000ff size=3D2>; Dividing =
the day count by=20
7 and reviewing the remainder (MOD) =
</FONT></SPAN><BR><SPAN=20
lang=3Den-nz><FONT face=3DArial color=3D#0000ff size=3D2>; gives the =
weekday where 0=20
=3D Sunday , 1 =3D Monday=20
=
etc &nbs=
p;=20
</FONT></SPAN><BR><SPAN lang=3Den-nz><FONT face=3DArial =
color=3D#0000ff size=3D2>;=20
STEP 3 is to calculate how many days to add to the "1st"=20
to </FONT></SPAN><BR><SPAN =
lang=3Den-nz><FONT=20
face=3DArial color=3D#0000ff size=3D2>; get to the next Tuesday. The =
INDEX=20
function returns the position ; in the string of each weekday, this =
number=20
is then added to the </FONT></SPAN></P>
<P><SPAN lang=3Den-nz><FONT face=3DArial color=3D#0000ff size=3D2>; =
1st of the=20
month. Note that Tuesday (day 2) is not needed in =20
</FONT></SPAN><BR><SPAN lang=3Den-nz><FONT face=3DArial =
color=3D#0000ff size=3D2>;=20
the lookup string . .=20
=
. =
&=
nbsp; &n=
bsp; =20
</FONT></SPAN><BR><SPAN lang=3Den-nz><FONT face=3DArial =
color=3D#0000ff=20
size=3D2> </FONT></SPAN> </P>
<P><SPAN lang=3Den-nz><FONT face=3DArial color=3D#0000ff =
size=3D2>DEFINE d_days_1st=20
num*6=20
=
& &n=
bsp; &nb=
sp; &nbs=
p; =20
</FONT></SPAN><BR><SPAN lang=3Den-nz><FONT face=3DArial =
color=3D#0000ff=20
size=3D2> =3D DAYS(FLOOR(dte_current_monthend / =
100)*100) +=20
1 =20
</FONT></SPAN><BR><SPAN lang=3Den-nz><FONT face=3DArial =
color=3D#0000ff=20
=
size=3D2> &nbs=
p;  =
; =
&=
nbsp; &n=
bsp; =20
</FONT></SPAN><BR><SPAN lang=3Den-nz><FONT face=3DArial =
color=3D#0000ff=20
size=3D2>DEFINE d_dte_tue DATE=20
=
& &n=
bsp; &nb=
sp; &nbs=
p; =20
</FONT></SPAN><BR><SPAN lang=3Den-nz><FONT face=3DArial =
color=3D#0000ff=20
size=3D2> =3D DATE(d_days_1st +=20
INDEX('106543',CCON(MOD(d_days_1st,7)))) "</FONT></SPAN> </P><BR>
<P><SPAN lang=3Den-nz><FONT face=3DArial =
size=3D2>regards</FONT></SPAN> <BR><SPAN=20
lang=3Den-nz><FONT face=3DArial size=3D2>Richard</FONT></SPAN> </P>
<P><SPAN lang=3Den-nz><FONT face=3DArial size=3D2>-----Original=20
Message-----</FONT></SPAN> <BR><SPAN lang=3Den-nz><FONT face=3DArial =
size=3D2>From: powerh-l-admin@lists.sowder.com [</FONT></SPAN><A=20
href=3D"mailto:powerh-l-admin@lists.sowder.com"><SPAN =
lang=3Den-nz><U><FONT=20
face=3DArial color=3D#0000ff=20
=
size=3D2>mailto:powerh-l-admin@lists.sowder.com</FONT></U></SPAN></A><SPA=
N=20
lang=3Den-nz><FONT face=3DArial size=3D2>] On Behalf Of Viet =
Nguyen</FONT></SPAN>=20
<BR><SPAN lang=3Den-nz><FONT face=3DArial size=3D2>Sent: Wednesday, =
10 November=20
2004 9:37 a.m.</FONT></SPAN> <BR><SPAN lang=3Den-nz><FONT =
face=3DArial=20
size=3D2>To: Robert Edis; PH List</FONT></SPAN> <BR><SPAN =
lang=3Den-nz><FONT=20
face=3DArial size=3D2>Subject: Beginning / End of week</FONT></SPAN> =
</P><BR>
<P><SPAN lang=3Den-nz><FONT face=3DArial size=3D2>Hi =
all,</FONT></SPAN> <BR><SPAN=20
lang=3Den-nz><FONT face=3DArial size=3D2> Please tell me =
if there are=20
functions to return the date of Monday and Friday of the current =
week in=20
Quiz? I need to autorun reports from Including data from =
Monday-Friday=20
weekly.</FONT></SPAN></P>
<P><SPAN lang=3Den-nz><FONT face=3DArial size=3D2>I am in IBM =
AIX</FONT></SPAN>=20
</P>
<P><SPAN lang=3Den-nz><FONT face=3DArial =
size=3D2>Thanks.</FONT></SPAN> <BR><SPAN=20
lang=3Den-nz><FONT face=3DArial size=3D2>Viet.</FONT></SPAN> =
</P><FONT=20
size=3D3><BR><BR>--=20
=
<BR>*********************************************************************=
*<BR>This=20
electronic message together with any attachments is confidential. =
If<BR>you=20
receive it in error: (i) you must not use, disclose, copy or =
retain<BR>it;=20
(ii) please contact the sender immediately by reply email and =
then<BR>delete=20
the emails. Views expressed in this email may not be those of =
the<BR>Airways=20
Corporation of New Zealand=20
=
Limited<BR>**************************************************************=
********<BR></BLOCKQUOTE></BLOCKQUOTE></FONT></BODY></HTML>
=00
------_=_NextPart_001_01C4C6A4.BB4F9E70--