Trying to create date buckets in Report Studio but they aren't working correctly

Question

I have a MySQL table that I'm pulling some information that includes a date [Appt_Date]. If the data is showing in the query I want to create date buckets so I can total how many are between 0-7 days, 8-14 days, 15-21 days, 22-28 days or 29+ days.

My date bucket expressions are as follows for the 5 buckets:

case
when [Appt_Date] between (current_date) and _add_days (current_date,-7) then 1 else 0
end

case
when [Appt_Date] between _add_days(current_date,-8) and _add_days (current_date,-14) then 1 else 0
end

case
when [Appt_Date] between _add_days (current_date,-15) and _add_days (current_date,-21) then 1 else 0
end

case
when [Appt_Date] between _add_days (current_date,-22) and _add_days (current_date,-28) then 1 else 0
end

case
when ([Appt_Date] > _add_days ({current_date},-28)) then 1 else 0
end

The problem is that all the dates show with 0 in the first 4 buckets and with a 1 in the 29+ bucket.

I created a Today and Last Week field in the query. The Today field expression is: current_date and Last Week is: _add_days (current_date,-7)

Here is sample of the data Query data sample

You can see Today's date and how all the Appt_Dates are in the 29+ bucket.

Why aren't the buckets working correctly?

Answer 1

You need to reverse your betweens

case
when [Appt_Date] between _add_days (current_date,-7) and (current_date) then 1 else 0
end

And I presume do

case
when ([Appt_Date] < _add_days ({current_date},-28)) then 1 else 0
end

If I understand your logic correctly