Need to calculate # of days

[memewang]

I am trying to create a report that will return # of records which meet the following criteria.

need to pull records that exceeds 360 days for post date and status ="active" and records exceeds 30 days for receive date. The post date has data type of "Date" and receive date has data type of "smalldatetime".

I have tried to create the formula below and it didn't seem to work. Can somebody please help me with this?

Currentdate - (items.post_date) < 360 and status ="active" and currentdate -(items.receive_date) < 30.

 

[lbass]

I'm confused about what you mean by "exceeds". Your formula seems to show that you want records SINCE the currentdate - 360 or -30. If that is what you mean, try:

{items.post_date} > currentdate - 360 and
status = "active" and
{items.receive_date} > currentdate - 30

The above means that each record must have been posted within the last 360 days AND must have been received within the last 30 days and be active. If you really want all records that meet one or both sets of criteria, then you would want to use an "or" statement, as in:

(
{items.post_date} > currentdate - 360 and
status = "active"
) or
{items.receive_date} > currentdate - 30

You also should do the math calculation on the currentdate side of the equation, since it only has to be calculated once, not for every record, and because it results in a constant, this formula should pass to the SQL statement.

-LB

 

[memewang]

Thanks a lot,I will try today. Do I not need to do any conversion since the data type is different?

 

[lbass]

I'm not sure about small datetime. You can ordinarily compare a datetime with a date in a record selection formula without having to do a conversion. I would try the above, and if it doesn't seem to work, comment out the clause that uses the small datetime to identify whether that is the issue.

-LB

 

[memewang]

How do I pass the formula to sql statement?

 

[lbass]

If you use my suggestion, it should pass to the SQL statement. Go to database->show SQL query to see whether all record selection criteria are included in the where clause.

-LB

 

[memewang]

I created the formula and when I went to the database > show sql query, it didn't include the formula I have created. I tried to add to the where statement, and it still does work. Please help.

thanks

 

[lbass]

Did you create the formula in the report->selection formula->record area? Also, please identify your CR version.

-LB

 

[memewang]

Yes, i did create the formula through report > selection formula> record area. I am using v.7. Here is the query I have put under "Show sql query section. It is giving me error message "error detected by database DLL". I really don't know what to do. Please help..

SELECT
effective_Test.Trans, effective_Test.Effective_date, effective_Test.Branch
FROM
Reporting.dbo.effective_Test effective_Test
WHERE
effective_Test.Branch ='022' and
{effective_Test.Effective_date} > (CurrentDate -360)

 

[lbass]

Don't try to modify the SQL query directly. Reset the show SQL query or remove the changes you made and then just add the formula I suggested earlier to the record selection area, and it will automatically be included in the SQL query if it all passes. If it doesn't all appear in the Show SQL query, it doesn't mean that it's not working, just that the part that doesn't appear is being processed locally.

-LB

 

[memewang]

I think I got it to work. thanks so much for helping getting this to work. Really appreciated your help.

Thank you

 

[memewang]

OK, The following query was working when i tried to create reports on my local machine (v7.0). but when I tried to do it via the web site (crystal report is residing on a customer's web site v.10). {effective_Test.Effective_date} > (CurrentDate -360) is missing from the where statement.

When i checked, it is still in the report->selection formula->record area.
Can somebody please help me with this? I have no clue what happen.

thanks

SELECT
effective_Test.Trans, effective_Test.Effective_date, effective_Test.Branch
FROM
Reporting.dbo.effective_Test effective_Test
WHERE
effective_Test.Branch ='022' and
{effective_Test.Effective_date} > (CurrentDate -360)

 

[synapsevampire]

You need topost wht is in the record selection.

What you posted is the correct show sql query.

-k

 

[memewang]

sorry,

here is what is in the record selection

effective_Test.Branch ='022' and
{effective_Test.Effective_date} > (CurrentDate -360)

here is what is in the sql query ( date was missing)

SELECT
effective_Test.Trans, effective_Test.Effective_date, effective_Test.Branch
FROM
Reporting.dbo.effective_Test effective_Test
WHERE
effective_Test.Branch ='022'

 

[synapsevampire]

Appears that the server is using a different type of connectivity, that's usually the reason for different interpretations, so check that first.

Also try posting specifics, we don't know what software your client is running.

Does "but when I tried to do it via the web site (crystal report is residing on a customer's web site v.10)." mean Crystal Enterprise, or?

Crystal Reports doesn't run on web sites.

Lastly try spinning the record selection:

(
{effective_Test.Effective_date} > (CurrentDate -360)
)
and
(
{effective_Test.Branch} = "022"
)

Also what you posted as the record selection would cause an error, so please take the time to just copy and paste it:

effective_Test.Branch ='022' and

Didn't have braces around the field name, which would cause an error.

-k

 

[memewang]

Thanks for the detailed infor. I have tried getting differnet people to provide me more infor, my client doesn't like to give me too much infor. All I know, when i log on to the web site, I can click on crystal report icon and look like to me it was a module and open up the crystal report (regular v.10) and every report have to save to the default directory they have set up. i won't be able to open the report from anywhere besides via that web site. One thing for sure it is not Crystal Enterprise. It is my first time seeing this type of environment, i really don't know what to do. Also, sorry for the typo, I will try copy and paste next time. I have tried what you have listed below this morning but my date criteris is still missing from the where statement. do you know what are the other alternatives? How do i check the connectivity? thanks

(
{effective_Test.Effective_date} > (CurrentDate -360)
)
and
(
{effective_Test.Branch} = "022"
)