Do we have any Connectwise Sql hackers out there that might need a SQL script to grab a list of all primary company contacts for all companies invoiced over the last 6 months so you can submit that to Microsoft Partner Portal for the survey Microsoft expects you to send out twice a year?  You know the one where you submit a excel spreadsheet of those customers and Microsoft send them a survey on your behalf.

Well here is a quick SQL Query that will produce that very list for you. Just export to a tab delimited text file.

SELECT [dbo].[v_Company_Contact].[Company_Name], [dbo].[v_Company_Contact].[First_Name], [dbo].[v_Company_Contact].[Last_Name], [dbo].[v_Contact_Communication_Type].[Description] AS Email,[dbo].[Contact_Communication].[Description] AS Phone
FROM [Your Database Name].[dbo].[v_Company_Contact]
JOIN [dbo].[v_Contact_Communication_Type] ON [dbo].[v_Contact_Communication_Type].[Contact_RecID] = [dbo].[v_Company_Contact].[Contact_RecID]
JOIN [dbo].[Contact_Communication] ON [dbo].[Contact_Communication].[Contact_RecID] = [dbo].[v_Company_Contact].[Contact_RecID]
WHERE Company_RecID IN (SELECT Company_RecID
FROM [Your Database Name].[dbo].[v_rpt_Invoices]
WHERE Date_Invoice between (‘01/01/2012‘) and (‘07/01/2012‘)
Group By Company_RecID)
and [dbo].[v_Company_Contact].[Default_Flag] = ‘1’
and [dbo].[v_Contact_Communication_Type].[Communication_Type_RecID] = ‘1’
and [dbo].[Contact_Communication].[Communication_Type_RecID] = ‘2’

 

Find the line in the query “WHERE Date_Invoice between (’01/01/2012′) and (’07/01/2012′)”  and change the dates for any range you want to include also change the name of the database to reflect your CW database. WordPress changes ( ‘ ) in to ( ` ) when you copy and paste SQL query so you may need to go through query and revert them back to ( ‘ ). See dates and any thing that has a “=” before it.

I hope my SQL Foo helps some one out there in Connectwise land

Cubert 😎