If Statement to Count Events Between Two Different Date Ranges

[DarkAngel]

Hello,

(Crystal Reports 2008 with SQL Server)

Working on a report which shows customer appointments grouped by technician (the person who provided the service). In the detail, would like to include a count of appointments in the last 30 days for each customer based their Account Status (1=Open, 2=Closed). For Open accounts, would like a count of appointments in the last 30 days from current date. For Closed accounts, would like a count of appointments in the last 30 days from the account closed date (EndDate). Created the following formula field:


if
{tblCustomer.AccountStatusID} = 1 and CurrentDate in Aged0To30Days
then
DistinctCount ({tblAppointments.ApptID})
else if
{tblCustomer.AccountStatusID} = 2 and {tblAppointments.EndDate} in Aged0To30Days
then
DistinctCount ({tblAppointments.ApptID})
else
0

However, it returns the same number for all each client row. Can if/then/else be used in Running totals?

Attempted to write SQL to do the counts, with no success (syntax errors).

(
SELECT {"tblAppointments"."ApptID"}
Case when {"tblCustomer"."AccountStatusID"} = 1
then DistinctCount({"tblAppointments"."ApptID"}) BETWEEN {fn NOW()} AND {fn NOW()}-30
Case when {"tblCustomer"."AccountStatusID"} = 2
then DistinctCount({"tblAppointments"."ApptID"}) BETWEEN ({"tblAppointments"."EndDate"} AND ({"tbleAppointments"."EndDate"}-30
else 0
FROM "tblAppointments"
WHERE "tblCustomer"."CustID" = "tblAppointments"."CustId"
ORDER BY "tblAppointments"."CustID"
)


Please let me know if more information is needed. Any direction is much appreciated.

Thanks,
DA

 

[lbass]

First create a formula {@null} by opening and saving a new formula without entering anything. Then change your conditional formula to:

//{@Open}:
if {tblCustomer.AccountStatusID} = 1 and
{table.apptdate} in CurrentDate-29 to currentdate then
{tblAppointments.ApptID} else
tonumber({@null})//remove the tonumber() if ApptID is a string

Replace {table.apptdate} with your actual appt date field.

//{@Closed}:
if {tblCustomer.AccountStatusID} = 2 and
{tblAppointments.EndDate} in Aged0To30Days then
{tblAppointments.ApptID} else
tonumber({@null})//remove the tonumber() if ApptID is a string

Place these in the detail section and then insert distinctcounts on them at the group and/or report level.

-LB

 

[DarkAngel]

LB

Thank you for your response!! Do you know if there is a way to accomplish this in one column – which includes both the Open and the Closed status appointments? The desire is to dump the report into Excel and have it only be one page wide (landscaped)…real estate is at a premium in this one

I have a record selection formula which takes the Max({tblAppointments.EndDate}), so I only get a count of the last appointment. If I remove the Max() and add a date range to the record selection, the report returns the appointment counts for the date range. Maybe I need to try this in a subreport?

Slimmed down version:

CustomerName Cust# TechnicianName LastAppt #LastAppts // (in last 30 days based on date ranges for closed/open)
Smith, Tony 533 Mills, Christine 09/30/2010 11
Doe, Jane 516 Mills, Christine 10/09/2010 25
Ingles, Kelly 185 Hobgood, Bill 10/14/2010 4

I appreciate any further direction or feedback you might provide.

Thank you again,
DA

 

[lbass]

//{@Both}:
if
(
{tblCustomer.AccountStatusID} = 1 and
{table.apptdate} in CurrentDate-29 to currentdate
) or
(
{tblCustomer.AccountStatusID} = 2 and{tblAppointments.EndDate} in Aged0To30Days
) then
{tblAppointments.ApptID} else
tonumber({@null})

I'm not clear on how you are using the maximum. You sample data is not clear to me either--it looks like you are grouping on customer and then on technician, so why would there be so many visits by one person per technician?

Can you show the selection formula where you used the maximum and explain more about why you are doing this?

-LB

 

[DarkAngel]

I figured out how to nest using or and was going to post the news. Then while getting information to answer your questions had an "ah-ha". I'm gonna run down that path for a minute will let you know results.

Thank you so much for helping!
DA

 

[DarkAngel]

Thank you LB! Your appointment count formula works perfectly. Just gotta figure out how to make it work with my other filters. Realized my “ah-ha” works because I’m not using the Max(date) to select records.

Ultimately the report will be grouped by Technician, then by Customer. I have it grouping by Customer initially because some customers have two Technicians (plus I can verify activity in the front end application by Customer but not Technician). Some Customers have daily appointments. The number of last appointments (#LastAppts) will reflect all the Customer’s appointment – for now.

Revised sample data:

CustomerName Cust# TechnicianName LastAppt #LastAppts // (in last 30 days based on date ranges for closed/open)
Doe, Jane 516 Hobgood, Bill 10/09/2010 25
Doe, Jane 516 Mills, Christine 10/09/2010 25
Ingles, Kelly 185 Hobgood, Bill 10/14/2010 4
Smith, Tony 533 Mills, Christine 09/30/2010 11

Created an SQL expression for the Max(date) is based on the LastAppt date.

(
SELECT DISTINCT Max("tblAppointments"."ApptDate") AS "Last Date of Service"
FROM "tblAppointments"
WHERE " tblAppointments"."CustID" = " tblCustomer"."CustId"
GROUP BY "tblCustomer"."CustId"
)

Then added it via select expert for records: { tblAppointments.ApptDate}={%MaxApptDate}

My internal client requested a report which lists both active and inactive customers. To include Customer name, Customer Account number, Technician Name, Account Opened Date, Account Closed Date, Last Appointment Date, and Number of Events (last 30 days, if closed – 30 days before Last Appt Date, if open – 30 days from current date).

Please let me know if there is further clarification I can provide.

 

[lbass]

You haven't defined the issue clearly, but if it is that you want the count to reflect only the most recent dates, then change the formula to:

if {tblAppointments.ApptDate}={%MaxApptDate} and
(
(
{tblCustomer.AccountStatusID} = 1 and
{table.apptdate} in CurrentDate-29 to currentdate
) or
(
{tblCustomer.AccountStatusID} = 2 and{tblAppointments.EndDate} in Aged0To30Days
)
) then
{tblAppointments.ApptID} else
tonumber({@null})

-LB

 

[DarkAngel]

Here are definitions of the column headers reference in my last post:

Customer name
Customer Account number - Customer’s Account Number
Technician Name
Account Opened Date – date the customer’s account was opened
Account Closed Date – date customer’s account was close, will be null/blank if Account Status is Open
Last Appointment Date – Date of most recent appointment, if they had one on 10/01/2010 and one on 10/13/2010, the report returns 10/13/2010 (uses Max(date))
* Number of Events – count of appointments in the last 30 days from a date determined by Account Status

*Date range for Number of Events differs depending on Account Status:
if Account Status is closed then count the number of appointments in the last 30 days as a Customer (count ALL appointments between the LastApptDate-30 days and LastApptDate)

if Account Status is open then count the number of appointments in the last 30 days from report date (count ALL appointments between CurrDate and CurrDate-30)

The max(date) filter is used to get the most recent appointment date (it is possible to have >1 in a given day but is not the norm). Regardless of the last appointment date, the Number of Events column should return a count of all appointments in the 30 day range.


Sample data from appointments table:
CustID LastAppt ApptID
215 10/14/2010 425926
215 10/7/2010 425920
215 9/30/2010 420688
215 9/21/2010 420681
215 9/19/2010 420677
312 10/8/2010 410083
345 10/14/2010 420548
345 10/7/2010 415838
345 9/30/2010 415837
345 9/21/2010 415836
404 10/13/2010 423869
404 10/12/2010 408682
404 9/30/2010 408681

Sample of how it should appear in report
CustAcct# TechnicianName LastAppt #Events
215 Hobgood, Bill 10/14/2010 5
215 Mills, Christine 10/14/2010
312 Mills, Christine 10/8/2010 1
345 Hobgood, Bill 10/14/2010 4
404 Mills, Christine 10/13/2010 3


I know, it’s clear as mud LOL. The formula you posted originally for both (posted 12:15) works great. I just need to get it to play nice with the rest of the report. I created a subreport that returns #Events, but run time performance – well you know – slow as a snail.

Thank you again!

 

[lbass]

But what is currently NOT working? I think I've followed all of this, but you aren't saying what the problem is.

-LB

 

[DarkAngel]

Sorry for that!

I created {@Null} and {@Both}. Placed {@Both} in the detail section and inserted distinctcounts for it at the group and/or report level. The SQL expression Max(LastApptDate) is used both in the detail and as a record selection formula.

What is not working?
The report only returns a count of the records that fall into the Max(LastApptDate) record selection filter. For example, Customer 215 returns a count of 2 events instead of 5. BTW meant to show 5 events for both rows (in last post) for this customer which shows two rows because of two Technicians.


DA

 

[lbass]

But why are you using the maxdate in the record selection formula? You can just place the SQL expression on the report for the last appt date column without using it as a filter.

-LB

 

[DarkAngel]

Good morning LB,

I was using maxdate in the record selection formula, because the report was returning so many records...like one for each record in the Appointments table. I found and corrected the join between the Customer table & Appointments table, but still get a gazillion records (in this case, a gazillion is over 30K - report should only return about 200).

The closed Accounts are returning nothing for appointment counts.

I have other record selection formulas which could be conflicting with the count formula. I'll remove to see what happens.

DA

 

[lbass]

It looks to me like you should just be limiting the appointments records to the last 30 days--but this depends upon whether you need to show all customers and/or all technicians regardless of whether they have any appointments. How do you currently have the tables joined? Which fields come from which tables?

-LB

 

[DarkAngel]

LB - thank you for your continued help troubleshooting this one!!!!

Yes, need to show all customers and/or all technicians regardless of whether they have any appointments.
The report pulls data from 13 different tables all spaghetti joined together. I created a report using the basic necessities (in case one of the other joins or my record selection formulas were the culprit). Still receive too many records.

Tables in Report:
tblCustomerDetails Left Outer Join on tblAppointments.CustID = tblCustomerDetails.CustID
tblCustomer Left Outer Join on tblCustomer.CustID = tblCustomerDetails.CustID
tblAppointments

I've reversed the join between tblCustomerDetails and tblCustomer, with same results.

Report Field Data Source
CustomerName tblCustomerDetails (concatenate last, first, middle names)
CustomerAcct# tblCustomerDetails
AcctStatusID tblCustomer
LastApptDate SQL Expression {%LastApptDate}
LastFace2Face Calculated Field {@LastF@F}
#Events Calculated Field {@StatusAll}

Formulas / Expressions:
{%LastApptDate}
(
SELECT DISTINCT Max("tblAppointments"."EndDate")
FROM "tblAppointments"
WHERE "tblAppointments"."CustID" = "tblCustomerDetails"."CustId"
GROUP BY "tblAppointments"."CustId"
)

{@LastF2F}
if {tblAppointments.ApptType} = 4510
then CStr ({tblAppointments.EndDate},"MM/dd/yy")
else "No Entry"

{@StatusAll}
if ({tblCustomer.AcctStatusID} = 1 and {tblAppointments.EndDate} in CurrentDate-30 to currentdate)
or ({tblCustomer.CustStatusID} in [2, 3] and {tblAppointments.EndDate} in Aged0To30Days)
then {tblAppointments.ApptID}
else tonumber({@null})

DA

 

[lbass]

I don't see the technician in there at all. Which table does that come from? I also can't tell the directions of your joins. Can you please copy the "show SQL query" into the thread?

-LB

 

[DarkAngel]

I figured out why the closed Accounts were returning nothing for appointment counts. I didn't realize Aged0To30Days is based on current date.

Technician comes from a table called 'users'. I thought it would be easier to trouble shoot the too many records issue if I removed all but the three basic tables. Since the issue persists, it seems it wasn't an issue with the previous tables/joins/record select formulas.

Here is the SQL from the "basics" report:

SELECT DISTINCT "tblCustomerDetails"."CustID", "tblAppointments"."EndDate", "tblCustomerDetails"."MiddleInitial", "tblCustomerDetails"."Lastname", "tblCustomerDetails"."Firstname", "tblCustomer"."AcctStatusID", "tblAppointments"."ApptId", "tblAppointments"."ApptType", (
SELECT DISTINCT Max("tblAppointments"."EndDate")
FROM "tblAppointments"
WHERE "tblAppointments"."CustID" = "tblCustomerDetails"."CustID"
GROUP BY "tblAppointments"."CustID"
)



FROM ("DbMain"."dbo"."tblCustomerDetails" "tblCustomerDetails" LEFT OUTER JOIN "DbMain"."dbo"."tblAppointments" "tblAppointments" ON "tblCustomerDetails"."CustID"="tblAppointments"."CustID") LEFT OUTER JOIN "DbMain"."dbo"."tblCustomer" "tblCustomer" ON "tblCustomerDetails"."CustID"="tblCustomer"."CustID"
ORDER BY "tblCustomerDetails"."CustID"


DA