I am getting a join error "Sorry, query execution failed as the columns used in the JOIN conditions might contain too many duplicate values. Please ensure that at-least one of the column used in JOIN condition contains unique values."
This is a query that previously worked and I have multiple reports pulling data on it. I haven't changed anything within the query. Suddenly, now reports that are dependent aren't able to populate data and when running the query I get the error statement listed above.
Is this a Zoho bug? If not, how do i resolve this issue?
SELECT
max("payorauthorization"."AuthEntryDate") AS 'AuthEntryDate',
current_auth_rendered.AuthId AS 'AuthId',
cast(group_first("current_auth_rendered"."Weekly_Hours") as float) AS 'Weekly_Hours',
"current_auth_rendered"."AuthClientId" AS 'AuthClientId',
"current_auth_rendered"."YearWeek" AS 'YearWeek',
"current_auth_rendered"."Service_Desc" AS 'Service_Desc',
"current_auth_rendered"."Monday" AS 'Monday',
"current_auth_rendered"."CompositeKey" AS 'AuthCompositeKey',
"current_bx_weekly_sp_hours1"."Compositekey" AS 'SPCompositeKey',
cast(avg("current_auth_rendered"."Authorized Hours") as float) AS 'Authorized Hours',
"current_auth_rendered"."Rendered Hours" AS 'Rendered Hours',
"current_auth_rendered"."sum_hours" AS 'sum_hours',
"clientpayor"."ClientPayorType" as "ClientPayorType",
"clientpayor"."IsCurrentPayor" as "IsCurrentPayor",
"clientpayor"."ClientPayorCompanyId" as "ClientPayorCompanyId",
"clientpayor"."ClientPayorPlanId" as "ClientPayorPlanId",
"contactprincipal"."PrincipalContactId" as "PS_ID",
"contactprincipal"."PrincipalProviderFullName" as 'Program Supervisor',
"contactprincipal"."PrincipalProviderType" AS 'PrincipalProviderType',
IF("contactprincipal"."PrincipalProviderType" = 'SuperSupervisor', 'Program Supervisor', IF("contactprincipal"."PrincipalProviderType" = 'SpeechTherapist', 'Speech Language Pathologist', IF("contactprincipal"."PrincipalProviderType" = 'SeniorConsultant', 'Occupational Therapist', ' '))) AS 'Supervision Position',
group_first("current_bx_weekly_sp_hours1"."SP_start_yearweek") AS 'SP Start Week',
"current_bx_weekly_sp_hours1"."SP_end_yearweek" AS 'SP End Week',
if_null("current_bx_weekly_sp_hours1"."Service Plan Hours", 0) as "Service Plan Hours",
if_null("Termination Date"."Termination Date", add_year(current_date(), 1)) as "Patient Termination Date",
if("current_auth_rendered"."Monday" BETWEEN if_null("Start Date"."Start Date", '2017-01-01 00:00:00') AND if_null("Termination Date"."Termination Date", add_year(current_date(), 1)), 'Yes', 'No') as 'Active Patient',
if_null("Start Date"."Start Date", '2017-01-01 00:00:00') as "Patient Start Date",
"payorauthorization"."AuthId" AS 'AuthorizationId',
"payorplan"."payorname" AS 'PayorName',
"sp_sp_service_plans_leads_c"."sp_sp_service_plans_leadssp_sp_service_plans_idb" AS 'SP_id',
"payorauthorization"."Authorization Period" AS 'AuthorizationPeriod',
RANK() OVER(PARTITION BY "current_auth_rendered"."CompositeKey" ORDER BY current_auth_rendered.AuthId DESC ) AuthRank,
RANK() OVER(PARTITION BY "current_bx_weekly_sp_hours1"."Compositekey" ORDER BY "sp_sp_service_plans"."sp_active_date" DESC ) SPRank,
IF(if_null((sum("current_auth_rendered"."Rendered Hours") / sum("current_bx_weekly_sp_hours1"."Service Plan Hours")), 0) * 100 <= 30, '30% and under', IF(if_null((sum("current_auth_rendered"."Rendered Hours") / sum("current_bx_weekly_sp_hours1"."Service Plan Hours")), 0) * 100 > 30
AND if_null((sum("current_auth_rendered"."Rendered Hours") / sum("current_bx_weekly_sp_hours1"."Service Plan Hours")), 0) * 100 <= 50, '50%', IF(if_null((sum("current_auth_rendered"."Rendered Hours") / sum("current_bx_weekly_sp_hours1"."Service Plan Hours")), 0) * 100 > 50
AND if_null((sum("current_auth_rendered"."Rendered Hours") / sum("current_bx_weekly_sp_hours1"."Service Plan Hours")), 0) * 100 <= 70, '70%', IF(if_null((sum("current_auth_rendered"."Rendered Hours") / sum("current_bx_weekly_sp_hours1"."Service Plan Hours")), 0) * 100 > 70
AND if_null((sum("current_auth_rendered"."Rendered Hours") / sum("current_bx_weekly_sp_hours1"."Service Plan Hours")), 0) * 100 <= 90, '90%', IF(if_null((sum("current_auth_rendered"."Rendered Hours") / sum("current_bx_weekly_sp_hours1"."Service Plan Hours")), 0) * 100 > 90, 'Greater than 90%', 'Check'))))) AS 'Percent Bucket',
"contactprincipal"."PrincipalProviderId",
"sp_sp_service_plans"."sp_active_date",
IF("current_auth_rendered"."Service_Desc" = 'Behavioral Individual Therapy'
OR "current_auth_rendered"."Service_Desc" = 'Behavioral Group Therapy', "current_auth_rendered"."Rendered Hours", 0) AS '1:1 and Group Rendered Hours',
IF("current_auth_rendered"."Service_Desc" = 'Behavioral Supervision', "current_auth_rendered"."Rendered Hours", 0) AS 'Supervision Rendered Hours'
FROM "current_auth_rendered"
JOIN "leads_cstm" ON "current_auth_rendered"."AuthClientId" = "leads_cstm"."eo_id_c"
INNER JOIN "sp_sp_service_plans_leads_c" ON "sp_sp_service_plans_leads_c"."sp_sp_service_plans_leadsleads_ida" = "leads_cstm"."id_c"
INNER JOIN "sp_sp_service_plans" ON "sp_sp_service_plans"."id" = "sp_sp_service_plans_leads_c"."sp_sp_service_plans_leadssp_sp_service_plans_idb"
INNER JOIN "current_bx_weekly_sp_hours1" ON "current_auth_rendered"."CompositeKey" = "current_bx_weekly_sp_hours1"."Compositekey"
LEFT JOIN "contactprincipal" ON "current_auth_rendered"."AuthClientId" = "contactprincipal"."PrincipalContactId"
AND "current_auth_rendered"."Monday" >= "contactprincipal"."PrincipalStartDate"
AND "current_auth_rendered"."Monday" <= "contactprincipal"."End Date"
LEFT JOIN "Termination Date" ON "current_auth_rendered"."AuthClientId" = "Termination Date"."ContactId"
LEFT JOIN "Start Date" ON "Start Date"."ContactId" = "current_auth_rendered"."AuthClientId"
INNER JOIN "payorauthorization" ON "current_auth_rendered"."AuthId" = "payorauthorization"."AuthId"
INNER JOIN "timebilling" ON "current_auth_rendered"."AuthId" = "timebilling"."TimeBillingAuthorizationId"
INNER JOIN "clientpayor" ON "payorauthorization"."AuthPayorPlanId" = "clientpayor"."ClientPayorPlanId"
AND "payorauthorization".AuthClientId = "clientpayor".ClientId
LEFT JOIN "payorplan" ON "payorplan"."payorplanid" = "payorauthorization"."authpayorplanid"
LEFT JOIN "leads" ON "leads"."id" = "leads_cstm"."id_c"
WHERE "clientpayor"."ClientPayorType" = 'Primary'
AND "clientpayor"."ClientPayorDeletedDate" IS Null
AND "current_auth_rendered"."Monday" BETWEEN "ClientPayorCoverageStartDate" AND if_null("ClientPayorCoverageEndDate", add_year(current_date(), 2))
AND "timebilling"."TimeBillingIsDeleted" IS NULL
AND "timebilling"."TimeBillingIsVoid" IS NULL
GROUP BY "current_auth_rendered"."YearWeek",
"current_auth_rendered"."AuthClientId",
"current_auth_rendered"."Service_Desc",
"current_auth_rendered"."Monday",
"current_auth_rendered"."CompositeKey",
"current_auth_rendered"."Rendered Hours",
"current_auth_rendered"."sum_hours",
"clientpayor"."ClientPayorType",
"clientpayor"."IsCurrentPayor",
"clientpayor"."ClientPayorCompanyId",
"clientpayor"."ClientPayorPlanId",
"contactprincipal"."PrincipalContactId",
"contactprincipal"."PrincipalProviderFullName",
"contactprincipal"."PrincipalProviderType",
"current_bx_weekly_sp_hours1"."SP_start_yearweek",
"current_bx_weekly_sp_hours1"."SP_end_yearweek",
"payorplan"."payorname",
if_null("current_bx_weekly_sp_hours1"."Service Plan Hours", 0),
if_null("Termination Date"."Termination Date", add_year(current_date(), 1)),
if("current_auth_rendered"."Monday" BETWEEN if_null("Start Date"."Start Date", '2017-01-01 00:00:00') AND if_null("Termination Date"."Termination Date", add_year(current_date(), 1)), 'Yes', 'No'),
if_null("Start Date"."Start Date", '2017-01-01 00:00:00'),
"payorauthorization"."AuthId",
"current_bx_weekly_sp_hours1"."Compositekey",
"sp_sp_service_plans_leads_c"."sp_sp_service_plans_leadssp_sp_service_plans_idb",
IF("current_auth_rendered"."Service_Desc" = 'Behavioral Individual Therapy', "current_auth_rendered"."Weekly_Hours", NULL),
"contactprincipal"."PrincipalProviderId",
current_auth_rendered.AuthId,
"sp_sp_service_plans"."sp_active_date",
"payorauthorization"."Authorization Period",
IF("contactprincipal"."PrincipalProviderType" = 'SuperSupervisor', 'Program Supervisor', IF("contactprincipal"."PrincipalProviderType" = 'SpeechTherapist', 'Speech Language Pathologist', IF("contactprincipal"."PrincipalProviderType" = 'SeniorConsultant', 'Occupational Therapist', ' ')))
ORDER BY "current_auth_rendered"."Yearweek"