Renee Spencer
$25/hr
Expert in Excel, Data, Accounting and Administrative work.
- Reply rate:
- -
- Availability:
- Hourly ($/hour)
- Age:
- 58 years old
- Location:
- Chandler, Arizona, United States
- Experience:
- 25 years
How to SQL Query document for team
SQL Query to pull Company and Contacts out of the MDR Database
select * from pending.site where processlogid = 514
select s.siteguid, s.SiteName, ps.SiteName,ps.AddressLine1,s.BillingAddressLine1
from site s
inner join pending.Site ps on ps.SiteGuid = s.SiteGuid and ps.P
rocessLogId = 526
select p.*
from dbo.Person p
inner join pending.person pp on p.PersonGuid = pp.MergedIntoPersonGuid
where pp.ProcessLogId = 571
select p.SiteGuid, p.PersonGuid, p.FirstName, p.LastName, s.SiteName,
P.BusinessEmailAddress
from pending.person pp (nolock)
inner join dbo.person p (nolock) on p.personguid = pp.mergedintopersonguid
inner join dbo.site s (nolock) on s.siteguid = p.siteguid
where pp.ProcessLogId = 621
select p.*, s.*
from pending.person pp (nolock)
inner join dbo.person p (nolock) on p.personguid = pp.mergedintopersonguid
inner join dbo.site s (nolock) on s.siteguid = p.siteguid
where pp.ProcessLogId = 616
where s.SiteGuid = 'A8D52616-E8C7-E211-AB-AE7AAD'
'Marketsphere Consulting, L.L.C.'
select *
from SiteHistory h where h.SiteGuid = 'B09C262D-0714-40C2-B-C2F8044’
//Extracting imported data in production – Site Info Only
select s.*, ps.ProcessLogId
from site s
inner join pending.Site ps on (ps.SiteGuid = s.SiteGuid OR ps.M
ergedIntoSiteGuid =
s.SiteGuid)
and (ps.ProcessLogId = 514 or ps.ProcessLogId=526)
Query
select p.*
from iovmbiproddb.MDR.dbo.Person p
inner join iovmbiproddb.MDR.pending.person pp on p.PersonGuid = pp.MergedIntoPersonGuid
where pp.ProcessLogId = 571
select p.SiteGuid, p.PersonGuid, p.FirstName, p.LastName, s.SiteName,
P.BusinessEmailAddress
from iovmbiproddb.MDR.pending.person pp (nolock)
inner join iovmbiproddb.MDR.dbo.person p (nolock) on p.personguid =
pp.mergedintopersonguid
inner j
oin iovmbiproddb.MDR.dbo.site s (nolock) on s.siteguid = p.s
iteguid
where pp.ProcessLogId = 621
select p.*, s.*
from pending.person pp (nolock)
inner join iovmbiproddb.MDR.dbo.person p (nolock) on p.personguid =
pp.mergedintopersonguid
inner join iovmbiproddb.MDR.dbo.site s (n
olock) on s.siteguid = p.s
iteguid
where pp.ProcessLogId = 616
select * from
pending.site (nolock)
where processlogid = 526
and siteguid in ('A1B1908B-0F27-43A3-A8EC-062229C35F12',
'44471A8A-7787-4B1D-8315-25A1CFC59209',
'12B-D-45D5-A-C967274',
'7C4060C4-C--A81E-A106950CA191',
'B09C262D-0714-40C2-B-C2F8044',
'11D38B-D8-A0A5-CA185DA73239',
'D3F29C2B-0A-D83-3E83FCBC03BA')
select * from
pending.site (nolock)
where --processlogid = 526
siteguid ='B09C262D-0714-40C2-B-C2F8044'
To get SiteGuids ONLY
select s.*
from dbo.site s
inner join pending.site ps on s.SiteGuid = ps.MergedIntoSiteGuid
where ps.ProcessLogId = 514
Select *
From [dbo].[CDP- NetApp_CB_S123] cb
join [dbo].[CDP-USAContactAppend] rf
on cb.TVID=rf.TVID
select *
from pending.site ps
where ps.ProcessLogId = 526
select s.SiteName,ps.SiteName as PendingSiteName,
s.BillingAddressLine1, ps.AddressLine1 as PendingBillingAddressLine1
from dbo.site s
inner join pending.site ps on s.SiteGuid = ps.MergedIntoSiteGuid
where ps.ProcessLogId = 526
and s.SiteName != ps.SiteName
and s.BillingAddressLine1 != ps.AddressLine1
select *
from [Pending].[Site] ps
where ps.ProcessLogId = 514 and ps.[SubmittalStatusId]<>1
select s.*
Into DemandGenCBdata
from iovmbiproddb.MDR.dbo.site s (nolock)
inner join iovmbiproddb.MDR.Pending.Site ps (nolock) on s.SiteGuid =
ps.MergedIntoSiteGuid
where (ps.ProcessLogId = 514 or ps.ProcessLogId=526)
select s.*
Into DGMDRSiteImportFinal
from iovmbiproddb.MDR.dbo.site s (nolock)
inner join iovmbiproddb.MDR.pending.site ps on s.SiteGuid = ps.MergedIntoSiteGuid
where ps.ProcessLogId = 539
/*-----------------------select s.*
Into DGMDRSiteImportFinal
from iovmbiproddb.MDR.dbo.site s (nolock)
inner join iovmbiproddb.MDR.pending.site ps on s.SiteGuid = ps.MergedIntoSiteGuid
where ps.ProcessLogId = 539
------------------------*/
(214936 row(s) affected)
--total count is 271847 rows
select p.*
Into DGMDRPersonImportFinal
from iovmbiproddb.MDR.dbo.Person p (nolock)
inner join iovmbiproddb.MDR.pending.Person ps on p.PersonGuid = ps.MergedIntoPersonGuid
where ps.ProcessLogId = 539
/*-----------------------select p.*
Into DGMDRPersonImportFinal
from iovmbiproddb.MDR.dbo.Person p (nolock)
inner join iovmbiproddb.MDR.pending.Person ps on p.PersonGuid = ps.MergedIntoPersonGuid
where ps.ProcessLogId = 539
------------------------*/
(271843 row(s) affected)
99585
select distinct
c.CB_ID,pp.MergedIntoPersonGuid,pp.SiteGuid,c.FirstName,c.LastName,c.SiteName
into #tmpc
from pending.person pp
inner join tmpContacts- c on c.FirstName = pp.FirstName
and c.LastName = pp.LastName and c.PersonEmailAddress = pp.BusinessEmailAddress
where pp.ProcessLogId = 539
create index #tmpc on tempdb..#tmpc (MergedIntoPersonGuid)
select s.[SiteGuid]
,s.[DunsId]
,p.[PersonGuid]
,p.[PublicProfileURL]
,p.[FirstName]
,p.[LastName]
,p.[BusinessEmailAddress]
,p.[JobTitle]
,p.[Level]
,s.[BillingEmailAddress]
,s.[BillingAddressLine1]
,s.[BillingAddressLine2]
,s.[BillingAddressCity]
,s.[BillingAddressStateAbbreviation]
,s.[BillingAddressPostalCode]
,s.[BillingAddressPostalCodeExtension]
,s.[BillingAddressCountryCode]
,s.[BusinessPhoneNumber]
,s.[BusinessPhoneNumberExtension]
,p.[BusinessPhoneNumber]
,p.[BusinessPhoneNumberExtension]
,s.[WebsiteUrl]
,s.[EmployeeCountRangeId]
,s.[Revenue]
,s.[Industry]
,s.[Type]
,s.[SICFourDigitCode]
,s.[SICFourDigitName]
,s.[NAICSCode]
,s.[SubsidaryCode]
,s.[SiteLocationTypeId]
,s.[SourceId]
,s.[ParentSiteGuid]
,s.[HQCompanyName]
,s.[HQStreet]
,s.[HQCity]
,s.[HQState]
,s.[HQZipCode]
,s.[HQCountry]
,s.[HQPhone]
,s.[HQWebsite]
,s.[HQEmployeeCount]
,s.[HQAnnualRevenue]
,s.[HQSICCode]
,s.[HQSICName]
,s.[HQNAICSCode]
,s.[HQNAICSName]
,s.[HQSubsidiaryCode]
,s.[HQLocationType]
,s.[MasterRecordId]
,s.[MSCompanyName]
,s.[MSStreet]
,s.[MSCity]
,s.[MSState]
,s.[MSZipCode]
,s.[MSCountry]
,s.[MSPhone]
,s.[MSWebsite]
,s.[MSEmployeeCount]
,s.[MSAnnualRevenue]
,s.[MSSICCode]
,s.[MSSICName]
,s.[MSNAICSCode]
,s.[MSNAICSName]
,s.[MSSubsidiaryCode]
,s.[MSLocationType]
,s.[DHQCompany]
,s.[DHQStreet]
,s.[DHQCity]
,s.[DHQState]
,s.[DHQZipCode]
,s.[DHQCountry]
,s.[DHQPhone]
,s.[DHQWebsite]
,s.[DHQEmployeeCount]
,s.[DHQAnnualRevenue]
,s.[DHQSICCode]
,s.[DHQSICName]
,s.[DHQNAICCode]
,s.[DHQNAICSName]
,s.[DHQSubsidiaryCode]
,s.[DHQLocationType]
,s.[GHQCompanyName]
,s.[GHQStreet]
,s.[GHQCity]
,s.[GHQState]
,s.[GHQZipCode]
,s.[GHQCountry]
,s.[GHQPhone]
,s.[GHQWebsite]
,s.[GHQEmployeeLocation]
,s.[GHQEmployeeTotal]
,s.[GHQAnnualRevenue]
,s.[GHQSICCode]
,s.[GHQSICName]
,s.[GHQNAICCode]
,s.[GHQNAICSName]
,s.[GHQSubsidiaryCode]
,s.[GHQLocationType]
,s.[DSEDSUltimateParent]
,s.[DSEDSUltimateParentName]
,s.[DSEDSCountryISOCode]
,s.[DSEDSLegalForm]
,s.[DSEDomain]
,s.[DSEDemoInstalledProducts]
,s.[DSEDSHierarchyLevel]
,p.[TeleverdeInternalContact]
,s.[TeleverdeInternalAccount]
,p.[SalesforceContactId]
,s.[SalesforceAccountId]
,s.[SalesforceParentAccountId]
,p.[MarketoLeadId]
,p.[MarketoInstanceId]
,s.[ProcessLogId]
,s.[Technologies]
Into mssql1.CampServ.dbo.DemandGenPhase1CF
from dbo.Person p
inner join dbo.site s on s.siteguid = p.siteguid
where exists(select '' from #tmpc t where t.MergedIntoPersonGuid = p.p
ersonguid)
select distinct
c.CB_ID,pp.MergedIntoPersonGuid,pp.SiteGuid,c.FirstName,c.LastName,c.SiteName
into #tmpc
from pending.person pp
inner join tmpContacts- c on c.FirstName = pp.FirstName
and c.LastName = pp.LastName and c.PersonEmailAddress = pp.BusinessEmailAddress
where pp.ProcessLogId = 539
create index #tmpc on tempdb..#tmpc (MergedIntoPersonGuid)
select s.[SiteGuid]
,s.[DunsId]
,p.[PersonGuid]
Into mssql1.CampServ.dbo.DemandGenPhase1CF
from dbo.Person p
inner join dbo.site s on s.siteguid = p.siteguid
where exists(select '' from #tmpc t where t.MergedIntoPersonGuid = p.p
ersonguid)
SELECT [Technologies], replace(replace([Technologies], ',', '>'), ';', '-') as
Technologies
FROM [user].DemandGenFinalForExport
Matching on a web URl domain to the MDR database
Set Rowcount 150
select *
Into DomainMatch_102214
from DomainMatch d
inner join iovmbiproddb.MDR.dbo.site s (nolock)
on D.Domain = s.WebsiteUrl
Where d.Domain in (select s.WebsiteUrl from iovmbiproddb.MDR.dbo.site s (nolock) where
s.WebsiteUrl like '%' + d.Domain + '%')
and s.BillingAddressLine1 IS NOT NULL
Order by d.Domain asc
Set Rowcount 0
Select *
From [user].[Domain_Match102214]
Order by Domain
Set Rowcount 150
select *
Into DomainMatch102214
from DomainMatch d
inner join iovmbiproddb.MDR.dbo.site s (nolock)
on D.Domain = s.WebsiteUrl
Where d.Domain in (select s.WebsiteUrl from iovmbiproddb.MDR.dbo.site s (nolock) where
s.WebsiteUrl like '%' + d.Domain + '%')
and s.BillingAddressLine1 IS NOT NULL and s.HQStreet IS NOT NULL and (s
.HQLocationType
='HQP' or s.HQLocationType='SL')
Order by d.Domain asc
Set Rowcount 0
Select *
From [user].[Domain102214]
Order by Do
