I'm trying to get SQL join data from an Order to both the SettlementAgent and SellingAgentBroker data. None of the key relationships in SQL seem to be mapping a clear path to this data. Here's a list of the Field Code Browser equivalents of what I need for each:
SettlementAgent:
Order.SettlementAgents.Name
Order.SettlementAgents.Address.Address1
Order.SettlementAgents.Address.Address2
Order.SettlementAgents.Address.City
Order.SettlementAgents.Address.State
Order.SettlementAgents.Address.Zip
Order.SettlementAgents.LicenseNumber
Order.SettlementAgents.MainPerson.FirstName
Order.SettlementAgents.MainPerson.LastName
Order.SettlementAgents.MainPerson.LicenseNumber
SellingAgentBroker:
Order.SellingAgentBrokers.Name
Order.SellingAgentBrokers.Address.Address1
Order.SellingAgentBrokers.Address.Address2
Order.SellingAgentBrokers.Address.City
Order.SellingAgentBrokers.Address.State
Order.SellingAgentBrokers.Address.Zip
Order.SellingAgentBrokers.LicenseNumber
Order.SellingAgentBrokers.MainPerson.FirstName
Order.SellingAgentBrokers.MainPerson.LastName
Order.SellingAgentBrokers.MainPerson.LicenseNumber
The fields are the same for each path, so I'm hoping that the difference between these queries is just swapping out a couple intermediate tables from one query to the other. Thanks!
Settlement Agent/SellingAgentBrokers
Moderator: Phil Barton
-
- Posts: 82
- Joined: Tue Jan 21, 2014 11:35 am
- Location: Austin, TX
-
- Posts: 84
- Joined: Thu Sep 11, 2008 1:57 pm
Re: Settlement Agent/SellingAgentBrokers
This gets the data based on the contact types.
If you are needing the settlement agent and selling agent that is selected on Page 5 of the CDF, use the pfm.PointOfContact table
Hope this helps!
Code: Select all
SELECT
Name,
C.Address#Address1 AS Address1,
C.Address#Address2 AS Address2,
C.Address#City AS City,
C.Address#StateID,
zS.Code AS [State],
C.Address#Zip,
C.LicenseNumber,
P.FirstName AS MainPersonFirstName,
P.LastName AS MainPersonLastName,
P.LicenseNumber AS MainPersonLastNumber
FROM pfm.Contact AS C
LEFT OUTER JOIN zref.[State] AS zS ON C.Address#StateID=zS.ID
LEFT OUTER JOIN pfm.Person AS P ON C.RootId#=P.RootId# AND P.Id# BETWEEN C.Id# AND C.LastID# AND P.IsMainPerson=1
WHERE
C.ContactType=22 OR C.ContactType=21 --22=Settlement Agent, 21=Selling Agent
Code: Select all
SELECT
Name,
C.Address#Address1 AS Address1,
C.Address#Address2 AS Address2,
C.Address#City AS City,
C.Address#StateID,
zS.Code AS [State],
C.Address#Zip,
C.LicenseNumber,
P.FirstName AS MainPersonFirstName,
P.LastName AS MainPersonLastName,
P.LicenseNumber AS MainPersonLastNumber
FROM
(SELECT
PC.RootId#,
PC.Id#,
PC.ContactId$,
PC.PersonId$,
PC.Position, --1=Buyer's Agent, 2=Seller's Agent, 3=Settlement Agent,
CDF.Id# AS CDF_Id#
FROM
pfm.PointOfContact AS PC WITH (NOLOCK)
INNER JOIN pfm.CDF AS CDF WITH (NOLOCK) ON PC.RootId#=CDF.RootId# AND PC.Id# BETWEEN CDF.Id# AND CDF.LastId#
WHERE
PC.Type#=(SELECT ID FROM pf.OrderItemType WHERE Name='CDFPointOfContact')
AND PC.Position IN (2, 3)) AS POC
INNER JOIN pfm.Contact AS C ON POC.RootId#=C.RootId# AND POC.ContactId$=C.Id#
LEFT OUTER JOIN pfm.Person AS P ON POC.RootId#=P.RootId# AND POC.PersonId$=P.Id#
LEFT OUTER JOIN zref.[State] AS zS ON C.Address#StateID=zS.ID
Lisa Ennis
Senior Report Developer
SoftPro
Senior Report Developer
SoftPro
-
- Posts: 82
- Joined: Tue Jan 21, 2014 11:35 am
- Location: Austin, TX
Re: Settlement Agent/SellingAgentBrokers
Thanks...so far, so good. One further question, is this the proper way to tie it back to the Order? Doing a straight join on the Id# fields does not seem to work properly.
Code: Select all
where contact.RootId# = [Order].RootId#
and (contact.Id# between [Order].Id# and [Order].LastId#)
and contact.ContactType = 22 -- Settlement Agent
-
- Posts: 84
- Joined: Thu Sep 11, 2008 1:57 pm
Re: Settlement Agent/SellingAgentBrokers
The field RootId# that exists in most of the pfm schema tables is the order id. As you traverse down through the tables, you also need the between joins of Id# and LastId# from the table above it in hierarchy.
Lisa Ennis
Senior Report Developer
SoftPro
Senior Report Developer
SoftPro