human
Schema:
Customer_Master_Index (master_customer_id, cmi_details)
CMI_Cross_References (cmi_cross_ref_id, master_customer_id, source_system_code)
Council_Tax (council_tax_id, cmi_cross_ref_id)
Business_Rates (business_rates_id, cmi_cross_ref_id)
Benefits_Overpayments (council_tax_id, cmi_cross_ref_id)
Parking_Fines (council_tax_id, cmi_cross_ref_id)
Rent_Arrears (council_tax_id, cmi_cross_ref_id)
Electoral_Register (electoral_register_id, cmi_cross_ref_id)
Please answer with SQL.
assistant
Sure, I understand.
human
what are the details of the cmi masters that have the cross reference code 'Tax'?
assistant
SELECT T1.cmi_details FROM Customer_Master_Index AS T1 JOIN CMI_Cross_References AS T2 ON T1.master_customer_id = T2.master_customer_id WHERE T2.source_system_code = 'Tax'
human
What is the tax source system code related to the benefits and overpayments? List the code and the benifit id, order by benifit id.
assistant
SELECT T1.source_system_code , T2.council_tax_id FROM CMI_Cross_References AS T1 JOIN Benefits_Overpayments AS T2 ON T1.cmi_cross_ref_id = T2.cmi_cross_ref_id ORDER BY T2.council_tax_id
human
Wat is the tax source system code and master customer id of the taxes related to each parking fine id?
assistant
SELECT T1.source_system_code , T1.master_customer_id , T2.council_tax_id FROM CMI_Cross_References AS T1 JOIN Parking_Fines AS T2 ON T1.cmi_cross_ref_id = T2.cmi_cross_ref_id
human
What is the cmi cross reference id that is related to at least one council tax entry? List the cross reference id and source system code.
assistant
SELECT T1.cmi_cross_ref_id , T1.source_system_code FROM CMI_Cross_References AS T1 JOIN Council_Tax AS T2 ON T1.cmi_cross_ref_id = T2.cmi_cross_ref_id GROUP BY T1.cmi_cross_ref_id HAVING count(*) >= 1
human
How many different source system code for the cmi cross references are there?
assistant
SELECT count(DISTINCT source_system_code) FROM CMI_cross_references
human
What are the distinct cross reference source system codes which are related to the master customer details 'Gottlieb, Becker and Wyman'?
assistant
SELECT DISTINCT T2.source_system_code FROM customer_master_index AS T1 JOIN cmi_cross_references AS T2 ON T1.master_customer_id = T2.master_customer_id WHERE T1.cmi_details = "Gottlieb , Becker AND Wyman" ;