Group Between Dates

[bernie321]

Hi

I am attempting to write a report for these tables:

Dates Table Call Table Old Call Table
MonthStart CallStartDate CallStartDate
MonthEnd CallCode CallEndDate
StaffMember CallCode
CallType StaffMember
CallType

The dates table identifies the dates of our months, the when we have a new call it is entered into the call table with a start date. When the call is completed it is moved to the OLD Call table and an EndDate is assigned.

We have a SQL Server view setup with a Union join between both the Call table and the old call table. Leaving us with the fields CallStartDate, CallEndDate, CallCode, StaffMember and CallType.

I wish to write a report grouped by StaffMember and CallType which shows all open calls for these groupings each month, for the past 24 months. If calls are still open (some calls can last years) then they should be shown each month since they were opened.

I am a little lost on how can restrict the data for each month.

Any advice would be much appreciated

Regards B

 

[lbass]

Not sure, but try grouping on staffmember and call type and then creating formulas like:

//{@openthismo}:
if isnull(NewTable.EndDate}) or
{NewTable.EndDate} >= date(year(currentdate),month(currentdate), 01) then 1

//{@openlastmo}:
if isnull(NewTable.EndDate}) or
{NewTable.EndDate} >= dateserial(year(currentdate),month(currentdate)-1, 01) then 1

This would define an open call as one that was open anytime during the specified month. If you mean that they must be open for the entire month, then change the formulas to:

//{@openthismo}:
if isnull(NewTable.EndDate}) then 1

//{@openlastmo}:
if isnull(NewTable.EndDate}) or
{NewTable.EndDate} >= dateserial(year(currentdate),month(currentdate), 01)-1 then 1

//{@opentwomosago}:
if isnull(NewTable.EndDate}) or
{NewTable.EndDate} >= dateserial(year(currentdate),month(currentdate)-1, 01)-1 then 1 //etc. for 24 mos.

Place these horizontally across the canvas in the details section. You would then insert summaries (SUMS, not counts} on these formulas at all group levels.

-LB

 

[Madawc]

Since you only want open calls, you should be able to get rid of the rest using report selection, isnull(CallEndDate). That's assuming just one detail per StaffMember and CallType. If not, you might be able to do the same thing as a Group Selection for Maximum(CallEndDate).

You can get the month using DatePart("m", CallStartDate), which will yield a month number. You mgiht also have to use year, DatePart("yyyy", CallStartDate)

It helps to give your Crystal version, since newer versions have extra options, and some extra problems. I use Crystal 8.5.

Madawc Williams (East Anglia, UK)