Checking Dates of Service against a Bill Period

[terenceb]

I need to check dates of service (7/10/2003) against an bill period (7/2003). I only need to compare the month and year. And display only the records with the date of service in the bill period.

 

[bdreed35]

You can use the Month and Year functions in your record selection criteria as one option.

Month({table.date_of_service}) = 7 AND
Year({table.date_of_service}) = 2003

Your didn't say how you are determing the billing period so I hard coded them. You can replace it as necessary. If your were using a parameters to pass the month and year of the billing period, you can substititue the 7 and 2003 with the corresponding parameters.

If you were always looking for the last completed month, you could do this:

{table.date_of_service} IN lastfullmonth


~Brian

 

[synapsevampire]

The common solution will be to use the following in the record selection formula:

month({table.servicedt}) = month({table.billdt})
and
year({table.servicedt}) = year({table.billdt})

Unfortuntately Crystal won't pass this SQL to the database, so if performance is a concern, you're better served to create a yearmonth period from each date using SQL Expressions, and compare those.

Since you didn't share the version of Crystal, nor the type of database, nor anything about the database, I can't give you a real example, the following will work for reald date fields in SQL Server:

cast(year(table.servicedt) as char(4))+cast(month(table.servicedt) as char(4))

-k

 

[synapsevampire]

The common solution will be to use the following in the record selection formula:

month({table.servicedt}) = month({table.billdt})
and
year({table.servicedt}) = year({table.billdt})

Unfortuntately Crystal won't pass this SQL to the database, so if performance is a concern, you're better served to create a yearmonth period from each date using SQL Expressions, and compare those.

Since you didn't share the version of Crystal, nor the type of database, nor anything about the database, I can't give you a real example, the following will work for real date fields in SQL Server:

cast(year(table.servicedt) as char(4))+cast(month(table.servicedt) as char(4))

-k

 

[terenceb]

Sorry,
I forgot to state that I'm using Crystal 8.5 pulling the dta from a Cashe database. I want to display on the services rendered during the bill period.

 

[bdreed35]

How is the bill period determined? Last full month, passed in using parameters?

~Brian

 

[terenceb]

Bill period is passed using a parameter.

 

[bdreed35]

if your parameter is date field then use synapsevampire's formula, just substitute {table.billdt} with your parameter field

month({table.servicedt}) = month({?Bill_Period})
and
year({table.servicedt}) = year({?Bill_Period}


~Brian