'Join is not showing all of the Master Table in Zoho SQL

I'm having some trouble My main table will have different number of rows but this month I know it have 64 and I'm only being shown 33. I'm a little lost on this one as I can't see what the issue is.

    SELECT
         "Staff_Monthly_Targets"."User ID" as "ID",
         "Staff_Monthly_Targets"."Full Name" as "Agent",
         "Staff_Monthly_Targets"."Role" as "Role",
         count_distinct("Revenue Splits by Split_Table"."Customer Id") as "Customers Sold",
         count_distinct(`Raw Customer No. calculation for LGs_v1.1`."Customer ID") AS "Profiled Customers",
         "Staff_Targeted_Revenue_v1.0"."Meter Sold Date" as "Sold Date",
         "Staff_Monthly_Targets"."Lower Customer Target" as "Lower",
         "Staff_Monthly_Targets"."Kicker Customer Target" as "Kicker",
         "Staff_Monthly_Targets"."Revenue Target" as "Rev Target",
         ("Staff_Monthly_Targets"."Revenue Target") * 0.3 as "30% of Rev Target",
         "Staff_Targeted_Revenue_v1.0"."Revenue Share" as "Rev Share",
         ("Staff_Targeted_Revenue_v1.0"."Revenue Share") / ("Staff_Monthly_Targets"."Revenue Target") * 100 as "% on Rev Target",
         if("Staff_Monthly_Targets"."Role"  = 'EC', if(count_distinct("Revenue Splits by Split_Table"."Customer Id")  < "Staff_Monthly_Targets"."Lower Customer Target", 'Not Hit', if(count_distinct("Revenue Splits by Split_Table"."Customer Id")  >= "Staff_Monthly_Targets"."Kicker Customer Target", 'Target Hit', 'Qualified')), if("Staff_Monthly_Targets"."Role"  = 'Closer', if(count_distinct("Revenue Splits by Split_Table"."Customer Id")  < "Staff_Monthly_Targets"."Lower Customer Target", 'Not Hit', if(count_distinct("Revenue Splits by Split_Table"."Customer Id")  >= "Staff_Monthly_Targets"."Kicker Customer Target", 'Target Hit', 'Qualified')), if("Staff_Monthly_Targets"."Role"  = 'Lead Gen', if(count_distinct(`Raw Customer No. calculation for LGs_v1.1`."Customer ID")  < "Staff_Monthly_Targets"."Lower Customer Target", 'Not Hit', if(count_distinct(`Raw Customer No. calculation for LGs_v1.1`."Customer ID")  >= "Staff_Monthly_Targets"."Kicker Customer Target", 'Target Hit', 'Qualified')), ''))) as 'Target Pass',
         If("Staff_Targeted_Revenue_v1.0"."Revenue Share"  < ("Staff_Monthly_Targets"."Revenue Target") * 0.3, 'Not Qualified', if("Staff_Targeted_Revenue_v1.0"."Revenue Share"  >= "Staff_Monthly_Targets"."Revenue Target", 'Target Hit', 'Qualified')) as "Rev Target Hit"
FROM  "Staff_Monthly_Targets"
LEFT JOIN "Revenue Splits by Split_Table" ON "Staff_Monthly_Targets"."User ID"  = "Revenue Splits by Split_Table"."User ID" 
RIGHT JOIN "Staff_Targeted_Revenue_v1.0" ON "Staff_Targeted_Revenue_v1.0"."Agent"  = "Revenue Splits by Split_Table"."Agent" 
RIGHT JOIN "Raw Customer No. calculation for LGs_v1.1" ON "Raw Customer No. calculation for LGs_v1.1"."Custom User ID"  = "Revenue Splits by Split_Table"."Custom User ID"  
WHERE    "Staff_Monthly_Targets"."User ID"  is not Null
 AND    is_previous_nmonth("Revenue Splits by Split_Table"."Date Locked In", 1)  = 1
GROUP BY "Rev Share",
     "Rev Target",
     "Kicker",
     "Lower",
     "Sold Date",
     "Role",
     "Agent",
      "ID"

any help on this will be great.

sqlzoho


Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source

Related Questions