troubleshooting Question

SQL Query issues with a JOIN

Avatar of James Murphy
James MurphyFlag for Australia asked on
Microsoft SQL ServerSQL
7 Comments1 Solution130 ViewsLast Modified:
Hi,

I am trying to add in another join this is what I am attempting to add in:

"LEFT JOIN de_transaction ON de_rowid = tr_rowid_debtor"

but everytime I do (even without adding anything else to the select part of the clause), the query takes a minute longer, and the results of the below parts of the select clause are incorrect - but they are correct when i don't have the additional join in there.

",SUM(CASE WHEN co.co_type = '0' and co.co_completed = '1' and co.co_description != '@tvarEmail' and co.co_who != 'Update' THEN 1 END) letter_count
,SUM(CASE WHEN co.co_type = '0' and co.co_completed = '1' and co.co_description = '@tvarEmail' THEN 1 END) Email_count
,SUM(CASE WHEN co.co_type = '22' and co.co_completed = '1' THEN 1 END) SMS_Count
,SUM(CASE WHEN co.co_type = '1' and co.co_completed = '1' THEN 1 END) Phone_Count"

SELECT CASE WHEN cl_number = '1003' THEN 'ZQQML' 
WHEN cl_number = '1004' THEN 'ZQQML' 
ELSE 'Z1QML'
END as TCODE
,de_listed_date
,de_number	
,de_cl_account
,cl_number
,de_name
,de_owing
,de_status
,de_address
,de_address_1
,de_city
,de_state
,de_zip
,de_home_phone
,de_cell_phone
,de_work_phone
,de_other_phone
,de_email
,cl_alt_number
,cl_user_2
,de_status
,de_listed_date
,de_active
,SUM(CASE WHEN co.co_type = '0' and co.co_completed = '1' and co.co_description != '@tvarEmail' and co.co_who != 'Update' THEN 1 END) letter_count
,SUM(CASE WHEN co.co_type = '0' and co.co_completed = '1' and co.co_description = '@tvarEmail' THEN 1 END) Email_count
,SUM(CASE WHEN co.co_type = '22' and co.co_completed = '1' THEN 1 END) SMS_Count
,SUM(CASE WHEN co.co_type = '1' and co.co_completed = '1' THEN 1 END) Phone_Count
,sum(CASE WHEN co.co_type = '0' and co.co_completed = '1' and co.co_who != 'Update' THEN 1
WHEN co.co_type = '1' and co.co_completed = '1' THEN 1 
WHEN co.co_type = '22' and co.co_completed = '1' THEN 1 END) as Contact_attempt_date
,max(CASE WHEN co.co_type = '0' and co.co_completed = '1' and co.co_who != 'Update' THEN co.co_done_date
WHEN co.co_type = '1' and co.co_completed = '1' THEN co.co_done_date 
WHEN co.co_type = '22' and co.co_completed = '1' THEN co.co_done_date END) as Contact_attempt_date
,CASE WHEN cl_number = '1003' THEN 'Tier 1' 
WHEN cl_number = '1004' THEN 'Tier 2' 
ELSE null
END as TIER
,de_business
,de_business_address
,de_sin
,de_dl
,de_dob
,Max(CASE WHEN co.co_type = '2' THEN co.co_done_date END) Last_status_change
,Max(CASE WHEN co.co_type = '3' THEN co.co_description END) Prom_desc
,Max(CASE WHEN co.co_type = '3' THEN co.co_amount END) Prom_amt
,Max(CASE WHEN co.co_type = '3' THEN co.co_date END) Prom_dd
,Max(CASE WHEN co.co_type = '3' THEN co.co_cdate END) Prom_cd
,Max(CASE WHEN co.co_type = '3' and co.co_repeat = 0 THEN 'No Repeat'
WHEN co.co_type = '3' and co.co_repeat = 1 THEN 'DAILY'
WHEN co.co_type = '3' and co.co_repeat = 2 THEN 'Weekly'
WHEN co.co_type = '3' and co.co_repeat = 3 THEN 'Bi-weekly'
WHEN co.co_type = '3' and co.co_repeat = 4 THEN 'Monthly' END) frequency
,Max(CASE WHEN co.co_type = '3' THEN co.co_cby END) Collector
    FROM debtor     
    JOIN client ON cl_rowid = de_rowid_client
    LEFT JOIN contact co ON de_rowid = co.co_rowid_debtor
    WHERE cl_number between '1003' and '1004' and de_active = 'A'    
GROUP BY
cl_number
,de_listed_date
,de_number
,de_cl_account
,de_name
,de_owing
,de_status
,de_address
,de_address_1
,de_city
,de_state
,de_zip
,de_home_phone
,de_cell_phone
,de_work_phone
,de_other_phone
,de_email
,cl_alt_number
,cl_user_2
,de_collector
,de_salesman
,de_owned_by_debtor
,de_principal
,de_paid
,de_last_payment_date
,de_last_worked_date
,de_active
,de_poe
,de_business
,de_business_address
,de_sin
,de_dob
,de_dl
,de_zip
,de_state
,de_cell_phone
,de_home_phone
,de_email
,de_owing

Could anyone please assist?

many thanks!
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 7 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 7 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros