Page Comparison

...

1. Open Microsoft SQL Server Management Studio

2. Connect to and open the BOSS database

3. Right Click on the VIEW directory and select the New View... option

4. Close the Add Table window

5. Copy the following query in to the view:[cce lang="sql"]
   SELECT TOP (100) PERCENT u.Name AS [User], u.ID AS UserID, CT.Name AS ContactType, C.Name, C.Title, X.EmailAddress, X.PhoneNumber, X.Address, u.CreatedDate
   FROM dbo.Contact AS C INNER JOIN
   dbo.ContactType AS CT ON C.ContactTypeID = CT.ID INNER JOIN
   dbo.UserContactConnector AS ucc ON ucc.ContactID = C.ID INNER JOIN
   dbo.[User] AS u ON u.ID = ucc.UserID LEFT OUTER JOIN
   dbo.UserExt ON u.ID = dbo.UserExt.UserID LEFT OUTER JOIN
   (SELECT CP.ContactID, MIN(CASE CPT.Name WHEN 'Email Address' THEN CP.Value END) AS EmailAddress, MIN(CASE CPT.Name WHEN 'Home Phone' THEN CP.Value END) AS PhoneNumber,
   MIN(CASE CPT.Name WHEN 'Address' THEN CP.Value END) AS Address
   FROM dbo.ContactPoint AS CP INNER JOIN
   dbo.ContactPointType AS CPT ON CPT.ID = CP.ContactPointTypeID
   GROUP BY CP.ContactID) AS X ON C.ID = X.ContactID
   ORDER BY [user]
   [/cce]

   Code Block

   SELECT TOP (100) PERCENT u.Name AS [USER], u.ID AS UserID, CT.Name AS ContactType, C.Name, C.Title, X.EmailAddress, X.PhoneNumber, X.Address, u.CreatedDate FROM dbo.Contact AS C INNER JOIN dbo.ContactType AS CT ON C.ContactTypeID = CT.ID INNER JOIN dbo.UserContactConnector AS ucc ON ucc.ContactID = C.ID INNER JOIN dbo.[USER] AS u ON u.ID = ucc.UserID LEFT OUTER JOIN dbo.UserExt ON u.ID = dbo.UserExt.UserID LEFT OUTER JOIN (SELECT CP.ContactID, MIN(CASE CPT.Name WHEN 'Email Address' THEN CP.Value END) AS EmailAddress, MIN(CASE CPT.Name WHEN 'Home Phone' THEN CP.Value END) AS PhoneNumber, MIN(CASE CPT.Name WHEN 'Address' THEN CP.Value END) AS Address FROM dbo.ContactPoint AS CP INNER JOIN dbo.ContactPointType AS CPT ON CPT.ID = CP.ContactPointTypeID GROUP BY CP.ContactID) AS X ON C.ID = X.ContactID ORDER BY [USER]

6. Click the Execute SQL button [ ! ] or [CTRL]+R, to run the query

7. Save the View (no special characters or spaces, under scores can be used in place of spaces). Remember the name of the view as you will need to enter this value in EngageIP Billing

...