I’m running some queries on tracker analytics table, and noticed that “count(distinct tei)” and “count(distinct psi)” return slightly different values on a sql query.
Using “psi” gives me bigger values, but I’ve seen people using “tei” on queries.
So, I would like to understand why do both get me different results? On which situations one would be more appropriate to use?
I believe that psi stands for program stage instance, and tei stands for tracked entity instance.
The queries are to fetch events from a tracker program.
Hi @joao.machiana, Depending on what you want to do, you could use either; tei is the entity you are tracking; it could be a person or item, like in the malaria program; tei is the person accessing the malaria program. On the other hand, psi counts the number of times the same program stage is repeated either for the same tei or just for the program stage across different teis. Each time a program stage is repeated, it’s assigned a new psi, but the tei will remain the same across the repeats, so if you are to distinct on both, you will have 1 tei apreaing in more different psi(s) depending on how many times the program stage was repeated for that same tei.
Thank you @WaluQ
I think this clarifies my question.
Is there a particular reason for the event report queries, to use count(pi) (I believe it stands for program instance) instead of using count(tei)?
Hi @Gassim
Yes, there’s a need to create sql views to accomodate some of our requirements, and I wanted to understand better the choices to count of “patients” that I had.
As i stated in the my previous response, I saw that event report queries, do count(pi), and I’m asking to myself if it should not be doing count(tei), if we are count the number of patient that match a particular condition on a stage