Crystal Reports 2008 - help with formula to retrieve lastfullmonth

[Former Member]

Hi, I'm new to this forum. First post. I have a SQL query where

:month(CAST(a.LAST_MOD_DT / 86400 AS datetime) + CAST('01/ 01/1970' AS datetime)) = (MONTH(getdate())- 1)

I need to create a CR formula that will pull in data from last month as this report will be run on a monthly basis. If I use 'getdate() -31' there will be a problem with the months that don't have 31 days.

I'm also very new with creating formulas so including an explaination would be helpful. Thanks,

Joyce

[JWiseman]

hi Joyce,

are you using a SQL query / command for a specific reason?

the reason why i ask is that if you create a new report off of a set of tables, then you can use the LastFullMonth function in your Record Selection Filter.

i.e. create a new report off of tables. go to the Report menu > Selection Formulas > Record and then create a formula similar to

in lastfullmonth

this will create the sql for you.

i hope this helps,

jamie

[Former Member]

Thanks for the quick reply. I am using the SQL Command because we have to join different BOXI classes to generate this report and we can't do this from one table. Hope that helps.

Joyce

[JWiseman]

okay then. in this case you'll be relying on sql syntax and nothing that is specific to crystal...the command object is written in your database language.

depending on your database (_please see your database online help if this syntax doesn't work_) the WHERE clause of your command would look something like

yourtable.datefield >= DATEADD(MONTH, DATEDIFF(MONTH, 31, CURRENT_TIMESTAMP), 0)
AND yourtable.datefield < DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP), 0) 
 

[Former Member]

I will try that and let you know.

Thanks,

Joyce

[Former Member]

Sorry for the slow repsonse in getting back to you.

I wasn't able to get the SQL query to work.  I did find one that does work except I need to find out how to pull in the minimum date and the maximum date of the records into the report.

Here's the SQL query:

--to query for the prior month,  if January then month = 12 else month = (current month - 1 month).

and

month(CAST(a.LAST_MOD_DT / 86400 AS datetime) + CAST('01/ 01/1970' AS datetime)) =

CASE WHEN cast(DATEPART(Month,GETDATE()) as datetime) = 01

                      THEN month(cast((str(12)+'/'+ str(01) +'/'+STR(YEAR(Getdate())-1)) as datetime))

                      ELSE MONTH(cast((str(DATEPART(Month,GETDATE())-1)+'/'+ str(01) +'/'+STR(YEAR(Getdate()))) as datetime)) END

--to query for the proper year, if January then year = (current year - 1 year) else year = current year.

and

year(CAST(a.LAST_MOD_DT / 86400 AS datetime) + CAST('01/ 01/1970' AS datetime)) =

CASE WHEN cast(DATEPART(Month,GETDATE()) as datetime) = 01

                      THEN year(cast((str(12)+'/'+ str(01) +'/'+STR(YEAR(Getdate())-1)) as datetime))

                      ELSE year(cast((str(DATEPART(Month,GETDATE())-1)+'/'+ str(01) +'/'+STR(YEAR(Getdate()))) as datetime)) END

[Former Member]

Hi Joyce,

My understanding is not clear. why don't you use the function "LastFullMonth"

It will calculate the year and month of Print date .

e.g. from help:

If {orders.ORDER DATE} in LastFullMonth Then {orders.ORDER AMOUNT} Else 0

cheer

Gary

[Former Member]

Hi Gary, Thanks for replying.  I tried the lastfullmonth function as shown in the formula below but I get a message saying there's a error in the formula and it won't save.

if {@LastModDate} in LastFullMonth then {@LastModDate}

   then

    if ({Command.last_name} = (["Service Desk-Cleveland"]) )

   then

        if  ({Command.AssigneeName} in {?para_assignee}   or {?para_assignee} = "ALL")

        then

            if  {Command.SYM} in {?Survey Name} or  {?Survey Name} = "ALL"

            then

                true

                else

                false;

[Former Member]

Maybe something like:

Select * from mytable where datefield between trunc(trunc(sysdate,'MM')-1,'MM') and trunc(sysdate,'MM')-1

[Former Member]

Hi Joyce,

I am busy last two weeks, sorry for a late reply.

the first row in your script was unclear,

if {@LastModDate} in LastFullMonth then {@LastModDate}

then (again???)

Don't understand, could you please explain?

Cheers