:
The script below is commonly used during data conversions so the customer and data tech can verify the counts of customers etc are what they should be. It can also be used in day to day operation as a quick auditing tool.
declare @customerid int,
@vendorid int,
@partid int
--UPDATE:
set @customerid = 1 --Last CustomerID before import
set @vendorid = 1 --Last VendorID before import
set @partid = 1 --Last PartID before import
--Static code below
select distinct
cust.cnt [Total Customers], ar.cnt [AR Customer], anr.cnt [ANR Customers] ,canc.cnt [Canc Customers], sites.cnt [Sites], systems.cnt [Systems], Recurring.cnt [Recurring Lines],
Recurring.[Total RMR] [Total RMR], Invoices.cnt [Open AR Invoices], Invoices.[Total Open AR] [Open AR $ Total], Notes.cnt [Notes],
BillContacts.cnt [Bill Contacts],SiteContacts.cnt [Site Contacts], Inspections.cnt [Inspections], Vendors.cnt [Vendors],
VendorBills.cnt [Open AP Invoices], VendorBills.total [Open AP $ Total], Parts.cnt [Parts], Inventory.onhandnew [Inventory OnHandNew], Inventory.valueonhand [Inventory Value on Hand], Zones.cnt [Zones]
from ar_customer c
cross apply (select count(*) [cnt] from ar_customer where customer_id > @customerid) Cust
cross apply (select count(*) [cnt] from ar_customer where customer_id > @customerid and Customer_Status_Id = 1) AR
cross apply (select count(*) [cnt] from ar_customer where customer_id > @customerid and Customer_Status_Id = 2) ANR
cross apply (select count(*) [cnt] from ar_customer where customer_id > @customerid and Customer_Status_Id = 3) canc
cross apply (select count(*) [cnt] from AR_Customer_Site where customer_id > @customerid) sites
cross apply (select count(*) [cnt] from AR_Customer_System where customer_id > @customerid) Systems
cross apply (select count(*) [cnt], sum(monthly_amount) [Total RMR] from ar_customer_recurring where customer_id > @customerid) Recurring
cross apply (select count(*) [cnt], sum(amount) [Total Open AR] from ar_invoice where customer_id > @customerid) Invoices
cross apply (select count(*) [cnt] from ar_customer_notes where customer_id > @customerid) Notes
cross apply (select count(*) [cnt] from AR_Bill_Contact bc join ar_customer_bill b on bc.Bill_Id = b.Customer_Bill_Id join ar_customer c on b.customer_id = c.customer_id where c.customer_id > @customerid) [BillContacts]
cross apply (select count(*) [cnt] from AR_Site_Contact bc join ar_customer_site b on bc.Site_Id = b.Customer_Site_Id join ar_customer c on b.customer_id = c.customer_id where c.customer_id > @customerid) [SiteContacts]
cross apply (select count(*) [cnt] from SV_Inspection i join ar_customer_system sy on i.Customer_System_Id = sy.Customer_System_Id join ar_customer c on sy.customer_id = c.customer_id where c.customer_id > @customerid) [Inspections]
cross apply (select count(*) [cnt] from ap_vendor v where v.vendor_id > @vendorid) [Vendors]
cross apply (select count(*) [cnt], sum(amount) [total] from AP_Invoice where vendor_id > @vendorid) [VendorBills]
cross apply (select count(*) [cnt] from in_part where part_id > @partid) Parts
cross apply (select sum(value_on_hand) [valueonhand], sum(on_hand_new) [onhandnew] from in_inventory where part_id > @partid) Inventory
cross apply (select count(*) [cnt] from CS_Customer_System_Zone z join ar_customer_system sy on z.Customer_System_Id= sy.Customer_System_Id join ar_customer c on sy.Customer_Id = c.Customer_Id where c.customer_id > @customerid) Zones
where customer_id > @customerid