Beginning / End of week

Tue, 9 Nov 2004 13:24:13 -0800

This is a multi-part message in MIME format.

------_=_NextPart_001_01C4C6A2.75E4982F
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

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

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                                              =20
                                                                 =20
; The report is expected to return movement data for             =20
; "the 1st Tuesday of last month". This code derives the         =20
; 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.                                                    =20
; 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            =20
; 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
 =20

DEFINE d_days_1st num*6 &                                       =20
    =3D DAYS(FLOOR(dte_current_monthend / 100)*100) + 1           =20
                                                                =20
DEFINE d_dte_tue  DATE &                                        =20
    =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

--=20
**********************************************************************
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
**********************************************************************

------_=_NextPart_001_01C4C6A2.75E4982F
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
charset=3Diso-8859-1">
<TITLE>Beginning / End of week</TITLE>

<META content=3D"MSHTML 6.00.2800.1458" name=3DGENERATOR></HEAD>
<BODY>
<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>&nbsp;</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.&nbsp; =
Speaking as a=20
mathematician, using NCONVERT/SUBSTRING/CCONVERT would be more elegant =
than the=20
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>&nbsp;</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>&nbsp;</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 =
[mailto:powerh-l-admin@lists.sowder.com]<B>On=20
  Behalf Of </B>Latimer, Richard<BR><B>Sent:</B> Tuesday, November 09, =
2004 1:05=20
  PM<BR><B>To:</B> powerh-l@lists.sowder.com<BR><B>Subject:</B> =
Beginning / End=20
  of 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>&nbsp;- the "days" function =
will return an=20
  integer from a date</FONT></SPAN> <BR><SPAN lang=3Den-nz><FONT =
face=3DArial=20
  size=3D2>&nbsp;- you can use mod(days(date),7) to get the day of the =
week where=20
  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 =

calculated backwards from the date of the last day in the month.=20
 
 "Thank you to all=20
 who took the time to respond. 
 I =
have adapted the=20
 extremely elegant code supplied by Paul Howard and it works exactly =
the way I=20
 want ! 
 I =
must admit it took=20
 a while to get my head around how it worked and I ended up adding a =
lot of=20
 comments so I can figure it out in the future! For what it's worth =
here's how=20
 I implemented it (note that on the AS400 the date is stored as an 8 =
digit=20
 number yyymmdd and can be treated as a number or a
 date)=20
 : 
 ACCESS=20
 =
control_file&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n=
bsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n=
bsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
 ; The=20
 report is expected to return movement data=20
 =
for&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p;&nbsp;=20
 ;=20
 "the 1st Tuesday of last month". This code derives=20
 the&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
 ;=20
 corresponding date to use in the eventual choose=20
 statement.&nbsp;&nbsp;&nbsp;&nbsp; <FONT=20
 face=3DArial color=3D#0000ff size=3D2>; STEP 1 is to find the date of =
the "1st day=20
 of last month".&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <SPAN=20
 lang=3Den-nz>; The last =
day of the month=20
 is on the control file so start&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
 ;=20
 from=20
 =
there.&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n=
bsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p;&nbsp;&nbsp;&nbsp;&nbsp;=20
 ;=20
 STEP 2 is to find out which day of the week the that=20
 was.&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <SPAN=20
 lang=3Den-nz>; Dividing =
the day count by 7=20
 and reviewing the remainder (MOD)&nbsp;&nbsp; <SPAN=20
 lang=3Den-nz>; gives the =
weekday where 0 =3D=20
 Sunday , 1 =3D Monday=20
 =
etc&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p;=20
 ;=20
 STEP 3 is to calculate how many days to add to the "1st"=20
 to&nbsp;&nbsp;&nbsp;&nbsp; ; get to the next Tuesday. The INDEX function =
returns the=20
 position ; in the string of each weekday, this number is then added to =
the=20
 
 ; =
1st of the month.=20
 Note that Tuesday (day 2) is not needed in&nbsp;&nbsp;&nbsp;=20
 ; the=20
 lookup string . .=20
 =
.&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n=
bsp;&nbsp;&nbsp;&nbsp;=20
 &nbsp; 
 DEFINE d_days_1st=20
 num*6=20
 =
&amp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n=
bsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p;&nbsp;&nbsp;&nbsp;=20
 &nbsp;&nbsp;&nbsp; =3D DAYS(FLOOR(dte_current_monthend / =
100)*100) +=20
 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n=
bsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
 DEFINE d_dte_tue&nbsp; DATE=20
 =
&amp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n=
bsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p;&nbsp;&nbsp;&nbsp;&nbsp;=20
 &nbsp;&nbsp;&nbsp; =3D DATE(d_days_1st +=20
 INDEX('106543',CCON(MOD(d_days_1st,7)))) " 
 regards <SPAN=20
 lang=3Den-nz>Richard 
 -----Original=20
 Message----- From:=20
 powerh-l-admin@lists.sowder.com [<A=20
 href=3D"mailto:powerh-l-admin@lists.sowder.com"><FONT=20
 face=3DArial color=3D#0000ff=20
 =
size=3D2>mailto:powerh-l-admin@lists.sowder.com</A><SPA=
N=20
 lang=3Den-nz>] On Behalf Of Viet =
Nguyen=20
 Sent: Wednesday, 10 =
November 2004=20
 9:37 a.m. To:=20
 Robert Edis; PH List Subject: Beginning / End of week 
 Hi =
all, <SPAN=20
 lang=3Den-nz>&nbsp;&nbsp; Please tell me =
if there are=20
 functions to return the date of Monday and Friday of the current week =
in Quiz?=20
 I need to autorun reports from Including data from Monday-Friday=20
 weekly.
 I am in IBM =
AIX 
 Thanks. <SPAN=20
 lang=3Den-nz>Viet. <FONT =

size=3D3> --=20
 =
 *********************************************************************=
* This=20
 electronic message together with any attachments is confidential. =
If you=20
 receive it in error: (i) you must not use, disclose, copy or =
retain it;=20
 (ii) please contact the sender immediately by reply email and =
then delete=20
 the emails. Views expressed in this email may not be those of =
the Airways=20
 Corporation of New Zealand=20
 =
Limited **************************************************************=
******** </BLOCKQUOTE></BODY></HTML>

------_=_NextPart_001_01C4C6A2.75E4982F--