Beginning / End of week
Latimer, Richard
richard.latimer@airways.co.nz
Wed, 10 Nov 2004 10:05:20 +1300
This is a multi-part message in MIME format.
------_=_NextPart_001_01C4C69F.D2389AE2
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
> Hi Viet,
>=20
> there are no specific functions that will do exactly what you want
> but:
> - the "days" function will return an integer from a date
> - 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
>=20
> "Thank you to all who took the time to respond.=20
>=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
>=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
>=20
> regards
> Richard
>=20
> -----Original Message-----
> From: powerh-l-admin@lists.sowder.com
> [mailto:powerh-l-admin@lists.sowder.com] On Behalf Of Viet Nguyen
> Sent: Wednesday, 10 November 2004 9:37 a.m.
> To: Robert Edis; PH List
> Subject: Beginning / End of week
>=20
>=20
> Hi all,
> 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.
>=20
> I am in IBM AIX
>=20
> Thanks.
> 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_01C4C69F.D2389AE2
Content-Type: text/html; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; charset=3Dus-ascii">
<META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version 6.0.6556.0">
<TITLE> Beginning / End of week</TITLE>
</HEAD>
<BODY>
<!-- Converted from text/rtf format -->
<BR>
<P><SPAN LANG=3D"en-nz"><FONT SIZE=3D2 FACE=3D"Arial">Hi Viet,</FONT></SPAN>
</P>
<P><SPAN LANG=3D"en-nz"><FONT SIZE=3D2 FACE=3D"Arial">there are no specific=
functions that will do exactly what you want but:</FONT></SPAN>
<BR><SPAN LANG=3D"en-nz"><FONT SIZE=3D2 FACE=3D"Arial"> - the "da=
ys" function will return an integer from a date</FONT></SPAN>
<BR><SPAN LANG=3D"en-nz"><FONT SIZE=3D2 FACE=3D"Arial"> - you can use =
mod(days(date),7) to get the day of the week where 0 =3D Sunday, 1 =3D Mond=
ay etc</FONT></SPAN>
</P>
<P><SPAN LANG=3D"en-nz"><FONT SIZE=3D2 FACE=3D"Arial">the example below is =
from an automated report that we run which is for the first Tuesday of a mo=
nth calculated backwards from the date of the last day in the month. </FONT=
></SPAN></P>
<P><SPAN LANG=3D"en-nz"><FONT COLOR=3D"#0000FF" SIZE=3D2 FACE=3D"Arial">&qu=
ot;Thank you to all who took the time to respond. </FONT></SPAN>
</P>
<P><SPAN LANG=3D"en-nz"><FONT COLOR=3D"#0000FF" SIZE=3D2 FACE=3D"Arial">I h=
ave adapted the extremely elegant code supplied by Paul Howard and it works=
exactly the way I want !</FONT></SPAN>
</P>
<P><SPAN LANG=3D"en-nz"><FONT COLOR=3D"#0000FF" SIZE=3D2 FACE=3D"Arial">I m=
ust admit it took a while to get my head around how it worked and I ended u=
p adding a lot of comments so I can figure it out in the future! For what i=
t's worth here's how I implemented it (note that on the AS400 the date is s=
tored as an 8 digit number yyymmdd and can be treated as a number or a</FON=
T></SPAN></P>
<P><SPAN LANG=3D"en-nz"><FONT COLOR=3D"#0000FF" SIZE=3D2 FACE=3D"Arial">dat=
e) :</FONT></SPAN>
</P>
<P><SPAN LANG=3D"en-nz"><FONT COLOR=3D"#0000FF" SIZE=3D2 FACE=3D"Arial">ACC=
ESS control_file  =
; &n=
bsp;  =
; </FONT>=
</SPAN>
<BR><SPAN LANG=3D"en-nz"><FONT COLOR=3D"#0000FF" SIZE=3D2 FACE=3D"Arial">&n=
bsp;  =
; &n=
bsp;  =
; &n=
bsp;  =
; </FONT></SPAN>
<BR><SPAN LANG=3D"en-nz"><FONT COLOR=3D"#0000FF" SIZE=3D2 FACE=3D"Arial">; =
The report is expected to return movement data for &=
nbsp; </FONT></SPAN>
<BR><SPAN LANG=3D"en-nz"><FONT COLOR=3D"#0000FF" SIZE=3D2 FACE=3D"Arial">; =
"the 1st Tuesday of last month". This code derives the  =
; </FONT></SPAN>
<BR><SPAN LANG=3D"en-nz"><FONT COLOR=3D"#0000FF" SIZE=3D2 FACE=3D"Arial">; =
corresponding date to use in the eventual choose statement. &nbs=
p; </FONT></SPAN>
<BR><SPAN LANG=3D"en-nz"><FONT COLOR=3D"#0000FF" SIZE=3D2 FACE=3D"Arial">; =
STEP 1 is to find the date of the "1st day of last month". &=
nbsp; </FONT></SPAN>
<BR><SPAN LANG=3D"en-nz"><FONT COLOR=3D"#0000FF" SIZE=3D2 FACE=3D"Arial">; =
The last day of the month is on the control file so start =
</FONT></SPAN>
<BR><SPAN LANG=3D"en-nz"><FONT COLOR=3D"#0000FF" SIZE=3D2 FACE=3D"Arial">; =
from there. &nbs=
p; &=
nbsp; &nbs=
p; &=
nbsp; </FONT></SPAN>
<BR><SPAN LANG=3D"en-nz"><FONT COLOR=3D"#0000FF" SIZE=3D2 FACE=3D"Arial">; =
STEP 2 is to find out which day of the week the that was. =
</FONT></SPAN>
<BR><SPAN LANG=3D"en-nz"><FONT COLOR=3D"#0000FF" SIZE=3D2 FACE=3D"Arial">; =
Dividing the day count by 7 and reviewing the remainder (MOD) <=
/FONT></SPAN>
<BR><SPAN LANG=3D"en-nz"><FONT COLOR=3D"#0000FF" SIZE=3D2 FACE=3D"Arial">; =
gives the weekday where 0 =3D Sunday , 1 =3D Monday etc &n=
bsp; </FONT></SPAN>
<BR><SPAN LANG=3D"en-nz"><FONT COLOR=3D"#0000FF" SIZE=3D2 FACE=3D"Arial">; =
STEP 3 is to calculate how many days to add to the "1st" to =
</FONT></SPAN>
<BR><SPAN LANG=3D"en-nz"><FONT COLOR=3D"#0000FF" SIZE=3D2 FACE=3D"Arial">; =
get to the next Tuesday. The INDEX function returns the position ; in the s=
tring of each weekday, this number is then added to the </FONT></SPAN></P>
<P><SPAN LANG=3D"en-nz"><FONT COLOR=3D"#0000FF" SIZE=3D2 FACE=3D"Arial">; 1=
st of the month. Note that Tuesday (day 2) is not needed in &nbs=
p; </FONT></SPAN>
<BR><SPAN LANG=3D"en-nz"><FONT COLOR=3D"#0000FF" SIZE=3D2 FACE=3D"Arial">; =
the lookup string . . . &nbs=
p; &=
nbsp; &nbs=
p; </FONT></SPAN>
<BR><SPAN LANG=3D"en-nz"><FONT COLOR=3D"#0000FF" SIZE=3D2 FACE=3D"Arial">&n=
bsp;</FONT></SPAN>
</P>
<P><SPAN LANG=3D"en-nz"><FONT COLOR=3D"#0000FF" SIZE=3D2 FACE=3D"Arial">DEF=
INE d_days_1st num*6 & &=
nbsp; &nbs=
p; &=
nbsp; </FONT></SPAN>
<BR><SPAN LANG=3D"en-nz"><FONT COLOR=3D"#0000FF" SIZE=3D2 FACE=3D"Arial">&n=
bsp; =3D DAYS(FLOOR(dte_current_monthend / 100)*100) + 1 &=
nbsp; </FONT></SPAN>
<BR><SPAN LANG=3D"en-nz"><FONT COLOR=3D"#0000FF" SIZE=3D2 FACE=3D"Arial">&n=
bsp;  =
; &n=
bsp;  =
; &n=
bsp;  =
; </FONT></SPAN>
<BR><SPAN LANG=3D"en-nz"><FONT COLOR=3D"#0000FF" SIZE=3D2 FACE=3D"Arial">DE=
FINE d_dte_tue DATE & &n=
bsp;  =
; &n=
bsp; </FONT></SPAN>
<BR><SPAN LANG=3D"en-nz"><FONT COLOR=3D"#0000FF" SIZE=3D2 FACE=3D"Arial">&n=
bsp; =3D DATE(d_days_1st + INDEX('106543',CCON(MOD(d_days_1st,7=
)))) "</FONT></SPAN>
</P>
<BR>
<P><SPAN LANG=3D"en-nz"><FONT SIZE=3D2 FACE=3D"Arial">regards</FONT></SPAN>
<BR><SPAN LANG=3D"en-nz"><FONT SIZE=3D2 FACE=3D"Arial">Richard</FONT></SPAN>
</P>
<P><SPAN LANG=3D"en-nz"><FONT SIZE=3D2 FACE=3D"Arial">-----Original Message=
-----</FONT></SPAN>
<BR><SPAN LANG=3D"en-nz"><FONT SIZE=3D2 FACE=3D"Arial">From: powerh-l-admin=
@lists.sowder.com [</FONT></SPAN><A HREF=3D"mailto:powerh-l-admin@lists.sow=
der.com"><SPAN LANG=3D"en-nz"><U><FONT COLOR=3D"#0000FF" SIZE=3D2 FACE=3D"A=
rial">mailto:powerh-l-admin@lists.sowder.com</FONT></U></SPAN></A><SPAN LAN=
G=3D"en-nz"><FONT SIZE=3D2 FACE=3D"Arial">] On Behalf Of Viet Nguyen</FONT>=
</SPAN>
<BR><SPAN LANG=3D"en-nz"><FONT SIZE=3D2 FACE=3D"Arial">Sent: Wednesday, 10 =
November 2004 9:37 a.m.</FONT></SPAN>
<BR><SPAN LANG=3D"en-nz"><FONT SIZE=3D2 FACE=3D"Arial">To: Robert Edis; PH =
List</FONT></SPAN>
<BR><SPAN LANG=3D"en-nz"><FONT SIZE=3D2 FACE=3D"Arial">Subject: Beginning /=
End of week</FONT></SPAN>
</P>
<BR>
<P><SPAN LANG=3D"en-nz"><FONT SIZE=3D2 FACE=3D"Arial">Hi all,</FONT></SPAN>
<BR><SPAN LANG=3D"en-nz"><FONT SIZE=3D2 FACE=3D"Arial"> Please =
tell me if there are functions to return the date of Monday and Friday of t=
he current week in Quiz? I need to autorun reports from Including data from=
Monday-Friday weekly.</FONT></SPAN></P>
<P><SPAN LANG=3D"en-nz"><FONT SIZE=3D2 FACE=3D"Arial">I am in IBM AIX</FONT=
></SPAN>
</P>
<P><SPAN LANG=3D"en-nz"><FONT SIZE=3D2 FACE=3D"Arial">Thanks.</FONT></SPAN>
<BR><SPAN LANG=3D"en-nz"><FONT SIZE=3D2 FACE=3D"Arial">Viet.</FONT></SPAN>
</P>
<FONT SIZE=3D3><BR>
<BR>
-- <BR>
**********************************************************************<BR>
This electronic message together with any attachments is confidential. If<B=
R>
you receive it in error: (i) you must not use, disclose, copy or retain<BR>
it; (ii) please contact the sender immediately by reply email and then<BR>
delete the emails. Views expressed in this email may not be those of the<BR>
Airways Corporation of New Zealand Limited<BR>
**********************************************************************<BR>
</FONT>
</BODY>
</HTML>
------_=_NextPart_001_01C4C69F.D2389AE2--