Jump to content

MySQL - first query worked 100% - but second and third don't exactly corroborate the first.


Greywacke

Recommended Posts

hi there, the following query was developed and works as it should.

SELECT 	(		SELECT 			COUNT(DISTINCT SP1.bigint_ProspectID, PLS1.smallint_ProspectOrdinal) 		FROM 10_serviceprospects SP1 		LEFT JOIN 13_prospectleadsent PLS1 ON SP1.bigint_ProspectID = PLS1.bigint_ProspectID 		WHERE PLS1.timestamp_Sent 			BETWEEN "2013-08-01 00:00:00" AND "2013-08-30 23:59:59" 			AND SP1.bigint_ServiceID = SS.bigint_ServiceID 			AND PLS1.bigint_SupplierID = S.bigint_SupplierID 	) AS `Previous Month`, 	(		SELECT 			COUNT(DISTINCTROW SP2.bigint_ProspectID, PLS2.smallint_ProspectOrdinal) 		FROM 10_serviceprospects SP2 		LEFT JOIN 13_prospectleadsent PLS2 ON SP2.bigint_ProspectID = PLS2.bigint_ProspectID 		WHERE PLS2.timestamp_Sent 			BETWEEN "2013-09-01 00:00:00" AND "2013-09-31 23:59:59" 			AND SP2.bigint_ServiceID = SS.bigint_ServiceID 			AND PLS2.bigint_SupplierID = S.bigint_SupplierID 	) AS `Last Month`, 	(		SELECT 			COUNT(DISTINCT SP3.bigint_ProspectID, PLS3.smallint_ProspectOrdinal, PLS3.bigint_SupplierID) 		FROM 10_serviceprospects SP3 		LEFT JOIN 13_prospectleadsent PLS3 ON SP3.bigint_ProspectID = PLS3.bigint_ProspectID 		WHERE PLS3.timestamp_Sent 			BETWEEN "2013-10-01 00:00:00" AND "2013-10-30 23:59:59" 			AND SP3.bigint_ServiceID = SS.bigint_ServiceID 			AND PLS3.bigint_SupplierID = S.bigint_SupplierID 	) AS `Current Month`, 	(		SELECT 			COUNT(DISTINCT SP0.bigint_ProspectID, PLS0.smallint_ProspectOrdinal) 		FROM 10_serviceprospects SP0 		LEFT JOIN 13_prospectleadsent PLS0 ON SP0.bigint_ProspectID = PLS0.bigint_ProspectID 		WHERE PLS0.timestamp_Sent 			BETWEEN "2013-08-01 00:00:00" AND "2013-10-30 23:59:59" 			AND SP0.bigint_ServiceID = SS.bigint_ServiceID 			AND PLS0.bigint_SupplierID = S.bigint_SupplierID 	) AS `Total`, 	S.text_SupplierName AS `Supplier Name` FROM 5_suppliers S JOIN 4_servicesuppliers SS ON (S.bigint_SupplierID = SS.bigint_SupplierID) WHERE SS.bigint_ServiceID = 1 												# Bakkie Canopies OnlyAND S.smallint_SupplierStatus = 4 											# Freemium Suppliers OnlyGROUP BY S.bigint_SupplierID ORDER BY `Total` DESC, `Supplier Name` ASC;										# Order by Total, then Supplier Name

took about a day to develop this inbetween other work. it returns the correct data.

the output is available at https://performatix.co/csv/Bakkie_Canopies_Freemium_Supplier_Opportunities_2013-10-09.csv

i was then requested to change the ordering by `Total` and primarily by `Region Description`; as well as grouping by Region ID and primarily by Supplier ID, creating a secondary output.

