Query on Multiple Dimentions

[SivanVigder]

Here is a table of Codes that  Dentists assigned as treatment they have done to different Teeth in different Appointments. It can be that same Dentist assigns multiple Codes in an Appointment, and also that two Dentists assigned different or similar Codes in the same Appointment. 

Code Dentist Appt  Tooth

A10        S         1          37

H35       S         1          37

A10        A         2         38

H35       A         2         17

A10         B        3         28

H35        A        3         28

A10         A        4         37

H11         A        4         37

Query 1 needed  - Count  appointments where Code H35 or H11  assigned  in combination with A10 at the same Appt by the same Dentist 

Query 1a.

Count  appointments where Code H35 or H11  assigned  in combination with A10 at the same Appt by same Dentist for the same Tooth 

Query 2 - wild card  

Count  appointments where any H?? Code (any H followed by 2 digits) was assigned  in combination with A10 at the same Appt by same Dentist and the same Tooth 

Query 2a.  - wilder card

Same like query 2 only wild card H*  (H followed by anything )

———

[asinha1991]

are you ok with load script solutions?  if not let me know what kind of visualization you are creating

Q1:

Load sum(finalcount) as finalcountQ1; 

load if( (wildmatch(concat(CODE,',') ,'*H35*') or wildmatch(concat(CODE,',') ,'*H11*')) and wildmatch(concat(CODE,','),'*A10*'),1)  as finalcount,DENTIST FROM yourdatasource GROUP BY DENTIST,APPT;

Q1a

Load sum(finalcount) as finalcountQ1a; 

load if( (wildmatch(concat(CODE,',') ,'*H35*') or wildmatch(concat(CODE,',') ,'*H11*')) and wildmatch(concat(CODE,','),'*A10*'),1)  as finalcount,DENTIST,Tooth FROM yourdatasource GROUP BY DENTIST,APPT,Tooth;

Q2

Load sum(finalcount) as finalcountQ2; 

load if( wildmatch(concat(CODE,',') ,'*H*')  and wildmatch(concat(CODE,','),'*A10*'),1)  as finalcount,DENTIST,Tooth FROM yourdatasource GROUP BY DENTIST,APPT,Tooth;

Q2a

how it is different than Q2?

[asinha1991]

 I am replying here because my limit for private messages has exceeded (surprisingly)

for frontend you can convert what I sent to aggr maybe, somethings like this

for Q1

load if( (wildmatch(concat(CODE,',') ,'*H35*') or wildmatch(concat(CODE,',') ,'*H11*')) and wildmatch(concat(CODE,','),'*A10*'),1)  as finalcount,DENTIST FROM yourdatasource GROUP BY DENTIST,APPT;

change it to   sum(aggr( if( (wildmatch(concat(CODE,',') ,'*H35*') or wildmatch(concat(CODE,',') ,'*H11*')) and wildmatch(concat(CODE,','),'*A10*'),1)  ,DENTIST,APPT))

just add parameter you group by in aggr

[rishabirshad]

---

@SivanVigder wrote:

Here is a table of Codes that  Dentists assigned as treatment they have done to different Teeth in different Appointments. It can be that same Dentist assigns multiple Codes in an Appointment, and also that two Dentists assigned different or similar Codes in the same Appointment. 

Code Dentist Appt  Tooth

A10        S         1          37

H35       S         1          37

A10        A         2         38

H35       A         2         17

A10         B        3         28

H35        A        3         28

A10         A        4         37

H11         A        4         37

Query 1 needed  - Count  appointments where Code H35 or H11  assigned  in combination with A10 at the same Appt by the same Dentist 

Query 1a.

Count  appointments where Code H35 or H11  assigned  in combination with A10 at the same Appt by same Dentist for the same Tooth 

Query 2 - wild card  

Count  appointments where any H?? Code (any H followed by 2 digits) was assigned  in combination with A10 at the same Appt by same Dentist and the same Tooth 

Query 2a.  - wilder card

Same like query 2 only wild card H*  (H followed by anything )

———

---

Query 1: Count appointments where Code H35 or H11 assigned in combination with A10 at the same Appt by the same Dentist.

