Re: Sysdate with months from prior year

[Wayne Ivory <wivory@WSL.COM.AU>]

There is a neat trick my university lecturer taught me you can do with the mod function for cycling months.  Your function would become @MONTH(fieldname)=mod(@month(sysdate)+10,12)+1.  When you get to 1 a *wonderous* thing happens!
  eg
    12+10=22, mod 12=10, +1=11
    11+10=21, mod 12=9, +1=10
     .
     .
    2+10=12, mod 12=0, +1=1
    1+10=11, mod 12=11, +1=12!!!

Hope this helps.

Wayne Ivory
Information Services
Westralian Sands Limited


>>> Larry McCown <lmccown@WORLD.NORTHGRUM.COM> 28/1/98 4:40:52 am >>>
Yes Wes my @year(XXX)-1 function appears to be working. I hard coded

the month for January (01) and it worked. It seams that maybe the

problem is that when I change the @MONTH(fieldname) =

(@month(sysdate)-1) if we are in January which is 1, minus 1 would be 0

not 12. Any ideas?

Thanks Again

> ----------

> From: Wes Williams[SMTP:wwilliams@MAIL.RKD.SNDS.COM] 

> Reply To: SQR-USERS@USA.NET 

> Sent: Tuesday, January 27, 1998 9:04 AM

> To: Multiple recipients of list SQR-USERS

> Subject: Using SYSDATE to search for prior Month & Year -Reply

> 

> Hi Larry, what else has changed ? I ran a report that I have

> that uses the @month(fieldname) = @month(sysdate) and it

> retrieved the correct workorders.

> 

> I've also written reports utilizing the @year(XXX) and

> @year(XXX) -1 etc. function.

> 

> ????

> Wes

> 

> >>> Larry McCown

> <lmccown%WORLD.NORTHGRUM.COM@internet.rkd.snds.com> 01/27/98 09:53am

> >>>

> Hello, I am new to SQRUG, but have been using MAXIMO for

> 4 years and SQR for 3 years.

> 

> In September of 1997 I wrote a report on SQLBase, using

> sysdate to find records broken down by month over a 6

> month time frame based on a field "changedate". This

> program worked just fine going back a given number of

> months by coding these where statements within my select

> statements:

> 

> @month(changedate) = @month(sysdate) for current month

> 

> then another select statement read

> 

> @month(changedate) = (@month(sysdate)-1) for prior month

> 

> then and yet another select statement etc.

> 

> @month(changedate) = (@month(sysdate)-2) two month

> back etc.

> 

> But since the first of the year the report dose not report

> anything from prior year. ANY SUGGESTIONS?

> 

> also included in the select statements is

> 

> @year(changedate)=@year(sysdate) for current year and

> 

> @year(changedate)=(@year(sysdate)-1) for prior year.

> 

> Thank You

> 

> 

> 

> 

> 

> 

>