Date dormancy calculation

Mark HHP

Hey, I'm trying to figure out a dormancy rule. I need to check a clients bookings and if there are no bookings within a year, their status must become dormant.

I have:

If Client.TotalBookings=0 Then Client.Status='Dormant'

If Client.TotalBookings>= 1 Then Client.Status='Active'

BUT to get to those, I had: Client.TotalBookings=COUNT Booking WHERE (Client.Bookings.BookingDate>=DATE_ADD(CURRENT_DATE,-365))

It seems to work but not, at the same time. If the Client.Status is 'Active' it seems to use the same value across all clients. So any Client that is 'Active' would have the same value of '6'

I'm sure the last part (CURRENT_DATE,-365) is incorrect. I hate date functions at the end of the day...

Is there a better way around this?

Rennur

Try Rule:

IF COUNT Bookings WHERE (Bookings IN Client.Bookings AND Bookings.BookingDate>=DATE_ADD(CURRENT_DATE,-365)=0
Then
Client.Status='Dormant'
Else
Client.Status='Active'

Mark HHP

Why, thank you, kind sir!

It seems to have worked. Although, I did have to insert a close bracket

IF COUNT Bookings WHERE (Bookings IN Client.Bookings AND Bookings.BookingDate>=DATE_ADD(CURRENT_DATE,-365))=0
Then
Client.Status='Dormant'
Else
Client.Status='Active'

Looking at it this morning, I wonder how I couldn't figure this out in the first place!