Queries: General

Statistics

Various counts

select cast('Business partners: Count' as text) as heading, cast('1900/01/01' as text) as since, count(*) from c_bpartner a where a.ad_client_id = 1000000 and created>='1900/01/01'
union all
select cast('Business partners: Inactive' as text) as heading, cast('1900/01/01' as text) as since, count(*) from c_bpartner a where a.ad_client_id = 1000000 and created>='1900/01/01' and isactive = 'N'
union all
select cast('Business partners: Suppliers' as text) as heading, cast('1900/01/01' as text) as since, count(*) from c_bpartner a where a.ad_client_id = 1000000 and a.isvendor = 'Y' and created>='1900/01/01'
union all
select cast('Business partners: Customers' as text) as heading, cast('1900/01/01' as text) as since, count(*) from c_bpartner a where a.ad_client_id = 1000000 and a.iscustomer = 'Y' and created>='1900/01/01'
union all
select cast('Business partners: Count' as text) as heading, cast('1900/01/01' as text) as since, count(*) from c_bpartner a where a.ad_client_id = 1000000 and created>='1900/01/01'
union all
select cast('Invoices: Count' as text) as heading, cast('1900/01/01' as text) as since, count(*) from c_invoice a where a.ad_client_id = 1000000 and created>='1900/01/01'
union all
select cast('Invoices: Reversed or voided' as text) as heading, cast('1900/01/01' as text) as since, count(*) from c_invoice a where a.ad_client_id = 1000000 and created>='1900/01/01' and docstatus in ('RE','VO')
union all
select cast('Invoices: Suppliers' as text) as heading, cast('1900/01/01' as text) as since, count(*) from c_invoice a where a.ad_client_id = 1000000 and a.issotrx = 'N' and created>='1900/01/01'
union all
select cast('Invoices: Customers' as text) as heading, cast('1900/01/01' as text) as since, count(*) from c_invoice a where a.ad_client_id = 1000000 and a.issotrx = 'Y' and created>='1900/01/01'
union all
select cast('Payments: Count' as text) as heading, cast('1900/01/01' as text) as since, count(*) from c_payment a where a.ad_client_id = 1000000 and created>='1900/01/01'
union all
select cast('Payments: Reversed or voided' as text) as heading, cast('1900/01/01' as text) as since, count(*) from c_payment a where a.ad_client_id = 1000000 and created>='1900/01/01' and docstatus in ('RE','VO')
union all
select cast('Payments: Suppliers' as text) as heading, cast('1900/01/01' as text) as since, count(*) from c_payment a where a.ad_client_id = 1000000 and a.isreceipt = 'N' and created>='1900/01/01'
union all
select cast('Payments: Customers' as text) as heading, cast('1900/01/01' as text) as since, count(*) from c_payment a where a.ad_client_id = 1000000 and a.isreceipt = 'Y' and created>='1900/01/01'
union all
select cast('Assets: Count' as text) as heading, cast('1900/01/01' as text) as since, count(*) from a_asset a where a.ad_client_id = 1000000 and created>='1900/01/01'
union all
select cast('Assets: Inactive' as text) as heading, cast('1900/01/01' as text) as since, count(*) from a_asset a where a.ad_client_id = 1000000 and created>='1900/01/01' and isactive = 'N'
;

Invoices grouped by document type name

select dt.name as DocumentType, cast('1900/01/01' as text) as since, count(*) from c_invoice a join c_doctype dt on a.c_doctype_id = dt.c_doctype_id where a.ad_client_id = 1000000 and a.created>='1900/01/01' group by dt.name;

Credits

Initial contribution by: nTier Software Services http://www.ntier.co.za ngordon7000

Add your credit here.