beginning/end of month
Latimer, Richard
richard.latimer@airways.co.nz
Thu, 11 Nov 2004 15:35:32 +1300
This is a multi-part message in MIME format.
------_=_NextPart_001_01C4C797.1DB55A30
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
Hi Kristina,
=20
There is a "lastday" function which returns the date of the last day of
the current month - eg DEFINE d_EOM date =3D lastday(some date).
Unfortunately there is no equivalent "firstday" function so you need to
do either some maths or some string manipulation to work that one out.
=20
For both the above you will need to invent a date in the report month -
converting the sysdate using the "days" function allows you to subtract
some number of days to get into the previous month - I would suggest you
subtract more than 1 but less than 28 so the report can be run / rerun
on other than the 1st of the new month. =20
=20
You could probably concatenate most of it into a couple of defined items
(eg my example of yesterday) and create a maintenance nightmare - as
part of my "benchmarking" yesterday I split the numeric firstday
calculation up into 3 sequential defined items with no effect on
runtime.
=20
regards
Richard
=20
-----Original Message-----
From: powerh-l-admin@lists.sowder.com
[mailto:powerh-l-admin@lists.sowder.com] On Behalf Of Kristina Carlton
Sent: Thursday, 11 November 2004 6:26 a.m.
To: 'powerh-l@lists.sowder.com'
Subject: beginning/end of month
=09
=09
Hi List!
=20
I was wondering if someone can tell me the most efficient way to
derived the beginning and end of month. We are running an extract on the
first of every month and the date parameters need to be from the 1st
through the last day of the previous month. I know there is a MONTH
function as well as LASTDAY, DATEEXTRACT, etc. but deriving and
converting the dates seems very cumbersome. Any suggestions are greatly
appreciated.
=20
OpenVMS V7.3-2
Quiz V7.10.G4
=20
Thank you,
Kristina Carlton
Senior Programmer/Analyst
The JI Companies
512.427.2459
kristina.carlton@jicompanies.com
=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_01C4C797.1DB55A30
Content-Type: text/html; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML xmlns=3D"http://www.w3.org/TR/REC-html40" xmlns:o =3D=20
"urn:schemas-microsoft-com:office:office" xmlns:w =3D=20
"urn:schemas-microsoft-com:office:word"><HEAD><TITLE>Message</TITLE>
<META http-equiv=3DContent-Type content=3D"text/html; charset=3Dus-ascii">
<META content=3DWord.Document name=3DProgId>
<META content=3D"MSHTML 6.00.2800.1400" name=3DGENERATOR>
<META content=3D"Microsoft Word 9" name=3DOriginator><LINK=20
href=3D"cid:filelist.xml@01C4C717.FFA364D0" rel=3DFile-List><!--[if gte mso=
9]><xml>
<o:OfficeDocumentSettings>
<o:DoNotRelyOnCSS/>
</o:OfficeDocumentSettings>
</xml><![endif]--><!--[if gte mso 9]><xml>
<w:WordDocument>
<w:View>Normal</w:View>
<w:Zoom>0</w:Zoom>
<w:DocumentKind>DocumentEmail</w:DocumentKind>
<w:EnvelopeVis/>
<w:DrawingGridHorizontalSpacing>2.85 pt</w:DrawingGridHorizontalSpacing>
<w:DisplayVerticalDrawingGridEvery>2</w:DisplayVerticalDrawingGridEvery>
</w:WordDocument>
</xml><![endif]-->
<STYLE>@font-face {
font-family: Century Gothic;
}
@page Section1 {size: 8.5in 11.0in; margin: 1.0in 1.0in 1.0in 1.0in; mso-he=
ader-margin: .5in; mso-footer-margin: .5in; mso-paper-source: 0; }
P.MsoNormal {
FONT-SIZE: 9pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: "Century Gothic"; mso-bi=
di-font-size: 12.0pt; mso-style-parent: ""; mso-pagination: widow-orphan; m=
so-fareast-font-family: "Times New Roman"; mso-bidi-font-family: "Courier N=
ew"
}
LI.MsoNormal {
FONT-SIZE: 9pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: "Century Gothic"; mso-bi=
di-font-size: 12.0pt; mso-style-parent: ""; mso-pagination: widow-orphan; m=
so-fareast-font-family: "Times New Roman"; mso-bidi-font-family: "Courier N=
ew"
}
DIV.MsoNormal {
FONT-SIZE: 9pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: "Century Gothic"; mso-bi=
di-font-size: 12.0pt; mso-style-parent: ""; mso-pagination: widow-orphan; m=
so-fareast-font-family: "Times New Roman"; mso-bidi-font-family: "Courier N=
ew"
}
P.MsoAutoSig {
FONT-SIZE: 9pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: "Century Gothic"; mso-bi=
di-font-size: 12.0pt; mso-pagination: widow-orphan; mso-fareast-font-family=
: "Times New Roman"; mso-bidi-font-family: "Courier New"
}
LI.MsoAutoSig {
FONT-SIZE: 9pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: "Century Gothic"; mso-bi=
di-font-size: 12.0pt; mso-pagination: widow-orphan; mso-fareast-font-family=
: "Times New Roman"; mso-bidi-font-family: "Courier New"
}
DIV.MsoAutoSig {
FONT-SIZE: 9pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: "Century Gothic"; mso-bi=
di-font-size: 12.0pt; mso-pagination: widow-orphan; mso-fareast-font-family=
: "Times New Roman"; mso-bidi-font-family: "Courier New"
}
SPAN.EmailStyle15 {
COLOR: #333399; mso-bidi-font-family: Arial; mso-style-type: personal-comp=
ose; mso-ansi-font-size: 9.0pt; mso-ascii-font-family: "Century Gothic"; ms=
o-hansi-font-family: "Century Gothic"
}
DIV.Section1 {
page: Section1
}
</STYLE>
</HEAD>
<BODY lang=3DEN-US style=3D"tab-interval: .5in">
<DIV><SPAN class=3D417455501-11112004><FONT face=3DArial color=3D#0000ff si=
ze=3D2>Hi=20
Kristina,</FONT></SPAN></DIV>
<DIV><SPAN class=3D417455501-11112004><FONT face=3DArial color=3D#0000ff=20
size=3D2> </FONT></SPAN></DIV>
<DIV><SPAN class=3D417455501-11112004><FONT face=3DArial color=3D#0000ff si=
ze=3D2>There=20
is a "lastday" function which returns the date of the last day of the curre=
nt=20
month - eg DEFINE d_EOM date =3D lastday(some date). Unfortunately th=
ere is=20
no equivalent "firstday" function so you need to do either some maths or so=
me=20
string manipulation to work that one out.</FONT></SPAN></DIV>
<DIV><SPAN class=3D417455501-11112004><FONT face=3DArial color=3D#0000ff=20
size=3D2></FONT></SPAN> </DIV>
<DIV><SPAN class=3D417455501-11112004><FONT face=3DArial color=3D#0000ff si=
ze=3D2>For=20
both the above you will need to invent a date in the report month -=20
c</FONT></SPAN><SPAN class=3D417455501-11112004><FONT face=3DArial color=3D=
#0000ff=20
size=3D2>onverting the sysdate using the "days" function allows you to subt=
ract=20
some number of days to get into the previous month - I would suggest you=20
subtract more than 1 but less than 28 so the report can be run / rerun &nbs=
p;on=20
other than the 1st of the new month. </FONT></SPAN></DIV>
<DIV><SPAN class=3D417455501-11112004><FONT face=3DArial color=3D#0000ff=20
size=3D2></FONT></SPAN> </DIV>
<DIV><SPAN class=3D417455501-11112004><FONT face=3DArial color=3D#0000ff si=
ze=3D2>You=20
could probably concatenate most of it into a couple of defined items (eg my=
example of yesterday) and create a maintenance nightmare - as part of my=
"benchmarking" yesterday I split the numeric firstday calculation up=
into=20
3 sequential defined items with no effect on runtime.</FONT></SPAN></DIV>
<DIV><SPAN class=3D417455501-11112004><FONT face=3DArial color=3D#0000ff=20
size=3D2></FONT></SPAN> </DIV>
<DIV><SPAN class=3D417455501-11112004><FONT face=3DArial color=3D#0000ff=20
size=3D2>regards</FONT></SPAN></DIV>
<DIV><SPAN class=3D417455501-11112004><FONT face=3DArial color=3D#0000ff=20
size=3D2>Richard</FONT></SPAN></DIV>
<DIV><SPAN class=3D417455501-11112004><FONT face=3DArial color=3D#0000ff=20
size=3D2></FONT></SPAN> </DIV>
<BLOCKQUOTE dir=3Dltr style=3D"MARGIN-RIGHT: 0px">
<DIV></DIV>
<DIV class=3DOutlookMessageHeader lang=3Den-us dir=3Dltr align=3Dleft><FO=
NT=20
face=3DTahoma 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>Kristina Carlton<BR><B>Sent:</B> Thursday, 11 November 2004=
6:26=20
a.m.<BR><B>To:</B> 'powerh-l@lists.sowder.com'<BR><B>Subject:</B>=20
beginning/end of month<BR><BR></FONT></DIV>
<DIV class=3DSection1>
<P class=3DMsoNormal><SPAN class=3DEmailStyle15><FONT face=3D"Century Got=
hic"=20
color=3D#333399 size=3D1><SPAN style=3D"FONT-SIZE: 9pt"><SPAN=20
style=3D"mso-bidi-font-size: 12.0pt">Hi=20
List!<o:p></o:p></SPAN></SPAN></FONT></SPAN></P>
<P class=3DMsoNormal><SPAN class=3DEmailStyle15><FONT face=3D"Century Got=
hic"=20
color=3D#333399 size=3D1><SPAN style=3D"FONT-SIZE: 9pt"><![if !supportEmp=
tyParas]><![endif]> </SPAN><o:p></o:p></FONT></SPAN></P>
<P class=3DMsoNormal><SPAN class=3DEmailStyle15><FONT face=3D"Century Got=
hic"=20
color=3D#333399 size=3D1><SPAN style=3D"FONT-SIZE: 9pt"><SPAN=20
style=3D"mso-bidi-font-size: 12.0pt">I was wondering if someone can tell =
me the=20
most <U>efficient</U> way to derived the beginning and end of month. We a=
re=20
running an extract on the first of every month and the date parameters ne=
ed to=20
be from the 1<SUP>st</SUP> through the last day of the <U>previous</U> mo=
nth.=20
I know there is a MONTH function as well as LASTDAY, DATEEXTRACT, etc. bu=
t=20
deriving and converting the dates seems very cumbersome. Any suggestions =
are=20
greatly appreciated.<o:p></o:p></SPAN></SPAN></FONT></SPAN></P>
<P class=3DMsoNormal><SPAN class=3DEmailStyle15><FONT face=3D"Century Got=
hic"=20
color=3D#333399 size=3D1><SPAN style=3D"FONT-SIZE: 9pt"><![if !supportEmp=
tyParas]><![endif]> </SPAN><o:p></o:p></FONT></SPAN></P>
<P class=3DMsoNormal><SPAN class=3DEmailStyle15><FONT face=3D"Century Got=
hic"=20
color=3D#333399 size=3D1><SPAN style=3D"FONT-SIZE: 9pt"><SPAN=20
style=3D"mso-bidi-font-size: 12.0pt">OpenVMS=20
V7.3-2<o:p></o:p></SPAN></SPAN></FONT></SPAN></P>
<P class=3DMsoNormal><SPAN class=3DEmailStyle15><FONT face=3D"Century Got=
hic"=20
color=3D#333399 size=3D1><SPAN style=3D"FONT-SIZE: 9pt"><SPAN=20
style=3D"mso-bidi-font-size: 12.0pt">Quiz=20
V7.10.G4<o:p></o:p></SPAN></SPAN></FONT></SPAN></P>
<P class=3DMsoNormal><SPAN class=3DEmailStyle15><FONT face=3D"Century Got=
hic"=20
color=3D#333399 size=3D1><SPAN style=3D"FONT-SIZE: 9pt"><![if !supportEmp=
tyParas]><![endif]> </SPAN><o:p></o:p></FONT></SPAN></P>
<P class=3DMsoNormal><SPAN class=3DEmailStyle15><FONT face=3D"Century Got=
hic"=20
color=3D#333399 size=3D1><SPAN style=3D"FONT-SIZE: 9pt"><SPAN=20
style=3D"mso-bidi-font-size: 12.0pt">Thank=20
you,<o:p></o:p></SPAN></SPAN></FONT></SPAN></P>
<P class=3DMsoAutoSig><!--[if supportFields]><font color=3D"#333399"><spa=
n=20
style=3D'color:#333399'><span style=3D'mso-element:field-begin'></span><spa=
n=20
style=3D"mso-spacerun: yes"> </span>AUTOTEXTLIST \s "E-mail=20
Signature" <span style=3D'mso-element:field-separator'></span></span><=
/font><![endif]--><FONT=20
color=3D#333399><SPAN style=3D"COLOR: #333399">Kristina Carlton</SPAN></F=
ONT><FONT=20
color=3D#333399><SPAN=20
style=3D"COLOR: #333399; mso-color-alt: windowtext"><o:p></o:p></SPAN></F=
ONT></P>
<P class=3DMsoAutoSig><FONT face=3D"Century Gothic" color=3D#333399 size=
=3D1><SPAN=20
style=3D"FONT-SIZE: 9pt; COLOR: #333399"><SPAN=20
style=3D"mso-bidi-font-size: 12.0pt">Senior=20
Programmer/Analyst</SPAN></SPAN></FONT><FONT color=3D#333399><SPAN=20
style=3D"COLOR: #333399; mso-color-alt: windowtext"><o:p></o:p></SPAN></F=
ONT></P>
<P class=3DMsoAutoSig><FONT face=3D"Century Gothic" color=3D#333399 size=
=3D1><SPAN=20
style=3D"FONT-SIZE: 9pt; COLOR: #333399"><SPAN=20
style=3D"mso-bidi-font-size: 12.0pt">The JI Companies</SPAN></SPAN></FONT=
><FONT=20
color=3D#333399><SPAN=20
style=3D"COLOR: #333399; mso-color-alt: windowtext"><o:p></o:p></SPAN></F=
ONT></P>
<P class=3DMsoAutoSig><FONT face=3D"Century Gothic" color=3D#333399 size=
=3D1><SPAN=20
style=3D"FONT-SIZE: 9pt; COLOR: #333399"><SPAN=20
style=3D"mso-bidi-font-size: 12.0pt">512.427.2459</SPAN></SPAN></FONT><FO=
NT=20
color=3D#333399><SPAN=20
style=3D"COLOR: #333399; mso-color-alt: windowtext"><o:p></o:p></SPAN></F=
ONT></P>
<P class=3DMsoAutoSig><FONT face=3D"Century Gothic" color=3D#333399 size=
=3D1><SPAN=20
style=3D"FONT-SIZE: 9pt; COLOR: #333399"><SPAN=20
style=3D"mso-bidi-font-size: 12.0pt">kristina.carlton@jicompanies.com</SP=
AN></SPAN></FONT><FONT=20
color=3D#333399><SPAN=20
style=3D"COLOR: #333399; mso-color-alt: windowtext"><o:p></o:p></SPAN></F=
ONT></P>
<P class=3DMsoNormal><!--[if supportFields]><font color=3D"#333399"><span=
style=3D'color:#333399'><span style=3D'mso-element:field-end'></span></spa=
n></font><![endif]--><FONT=20
color=3D#333399><SPAN style=3D"COLOR: #333399"><![if !supportEmptyParas]>=
<![endif]> </SPAN></FONT><FONT=20
color=3D#333399><SPAN=20
style=3D"COLOR: #333399; mso-color-alt: windowtext"><o:p></o:p></SPAN></F=
ONT></P></DIV></BLOCKQUOTE><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>
=00
------_=_NextPart_001_01C4C797.1DB55A30--