SELECT 	R.text_RegionDescription AS `Region Description`, 	(		SELECT 			COUNT(DISTINCT SP1.bigint_ProspectID, PLS1.smallint_ProspectOrdinal) 		FROM 10_serviceprospects SP1 		LEFT JOIN 13_prospectleadsent PLS1 ON SP1.bigint_ProspectID = PLS1.bigint_ProspectID 		WHERE PLS1.timestamp_Sent 			BETWEEN "2013-08-01 00:00:00" AND "2013-08-30 23:59:59" 			AND SP1.bigint_ServiceID = SS.bigint_ServiceID 			AND PLS1.bigint_SupplierID = S.bigint_SupplierID 			AND SP1.bigint_RegionID = R.bigint_RegionID	) AS `Previous Month`, 	(		SELECT 			COUNT(DISTINCTROW SP2.bigint_ProspectID, PLS2.smallint_ProspectOrdinal) 		FROM 10_serviceprospects SP2 		LEFT JOIN 13_prospectleadsent PLS2 ON SP2.bigint_ProspectID = PLS2.bigint_ProspectID 		WHERE PLS2.timestamp_Sent 			BETWEEN "2013-09-01 00:00:00" AND "2013-09-31 23:59:59" 			AND SP2.bigint_ServiceID = SS.bigint_ServiceID 			AND PLS2.bigint_SupplierID = S.bigint_SupplierID 			AND SP2.bigint_RegionID = R.bigint_RegionID	) AS `Last Month`, 	(		SELECT 			COUNT(DISTINCT SP3.bigint_ProspectID, PLS3.smallint_ProspectOrdinal, PLS3.bigint_SupplierID) 		FROM 10_serviceprospects SP3 		LEFT JOIN 13_prospectleadsent PLS3 ON SP3.bigint_ProspectID = PLS3.bigint_ProspectID 		WHERE PLS3.timestamp_Sent 			BETWEEN "2013-10-01 00:00:00" AND "2013-10-30 23:59:59" 			AND SP3.bigint_ServiceID = SS.bigint_ServiceID 			AND PLS3.bigint_SupplierID = S.bigint_SupplierID 			AND SP3.bigint_RegionID = R.bigint_RegionID	) AS `Current Month`, 	(		SELECT 			COUNT(DISTINCT SP0.bigint_ProspectID, PLS0.smallint_ProspectOrdinal) 		FROM 10_serviceprospects SP0 		LEFT JOIN 13_prospectleadsent PLS0 ON SP0.bigint_ProspectID = PLS0.bigint_ProspectID 		WHERE PLS0.timestamp_Sent 			BETWEEN "2013-08-01 00:00:00" AND "2013-10-30 23:59:59" 			AND SP0.bigint_ServiceID = SS.bigint_ServiceID 			AND PLS0.bigint_SupplierID = S.bigint_SupplierID 			AND SP0.bigint_RegionID = R.bigint_RegionID	) AS `Total`, 	S.text_SupplierName AS `Supplier Name` FROM 5_suppliers S JOIN 4_servicesuppliers SS ON (S.bigint_SupplierID = SS.bigint_SupplierID) JOIN 1_regions R ON (SS.bigint_RegionID = R.bigint_RegionID)WHERE SS.bigint_ServiceID = 1 											# Bakkie Canopies OnlyAND S.smallint_SupplierStatus = 4 										# Freemium Suppliers OnlyGROUP BY SS.bigint_RegionID, SS.bigint_SupplierID ORDER BY `Total` DESC, `Region Description` ASC;								# Order by Total, then Region Description 

this took about an hour inbetween other work.

the results are in https://performatix.co/csv/Bakkie_Canopies_Regional_Freemium_Supplier_Opportunities_2013-10-09.csv

