Cacher is the code snippet organizer for pro developers

***************************************************************************************************************************************
***************************************************************************************************************************************
**************************************************input file stats / DUNS Matches*******************************************************
--Match stats Summary / Total technology installs to DUNS                    
SELECT count(DISTINCT ord.ID ) as 'Input Rows'                             
, COUNT(DISTINCT DUNSNumber) as 'Distinct Input DUNS'
,COUNT(hit_id) as 'Total Records'
, Count(distinct hit_id) as 'Unique Installs'
,COUNT(DISTINCT product_id) as 'Product Count'
from fulfillment.dbo.orderrequestdetail ord 
join fulfillment.dbo.ph_loc_duns_core u on ord.DUNSNumber = u.dnb_duns_nbr
where orderrequestid = 10710

--product breakdown summary matched to DUNS 
SELECT product_id as ProductID
, Product, Vendor
, category_parent as 'Category Parent'
, category as Category
, isnull(Attribute,'') Attributes
, count(DISTINCT ord.ID) as Installs
from fulfillment.dbo.orderrequestdetail ord 
join fulfillment.dbo.ph_loc_duns_core u on ord.DUNSNumber = u.dnb_duns_nbr
where orderrequestid = 10710 
Group BY product_id 
, Product, Vendor
, category_parent
, category 
, attribute
ORDER BY COUNT(*) DESC


--Full tech stack of prospects from DUNS Table
SELECT ord.ClientID as 'ClientID'
, [DUNSNumber]
, isnull(ord.[url],'') as URL
, ord.[Company] AS company
,ord.[Address1]
,ord.[City]
,ord.[State]
,ord.[Zip]
, product_id as ProductID
, Product
, Vendor
, date_first_verified as 'Date First Verified'
, date_last_verified as 'Date Last Verified'
, intensity as 'Intensity'
, category_parent as 'Category Parent'
, category as Category
, isnull(Attribute,'') Attributes
--, revenue_range as 'revenue'
--, employees_range as 'employees'
from fulfillment.dbo.orderrequestdetail ord 
join fulfillment.dbo.ph_loc_duns_core u on ord.DUNSNumber = u.dnb_duns_nbr
where orderrequestid = 10710
ORDER BY ord.id, vendor, product

--**********************************************************************************************************----
***************************************************************************************************************************************
                                                    URL Delivery Snippets
--*************************************************************************************************************************************                                                   
 --total breakout counts
