I’m quite stumped, i have two different users with clients assigned to them. One has 17 leads two of which are ‘confirmed’ and the other has 2 clients, both ‘confirmed’. But the results of this are the first user has 19 leads apparently and the second has 0 clients except some how got 4 ‘confirmations’.
If someone could point out if i’ve made any obvious mistakes that would cause this, that’d be a great help, my knowledge of advanced SQL is abit gappy.
SELECT user.id
, user.prefix
, user.forename
, user.surname
, clients.clients
, confirmed.confirmed
, paid.paid
, deposit.deposit
, case_value.case_value as 'case'
, discard.count as discard
, callback.count as callback
FROM user_csb as csb
LEFT OUTER
JOIN users as user
ON user.id = csb.id
/* GENERAL STATS */
LEFT OUTER JOIN
(
SELECT count(sub1_client.id) as clients
, sub1_client.assigned_id
FROM conveyancing_clients as sub1_client
LEFT OUTER
JOIN conveyancing_quotes as sub1_quote
ON sub1_client.id = sub1_quote.id
WHERE sub1_client.created > 1265846400 AND sub1_client.created < 1266537599
) AS clients
ON clients.assigned_id = csb.id
LEFT OUTER JOIN
(
SELECT count(sub2_client.id) as confirmed
, sub2_client.assigned_id
FROM conveyancing_clients as sub2_client
LEFT OUTER
JOIN conveyancing_quotes as sub2_quote
ON sub2_client.id = sub2_quote.id
WHERE sub2_quote.confirm_time > 1265846400 AND sub2_quote.confirm_time < 1266537599
) AS confirmed
ON confirmed.assigned_id = csb.id
LEFT OUTER JOIN
(
SELECT count(sub3_client.id) as paid
, sub3_client.assigned_id
FROM conveyancing_clients as sub3_client
LEFT OUTER
JOIN conveyancing_quotes as sub3_quote
ON sub3_client.id = sub3_quote.id
WHERE sub3_quote.payment_time > 1265846400 AND sub3_quote.payment_time < 1266537599
) AS paid
ON paid.assigned_id = csb.id
LEFT OUTER JOIN
(
SELECT sum(sub4_quote.payment_amount) as deposit
, sub4_client.assigned_id
FROM conveyancing_clients as sub4_client
LEFT OUTER
JOIN conveyancing_quotes as sub4_quote
ON sub4_client.id = sub4_quote.id
WHERE sub4_quote.payment_time > 1265846400 AND sub4_quote.payment_time < 1266537599
) AS deposit
ON deposit.assigned_id = csb.id
LEFT OUTER JOIN
(
SELECT sum(sub5_quote.legal_fee) as case_value
, sub5_client.assigned_id
FROM conveyancing_clients as sub5_client
LEFT OUTER
JOIN conveyancing_quotes as sub5_quote
ON sub5_client.id = sub5_quote.id
WHERE sub5_quote.payment_time > 1265846400 AND sub5_quote.payment_time < 1266537599
) AS case_value
ON case_value.assigned_id = csb.id
/**
* Status
*/
/* Discard */
LEFT OUTER JOIN
(
SELECT count(discard_sub.id) as count
, discard_sub.assigned_id
FROM conveyancing_clients as discard_sub
LEFT OUTER
JOIN conveyancing_quotes as discard_sub_quote
ON discard_sub.id = discard_sub_quote.id
WHERE (discard_sub.status = 7 OR discard_sub.status = 8 OR discard_sub.status = 9) AND discard_sub.updated > 1265846400 AND discard_sub.updated < 1266537599
) AS discard
ON discard.assigned_id = csb.id
/* Callback */
LEFT OUTER JOIN
(
SELECT count(callback_sub.id) as count
, callback_sub.assigned_id
FROM conveyancing_clients as callback_sub
LEFT OUTER
JOIN conveyancing_quotes as callback_sub_quote
ON callback_sub.id = callback_sub_quote.id
WHERE (callback_sub.status = 4 OR callback_sub.status = 5 OR callback_sub.status = 6) AND callback_sub.updated > 1265846400 AND callback_sub.updated < 1266537599
) AS callback
ON callback.assigned_id = csb.id
WHERE csb.level >= 30 AND csb.level <= 50
ORDER BY paid.paid DESC
The two main tables
CREATE TABLE IF NOT EXISTS `conveyancing_clients` (
`id` int(13) unsigned auto_increment NOT NULL,
`sale_company` smallint(3) default 0 NOT NULL,
`campaign` smallint(5) default 0 NOT NULL,
`assigned_id` smallint(5) unsigned default 0,
`matter_company` smallint(3) default 0 NOT NULL,
`stream_id` int(11) unsigned default 0 NOT NULL,
`last_note` int(15) unsigned default 0 NOT NULL,
`email` varchar(320) NOT NULL,
`session_id` varchar(40) default '' NOT NULL,
`password` varchar(16) NOT NULL,
`api_key` char(16) NOT NULL,
`prefix` varchar(5) default '' NOT NULL,
`forename` varchar(50) NOT NULL,
`middlename` varchar(200) default '',
`surname` varchar(50) NOT NULL,
`main_contact` varchar(15) default '' NOT NULL,
`second_contact` varchar(15) default '',
`third_contact` varchar(15) default '',
`street` varchar(255) default '',
`locality` varchar(50) default '',
`region` varchar(50) default '',
`postcode` varchar(10) default '',
`status` tinyint(2) default 1 NOT NULL,
`source` tinyint(1) default 1 NOT NULL,
`source_extra` varchar(100) default '',
`created` int(10) default 0 NOT NULL,
`updated` int(10) default 0 NOT NULL,
`active` int(10) default 0 NOT NULL,
PRIMARY KEY (`id`),
FULLTEXT INDEX `allname` (`prefix`, `forename`, `middlename`, `surname`),
FULLTEXT INDEX `fullname` (`forename`, `middlename`, `surname`),
FULLTEXT INDEX `name` (`forename`, `surname`),
FULLTEXT INDEX `residence` (`street`, `locality`, `region`, `postcode`),
INDEX `email` (`email`),
INDEX `api_key` (`api_key`),
INDEX `client_api` (`email`, `api_key`),
INDEX `client_email_login` (`email`, `password`),
INDEX `client_id_login` (`id`, `password`),
INDEX `sale_company` (`sale_company`),
INDEX `matter_company` (`matter_company`),
INDEX `campaign` (`campaign`),
INDEX `assigned` (`assigned_id`),
INDEX `created` (`created`),
INDEX `updated` (`updated`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=32680 ;
CREATE TABLE IF NOT EXISTS `conveyancing_quotes` (
`id` int(13) unsigned NOT NULL,
`quote_type` tinyint(1) unsigned NOT NULL,
`hidden` decimal (6, 2) unsigned default 0 NOT NULL,
`discount` decimal(6, 2) unsigned default 0 NOT NULL,
`speed` tinyint(1) default 1 NOT NULL,
`legal_fee` decimal (9, 2) unsigned default 0,
`legal_vat` decimal (9, 2) unsigned default 0,
`disbursement_fee` decimal (9, 2) unsigned default 0,
`confirm_time` int(10) unsigned default 0 NOT NULL,
`payment_time` int(10) unsigned default 0 NOT NULL,
`payment_contact` varchar(50) default '' NOT NULL,
`payment_description` text,
`payment_method` tinyint(2) unsigned default 1 NOT NULL,
`payment_ref` varchar(20) default '' NOT NULL,
`payment_amount` decimal(9, 2) default 0 NOT NULL,
`payment_vat` decimal(9, 2) default 0 NOT NULL,
`sale` int(10) unsigned default 0,
`remortgage` int(10) unsigned default 0,
`purchase` int(10) unsigned default 0,
`hips` int(10) unsigned default 0,
`transfer` int(10) unsigned default 0,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Just any insight as to why it might be going wrong ? Should i post more data for if people want to test ?
Regards
Kieran