!
Hello -
I have the following table:
| ID | Start Date | active_date | Contact Date |
| 4e1205d9-439a-48d2-bf86-49c9d413652f | 6/20/2013 | 5/24/2018 | 2/10/2018 |
| 4e1205d9-439a-48d2-bf86-49c9d413652f | 6/20/2013 | 5/24/2018 | 3/17/2018 |
| 4e1205d9-439a-48d2-bf86-49c9d413652f | 6/20/2013 | 5/24/2018 | 3/31/2018 |
I want to figure out how many times a contact occured before/after the start/active dates.
I have the following script
load
NoConcatenate
"ID",
sum(if("Contact Date" <= [Start Date],1,0)) as calls_before,
sum(if("Contact Date" > [Start Date] and "Contact Date" < "active_date",1,0)) as calls_after,
sum(if("Contact Date" > date("active_date"),1,0)) as calls_after_discharge,
count("Contact Date") as total_calls
resident res_contacts
group by "ID";
My expectation would be I would get a single row like this
| ID | calls_before | calls_after | calls_after_discharge | total_calls |
| 4e1205d9-4 !39a-48d2-bf86-49c9d413652f | 0 | 3 | 0 | 3 |
However, what I get is
| ID | calls_before | calls_after | calls_after_discharge | total_calls |
| 4e1205d9-439a-48d2-bf86-49c9d413652f | 0 | 9 | 0 | 9 |
I'm coming from a SQL background so this grouping behavior doesn't make sense to me. I've made sure there is not assoications or linkings inside the application to make sure I'm not getting a cartesian product. Even in Data Manager it shows the wrong values.
!