To answer this query, we need to find the appointments where both A10 and either H35 or H11 codes were assigned in the same appointment by the same dentist. We can use the following SQL query for this:

SELECT COUNT(DISTINCT Appt)
FROM table_name
WHERE (Code = 'A10' OR Code = 'H35' OR Code = 'H11')
AND Dentist = 'S'
GROUP BY Appt
HAVING COUNT(DISTINCT Code) = 2;

This will give you the count of appointments where both A10 and either H35 or H11 codes were assigned in the same appointment by the same dentist (in this case, 'S').

Query 1a: Count appointments where Code H35 or H11 assigned in combination with A10 at the same Appt by same Dentist for the same Tooth.

To answer this query, we need to find the appointments where both A10 and either H35 or H11 codes were assigned in the same appointment by the same dentist for the same tooth. We can use the following SQL query for this:

SELECT COUNT(DISTINCT Appt)
FROM table_name
WHERE (Code = 'A10' OR Code = 'H35' OR Code = 'H11')
AND Dentist = 'S'
GROUP BY Appt, Tooth
HAVING COUNT(DISTINCT Code) = 2;

This will give you the count of appointments where both A10 and either H35 or H11 codes were assigned in the same appointment by the same dentist for the same tooth.

Query 2: Count appointments where any H?? Code (any H followed by 2 digits) was assigned in combination with A10 at the same Appt by the same Dentist and the same Tooth.

To answer this query, we need to find the appointments where any code starting with H (followed by two digits) and A10 were assigned in the same appointment by the same dentist for the same tooth. We can use the following SQL query for this:

SELECT COUNT(DISTINCT Appt)
FROM table_name
WHERE (Code LIKE 'H__' OR Code = 'A10')
AND Dentist = 'S'
GROUP BY Appt, Tooth
HAVING COUNT(DISTINCT Code) = 2;

This will give you the count of appointments where any code starting with H (followed by two digits) and A10 were assigned in the same appointment by the same dentist for the same tooth.

Query 2a: Same like query 2 only wild card H* (H followed by anything).

To answer this query, we need to find the appointments where any code starting with H and A10 were assigned in the same appointment by the same dentist for the same tooth. We can use the following SQL query for this:

SELECT COUNT(DISTINCT Appt)
FROM table_name
WHERE (Code LIKE 'H%' OR Code = 'A10')
AND Dentist = 'S'
GROUP BY Appt, Tooth
HAVING COUNT(DISTINCT Code) = 2;

This will give you the count of appointments where any code starting with H and A10 were assigned in the same appointment by the same dentist for the same tooth.

[Chanty4u]

Query 1

COUNT(

    DISTINCT {<Code={'A10'}, Dentist={$(=concat(DISTINCT Dentist, ','))}>} Appt)

Q1a)

COUNT(

    DISTINCT {<Code={'A10'}, Dentist={$(=concat(DISTINCT Dentist, ','))}>} 

    IF(

        COUNT(

            DISTINCT {<Code={'H35','H11'}, Tooth={$(=max(Tooth))}, Appt={$(=max(Appt))}, Dentist={$(=concat(DISTINCT Dentist, ','))}>} Appt

        ) > 0,

        Appt

    )

)

Q2

COUNT(

    DISTINCT {<Code={'A10'}, Dentist={$(=concat(DISTINCT Dentist, ','))}>} 

    IF(

        COUNT(

            DISTINCT {<Code={'H??'}, Tooth={$(=max(Tooth))}, Appt={$(=max(Appt))}, Dentist={$(=concat(DISTINCT Dentist, ','))}>} Appt

        ) > 0,

        Appt

    )

)

Q2a

COUNT(

    DISTINCT {<Code={'A10'}, Dentist={$(=concat(DISTINCT Dentist, ','))}>} 

    IF(

        COUNT(

            DISTINCT {<Code={'H*'}, Tooth={$(=max(Tooth))}, Appt={$(=max(Appt))}, Dentist={$(=concat(DISTINCT Dentist, ','))}>} Appt

        ) > 0,

        Appt

    )

)