[Vtigercrm-developers] MySQL select statement

[Alan Lord (News)]

On 18/12/12 16:50, John Crisp wrote:
>
> Well at least I guessed it had something to do with the JOINS :-)
>
> I *think* this has got me what I needed :
>
> SELECT DISTINCT `vtiger_account`.`accountid`,
> `vtiger_account`.`accountname`,
> `vtiger_account`.`account_no`,
> `vtiger_accountscf`.`cf_592`
>
> FROM `vtiger_crmentity`
> INNER JOIN `vtiger_account` ON `vtiger_account`.`accountid` =
> `vtiger_crmentity`.`crmid`
> INNER JOIN `vtiger_accountscf` ON `vtiger_accountscf`.`accountid` =
> `vtiger_account`.`accountid`
> INNER JOIN `vtiger_invoice` ON `vtiger_invoice`.`accountid` =
> `vtiger_account`.`accountid`
> AND YEAR(`vtiger_invoice`.`invoicedate`) = 2012
>
>
> Just need to physically tot up the rows but I think it has done the job.

Stupid Question: Could you not get this information from a vtiger report?

Secondly, don't forget to check for deleted accounts.

Cheers

Al


-- 
Libertus Solutions
http://www.libertus.co.uk