however you will notice that the values do not corroborate with the first (this was discovered recently after reviewing the csv's! what is wrong with this query to get it to corroborate with the first?). i was then asked to order by `Total` but primarily by `Region Description`, and group by Region ID - creating a third output.

SELECT 	R.text_RegionDescription AS `Region Description`, 	(		SELECT 			COUNT(DISTINCT SP1.bigint_ProspectID, PLS1.smallint_ProspectOrdinal) 		FROM 10_serviceprospects SP1 		LEFT JOIN 13_prospectleadsent PLS1 ON SP1.bigint_ProspectID = PLS1.bigint_ProspectID 		WHERE PLS1.timestamp_Sent 			BETWEEN "2013-08-01 00:00:00" AND "2013-08-30 23:59:59" 			AND SP1.bigint_ServiceID = SS.bigint_ServiceID 			AND PLS1.bigint_SupplierID = S.bigint_SupplierID 			AND SP1.bigint_RegionID = R.bigint_RegionID	) AS `Previous Month`, 	(		SELECT 			COUNT(DISTINCTROW SP2.bigint_ProspectID, PLS2.smallint_ProspectOrdinal) 		FROM 10_serviceprospects SP2 		LEFT JOIN 13_prospectleadsent PLS2 ON SP2.bigint_ProspectID = PLS2.bigint_ProspectID 		WHERE PLS2.timestamp_Sent 			BETWEEN "2013-09-01 00:00:00" AND "2013-09-31 23:59:59" 			AND SP2.bigint_ServiceID = SS.bigint_ServiceID 			AND PLS2.bigint_SupplierID = S.bigint_SupplierID 			AND SP2.bigint_RegionID = R.bigint_RegionID	) AS `Last Month`, 	(		SELECT 			COUNT(DISTINCT SP3.bigint_ProspectID, PLS3.smallint_ProspectOrdinal, PLS3.bigint_SupplierID) 		FROM 10_serviceprospects SP3 		LEFT JOIN 13_prospectleadsent PLS3 ON SP3.bigint_ProspectID = PLS3.bigint_ProspectID 		WHERE PLS3.timestamp_Sent 			BETWEEN "2013-10-01 00:00:00" AND "2013-10-30 23:59:59" 			AND SP3.bigint_ServiceID = SS.bigint_ServiceID 			AND PLS3.bigint_SupplierID = S.bigint_SupplierID 			AND SP3.bigint_RegionID = R.bigint_RegionID	) AS `Current Month`, 	(		SELECT 			COUNT(DISTINCT SP0.bigint_ProspectID, PLS0.smallint_ProspectOrdinal) 		FROM 10_serviceprospects SP0 		LEFT JOIN 13_prospectleadsent PLS0 ON SP0.bigint_ProspectID = PLS0.bigint_ProspectID 		WHERE PLS0.timestamp_Sent 			BETWEEN "2013-08-01 00:00:00" AND "2013-10-30 23:59:59" 			AND SP0.bigint_ServiceID = SS.bigint_ServiceID 			AND PLS0.bigint_SupplierID = S.bigint_SupplierID 			AND SP0.bigint_RegionID = R.bigint_RegionID	) AS `Total`FROM 5_suppliers S JOIN 4_servicesuppliers SS ON (S.bigint_SupplierID = SS.bigint_SupplierID) JOIN 1_regions R ON (SS.bigint_RegionID = R.bigint_RegionID)WHERE SS.bigint_ServiceID = 1 							# Bakkie Canopies OnlyAND S.smallint_SupplierStatus = 4 						# Freemium Suppliers OnlyGROUP BY SS.bigint_RegionID ORDER BY `Total` DESC, `Region Description` ASC;	# Order by Total, then Region Description

this took approximately another hour between other work.

the results are in https://performatix.co/csv/Bakkie_Canopies_Regional_Freemium_Supplier_Opportunities_2013-10-09.csv

once again - the data does not corroborate with the first csv,

the issue i am facing is that i do not understand why the queries are not synchronous to the data... the first query works just as well for a different service, however the second and third queries do not return anything at all. i am assuming that this is because they do not have varying regions (all the same). further than this i just keep hitting my head... anyone comfortable enough to have a look at the queries and suggest changes other than the ones i applied?

the data from the tables are available at https://performatix.co/csv/1_regions, 2_servicescatalogue, 4_servicesuppliers, 5_suppliers, 10_serviceprospects & 13_prospectleadsent.csv.

Edited by Pierre 'Greywacke' du Toit
Link to comment
Share on other sites

What exactly is the issue? The first spreadsheet is a summary of the other 2. Kango, for example, is listed with 34 on the first spreadsheet (it's only listed once), and if you add up all of the entries for Kango on the other sheets (listed multiple times) then it adds up to 34. What errors are you seeing with the data?

Link to comment
Share on other sites

What exactly is the issue? The first spreadsheet is a summary of the other 2. Kango, for example, is listed with 34 on the first spreadsheet (it's only listed once), and if you add up all of the entries for Kango on the other sheets (listed multiple times) then it adds up to 34. What errors are you seeing with the data?

 

afraid i need to try a different approach to resolving this, i'm going to be re-writing the queries from scratch today - according to my client not all of the records tally up... especially when a different working service is used! -_- will reply back here with the queries once working 100%. after discussing with a friend (unfortunately he's terribly busy), i realised that in the second and third selects - that the JOIN types need to change as the perspective changes. will update once this work is complete.

Edited by Pierre 'Greywacke' du Toit
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...