SELECT count(DISTINCT ord.ID ) as 'Input Rows'  --Shows Count of Client Input Rows Matched
, COUNT(DISTINCT u.URL) as 'Total URLs'         --Shows Total Distinct URLs that Matched
,COUNT(hit_id) as 'Total Records'               --Shows Total Records Matched to Client File
,Count(DISTINCT hit_id) as 'Unique Installs'    --Shows Unque Installs we have at those Matched URLs
,COUNT(DISTINCT product_id) as 'Product Count'  --Shows Unique Products
from fulfillment.dbo.orderrequestdetail ord     --Client request Table ( Where Clients data was imported too)
join fulfillment.dbo.ph_url_global_all u on ord.url_match = u.url   --(matching to the URL GLobal ( Company Product Installs)
where OrderRequestID = 10710                     - Shows what client import


--product breakdown matched to URL / per product breakdown showing how many imput rows for each product
SELECT product_id as ProductID
, Product, Vendor
, category_parent as 'Category Parent'
, category as Category
, isnull(Attribute,'') Attributes
, count(DISTINCT ord.ID) as Installs
from fulfillment.dbo.orderrequestdetail ord 
join fulfillment.dbo.ph_url_global_all u on ord.url_match = u.url
where orderrequestid = 10710 
Group BY product_id 
, Product, Vendor
, category_parent
, category 
, attribute
ORDER BY COUNT(*) DESC

--Standard URL output  
select ord.ClientID as 'ClientID'
, [DUNSNumber]
, isnull(ord.[url],'') as URL
, ord.[Company] AS Company
,ord.[Address1]
,ord.[City]
,ord.[State]
,ord.[Zip]
,product_id as 'ProductID'
,Product
,Vendor
,date_first_verified as 'Date First Verified'
,date_last_verified AS 'Date Last Verified'
,intensity as 'Intensity'
,category_parent AS 'Category Parent'
,Category
,ISNULL(Attribute, '') AS 'Attributes'
--,revenue_range AS 'Revenue'
--,employees_range AS 'Employees'
--,top_level_industry AS 'Top Level Industry'
--,sub_level_industry AS 'Sub Level Industry'
--, ISNULL(hq_address, '') as 'HQ Address'
--, ISNULL(hq_city, '') as 'HQ City'
--, ISNULL(hq_state, '') as 'HQ State'
--, ISNULL(hq_zip, '') as 'HQ Zip'
--, ISNULL(hq_country, '') as 'HQ Country'
--, ISNULL(hq_phone, '') as 'HQ Phone'
from fulfillment.dbo.orderrequestdetail ord 
join fulfillment.dbo.ph_url_global_all u on ord.url_match = u.url
where OrderRequestID = 10710
ORDER BY ord.id, vendor, product

***************************************************************************************************************************************
                                                    Sample SAP Delivery Code
--************************************************************************************************************************************* 
-- Summary Counts
SELECT count(DISTINCT ord.ID ) as 'Input Rows Matched' 
, COUNT(DISTINCT u.dnb_duns_nbr) as 'Distinct Input DUNS'  --changed to u.dnb_duns_nbr from ord.DUNSNumber.  Client did not supply and input DUNS values
,COUNT(hit_id) as 'Total Records'
, Count(distinct hit_id) as 'Unique Installs'
,COUNT(DISTINCT product_id) as 'Product Count'
from fulfillment.dbo.orderrequestdetail ord 
join fulfillment.dbo.ph_loc_duns_core u on ord.URL_match = u.url
where OrderRequestID = 10691
--AND category_id IN(30) -- only ERP
AND category_id IN(30,36) -- ERP/HCM
AND date_last_verified >= dateadd(MONTH, -54, getdate())
AND ord.url_match = 'cvs.com'

--product breakdown summary matched to DUNS 
SELECT product_id as ProductID
, Product, Vendor
, category_parent as 'Category Parent'
, category as Category
, isnull(Attribute,'') Attributes
, count(DISTINCT ord.ID) as 'Input Rows'
, count(DISTINCT u.hit_id) as Installs
from fulfillment.dbo.orderrequestdetail ord 
join fulfillment.dbo.ph_loc_duns_core u on ord.URL_match = u.url
where OrderRequestID = 10691
--AND category_id IN(30) -- only ERP
AND category_id IN(30,36) -- ERP/HCM
AND date_last_verified >= dateadd(MONTH, -54, getdate())
AND ord.url_match = 'cvs.com'
Group BY product_id 
, Product, Vendor
, category_parent
, category 
, attribute
ORDER BY COUNT(*) DESC

--Full tech stack of prospects from DUNS Table
SELECT TOP 300 ord.Company as  'Client Company'
, url_match as 'URL'
,dnb_duns_nbr as 'DUNS'
,u.company as Company,
u.address as Address
,isnull(u.city,'') as City
,isnull(u.State,'') as State
,isnull(u.Zip,'') as Zip
,isnull(u.Country,'') as Country
, product_id as ProductID
, Product
, Vendor
, date_first_verified as 'Date First Verified'
, date_last_verified as 'Date Last Verified'
, intensity as 'Intensity'
, category_parent as 'Category Parent'
, category as Category
, isnull(Attribute,'') Attributes
, u.dnb_sales_volume_us Revenue
, u.dnb_emp_total Employees
, u.sic_2 SIC2
, u.sic_2_description 'SIC2 Description'
, u.sic_3 SIC3
, u.sic_3_description 'SIC3 Description'
, u.sic_4 SIC4
, u.sic_4_description 'SIC4 Description'
from fulfillment.dbo.orderrequestdetail ord 
join fulfillment.dbo.ph_loc_duns_core u on ord.URL_match = u.url
where OrderRequestID = 10691
--AND category_id IN(30) -- only ERP
AND category_id IN(30,36) -- ERP/HCM
AND date_last_verified >= dateadd(MONTH, -54, getdate())
ORDER BY ord.Company, u.dnb_duns_nbr, u.vendor, u.product --added Order BY statement