Versions Compared

Old Version 5

changes.mady.by.user admin

Saved on

compared with

New Version Current

changes.mady.by.user admin

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

MBV Display Name: EventLocation
View: EventLocation
Fields:

 
Code Block
languagesql
CREATE VIEW [dbo].vwEventLocations
AS
SELECT 
   m.ID  'ID',
   m.Place  'EventLocationName', 
   a.Line1  'EventLocationAddress1', 
   a.Line2  'EventLocationAddress2', 
   a.Line3  'EventLocationAddress3', 
   a.City  'EventCity', 
   a.StateProvince  'EventState', 
   a.PostalCode  'PostalCode', 
   m1.MeetingID  'MeetingID', 
   a.Country  'Country',
        Case when 
              (SELECT CASE WHEN ParentMeeting.ID IS NULL
                THEN 0
                ELSE ParentMeeting.ID
        END AS ParentMeetingID
       FROM Meeting ParentMeeting
       INNER JOIN Product ON ParentMeeting.ProductID = Product.ParentID
       WHERE Product.ID = m.ProductID
       )  is null then m.id else
       (SELECT TOP 1 ParentMeeting.ID
       FROM Meeting ParentMeeting
       INNER JOIN Product ON ParentMeeting.ProductID = Product.ParentID
       WHERE Product.ID = m.ProductID) end as 
       'ParentMeetingID',
       m.VenueID 'LocationID'
FROM 
   Meeting m
   LEFT OUTER JOIN 
       Address    a 
       ON 
            m.AddressID=a.ID
   LEFT OUTER JOIN 
       vwMeetings    m1 
       ON 
            m.ID=m1.ID 

Anchor
_rooms
_rooms
Event Rooms

MBV Display Name: Room
View: EventRooms
Fields:

 
Code Block
languagesql
CREATE VIEW [dbo].vwRooms
AS
SELECT 
   m.ID  'ID',
   v.Name  'EventLocationName', 
   mr.Name  'RoomName', 
   mrt.Name  'RoomType', 
   v.Floor  'RoomFloor',
   m.ID  'MeetingID', 
       m.MeetingRoomID 'RoomID',
   Case when 
              (SELECT CASE WHEN ParentMeeting.ID IS NULL
                THEN 0
                ELSE ParentMeeting.ID
        END AS ParentMeetingID
       FROM Meeting ParentMeeting 
       INNER JOIN Product ON ParentMeeting.ProductID = Product.ParentID
       WHERE Product.ID = m.ProductID
       )  is null then m.id else
       (SELECT TOP 1 ParentMeeting.ID
       FROM Meeting ParentMeeting
       INNER JOIN Product ON ParentMeeting.ProductID = Product.ParentID
       WHERE Product.ID = m.ProductID) end as 
       'ParentMeetingID',
       m.VenueID 'LocationID'     
FROM 
   Meeting m
   INNER JOIN 
       MeetingRoom    mr 
       ON 
            m.MeetingRoomID=mr.ID
   LEFT OUTER JOIN  
       MeetingRoomType    mrt 
       ON 
            m.MeetingRoomTypeID=mrt.ID
   INNER JOIN 
       Venue    v 
       ON 
            m.VenueID=v.ID 

Anchor
_agenda
_agenda
Event Agenda

MBV Display Name: EventSessions
View: EventSessions
Fields:

 
Code Block
languagesql
CREATE VIEW [dbo].vwAgendas
AS
SELECT 
   m.ID  'ID',
   m.StartDate  'Date',
   m.MeetingTitle  'AgendaTitle', 
   m.verboseDescription 'Description',
   m1.MeetingID  'MeetingID', 
   m1.ParentMeetingID  'ParentMeetingID',
   STUFF( right( convert( varchar(26), m.StartDate, 109 ), 15 ), 7, 7, ' ' ) 'StartTime',
   STUFF( right( convert( varchar(26), m.EndDate, 109 ), 15 ), 7, 7, ' ' ) 'EndTime'

FROM 
   Meeting m
   INNER JOIN 

       Product    p 
       ON 
            m.ProductID=p.ID
   LEFT OUTER JOIN 
       vwMeetings    m1 
       ON 
            m.ID=m1.ID 

Anchor
_sponsors
_sponsors
Event Sponsors

MBV Display Name: Sponsors
View: EventSponsors
Fields:

 
Code Block
languagesql
CREATE VIEW vwSponsors AS
SELECT 
   m.ID  'ID', 
   c.Name  'SponsorName', 
   ms.Comments  'Description', 
   c.Name  'Sponsor', 
   p.NameWCompany  'ContactName', 
   m1.MeetingID  'MeetingID', 
   p.Title  'ContactTitle', 
   c.CompanyType  'Type', 
      CASE WHEN c.MainAreaCode is null then c.MainPhone else '(' + RTRIM(c.MainAreaCode) +')'+ c.MainPhone end  'Phone', 
   c.MainEmail  'Email', 
   c.WebSite  'Website', 
   c.AddressLine1   'Address1', 
   c.AddressLine2  'Address2', 
   c.AddressLine3  'Address3', 
   c.City  'City', 
   c.State  'State', 
   c.ZipCode   'PostalCode', 
   ms.SponsorID  'SponsorsID',
       c.Country 'Country',
       Case when 
       (SELECT CASE WHEN ParentMeeting.ID IS NULL
       THEN 0
       ELSE ParentMeeting.ID
        END AS ParentMeetingID
       FROM Meeting ParentMeeting
       INNER JOIN Product ON ParentMeeting.ProductID = Product.ParentID
       WHERE Product.ID = m.ProductID
       )  is null then m.id else
       (SELECT TOP 1 ParentMeeting.ID
       FROM Meeting ParentMeeting
       INNER JOIN Product ON ParentMeeting.ProductID = Product.ParentID
       WHERE Product.ID = m.ProductID) end as 
       'ParentMeetingID'
FROM 
   Meeting m
   LEFT OUTER JOIN 
       MeetingSponsor    ms 
       ON 
            m.ID=ms.MeetingID
   LEFT OUTER JOIN 
       vwMeetings    m1 
       ON 
            m.ID=m1.ID
              LEFT OUTER JOIN 
       vwCompanies    c 
       ON 
            ms.SponsorID=c.ID
                     INNER JOIN 
       Person    p 
       ON 
            ms.ContactID=p.ID 

Anchor
_attendees
_attendees
Event Attendees

MBV Display Name: Attendees
View: EventAttendees
Fields:

 
Code Block
languagesql
CREATE VIEW [dbo].vwAttendees
AS
SELECT  
      p.ID 'PersonID',
      p.FirstName,
      p.LastName,
      p.Prefix,
      p.MiddleName,
          p.Suffix,
          p.Email,
      p.MemberType 'Type',
      p.Title,
      p.FirstLast ,
      p.Email1, 
      p.AddressLine1,
      p.AddressLine2,
      p.City,
      p.State,
      p.ZipCode 'PostalCode',
          CASE WHEN p.phoneAreaCode is null then p.Phone else '(' + RTRIM(p.phoneAreaCode) +')'+ p.Phone end  'Phone', 
      p.Company,
      od.ProductID 'ProductMeetingID',
      p.ID ,
      m.ID 'MeetingID',
      p.Country 'Country'
           
              FROM vwPersons p
              LEFT JOIN vwOrderMeetDetail omd ON  p.ID=omd.AttendeeID
         LEFT JOIN OrderDetail od ON (omd.OrderID = od.OrderID AND omd.Sequence=od.Sequence)
        
         INNER JOIN Meeting m on m.ProductID = od.ProductID
         
            WHERE od.Quantity > 0 AND  ((omd.StatusID = (SELECT ID FROM AttendeeStatus WHERE Name = 'Attended')) OR (omd.StatusID = (SELECT ID FROM AttendeeStatus WHERE Name = 'Registered')))

Anchor
_exhibit
_exhibit
Event Exhibitors

MBV Display Name: Exhibitor
View: EventExhibitors
Fields:

Code Block
languagesql
CREATE VIEW [dbo].vwExhibitors
AS 
 
(SELECT 
c.Name 'ExhibitorName',
c.Name 'Exhibitor',
c.WebSite 'WebSite',
ct.Name 'Type',
a.Line1 'Address1' ,
a.Line2 'Address2', 
a.Line3 'Address3' , 
a.City ,
a.StateProvince 'State' ,
a.PostalCode 'PostalCode', 
 p.ID 'PersonID',
 p.FirstLast 'ContactName',
 p.Email 'ContactEmail', 
 p.Title 'ContactTitle',  
CASE WHEN p.phoneAreaCode is null then p.Phone else '(' + RTRIM(p.phoneAreaCode) +')'+ p.Phone end  'ContactPhone',
 e.ProductID 'ProductMeetingID',
 c.ID,
 m.ID 'MeetingID',
 obd.BoothID 'BoothID'
 
 FROM OrderDetail od 
 INNER JOIN OrderBoothdetail obd ON (obd.OrderID = od.OrderID)
 INNER JOIN vwPersons p ON p.ID = obd.ContactID
 INNER JOIN Company c ON c.ID = obd.ExhibitorID 
 INNER JOIN CompanyType ct ON ct.ID = c.CompanyTypeID 
 LEFT JOIN Address a ON a.ID = c.AddressID 
 LEFT JOIN Expo e on e.ProductID = od.ProductID
 INNER JOIN Meeting m ON e.ID =  m.LinkedExpoID
 WHERE  od.Quantity > 0 AND obd.BoothID is not null )
 
 Union
 (
 SELECT 
c.Name 'ExhibitorName',
c.Name 'Exhibitor',
c.WebSite 'WebSite'
ct.Name 'Type',
a.Line1 'Address1' ,
a.Line2 'Address2', 
a.Line3 'Address3' , 
a.City ,
a.StateProvince 'State' ,
a.PostalCode 'PostalCode', 
 p.ID 'PersonID',
 p.FirstLast 'ContactName',
 p.Email 'ContactEmail', 
 p.Title 'ContactTitle',  
 CASE WHEN p.phoneAreaCode is null then p.Phone else '(' + RTRIM(p.phoneAreaCode) +')'+ p.Phone end  'ContactPhone',
 e.ProductID 'ProductMeetingID',
 c.ID,
 m.ID 'MeetingID',
 obd.BoothID 'BoothID'
 
 FROM vwPersons p
 LEFT JOIN vwOrderMeetDetail omd ON  p.ID=omd.AttendeeID
 LEFT JOIN OrderDetail od ON (omd.OrderID = od.OrderID AND omd.Sequence=od.Sequence)
 INNER JOIN OrderBoothdetail obd ON (obd.OrderID = od.OrderID AND p.ID = obd.ContactID)
 INNER JOIN Company c ON c.ID = obd.ExhibitorID 
 INNER JOIN CompanyType ct ON ct.ID = c.CompanyTypeID 
 LEFT JOIN Address a ON a.ID = c.AddressID 
LEFT JOIN Expo e on e.ProductID = od.ProductID
 INNER JOIN Meeting m ON m.ProductID = od.ProductID 
 WHERE od.Quantity > 0 AND  omd.StatusID = (SELECT ID FROM AttendeeStatus WHERE Name='Attended') ) 

Anchor
_speakers
_speakers
Event Speakers

MBV Display Name: Speakers
View: EventSpeakers
Fields:

 
Code Block
languagesql
CREATE VIEW [dbo].vwSpeakers
AS
SELECT 
   m.ID  'ID', 
   ms.Type  'Type', 
   a.Line1  'Address', 
   Case when 
       (SELECT CASE WHEN ParentMeeting.ID IS NULL
       THEN 0
       ELSE ParentMeeting.ID
        END AS ParentMeetingID
       FROM Meeting ParentMeeting
       INNER JOIN Product ON ParentMeeting.ProductID = Product.ParentID
       WHERE Product.ID = m.ProductID
       )  is null then m.id else
       (SELECT TOP 1 ParentMeeting.ID
       FROM Meeting ParentMeeting
       INNER JOIN Product ON ParentMeeting.ProductID = Product.ParentID
       WHERE Product.ID = m.ProductID) end as 
       'ParentMeetingID', 
   ms.MeetingID  'MeetingID', 
   p.FirstName  'FirstName', 
   p.LastName  'LastName', 
   p.Prefix  'Prefix', 
   p.MiddleName   'MiddleName', 
   p.Suffix  'Suffix', 
   p.WebSite  'Website', 
       CASE WHEN p.PhoneAreaCode is null then p.phone else '(' + RTRIM(p.PhoneAreaCode) +')'+  p.phone end  'Phone',  
   p.Email   'EMail', 
   p.Title  'JobTitle', 
   p.CompanyName   'Employer', 
   ms.SpeakerID  'SpeakerID'
FROM 
   Meeting m
   INNER JOIN 
       MeetingSpeaker    ms 
       ON 
            m.ID=ms.MeetingID
   LEFT OUTER JOIN 
       Address    a 
       ON 
            m.AddressID=a.ID
   INNER JOIN 
       vwMeetings    m1 
       ON 
            m.ID=m1.ID
                       LEFT OUTER JOIN 
       vwPersons    p 
       ON 
            ms.SpeakerID=p.ID 

Anchor
_sessions
_sessions
Event Sessions

MBV Display Name: Sessions
View: EventSessions
Fields:

 
Code Block
languagesql
CREATE VIEW [dbo].vwEventSessions AS
 
SELECT 
   m.ID  'ID',
   m.MeetingTitle  'SessionTitle',
   m.StartDate  'Date', 
   mt.Name  'Type', 
   mt1.Name  'Track', 
   m.verboseDescription  'Agenda',
   m.MeetingTitle  'Event',
   --m.Place  'LocationName', 
       v.Name  'LocationName',
   mr.Name  'RoomName', 
   m.verboseDescription  'Description', 
   --ms.Description  'Session', 
   --ms.Speaker  'Presenter2', 
   --ms.Speaker  'Presenter3', 
       --ms1.Presenter1 'Presenter1',
       --ms2.Presenter2 'Presenter2',
       --ms3.Presenter3 'Presenter3',
       --ms4.Presenter4 'Presenter4',
       --ms5.Presenter5 'Presenter5',
       ms1.SpeakerID 'Presenter1',
       ms2.SpeakerID 'Presenter2',
       ms3.SpeakerID 'Presenter3',
       ms4.SpeakerID 'Presenter4',
       ms5.SpeakerID 'Presenter5', 
   m1.MeetingID  'MeetingID', 
       m1.ParentMeetingID 'ParentMeetingID',
   --Case when 
   --m1.ParentMeetingID is null then m.id else
       --m1.ParentMeetingID end as  'ParentMeetingID',
       Ltrim(Rtrim(STUFF( right( convert( varchar(26), m.StartDate, 109 ), 15 ), 7, 7, ' ' ))) 'StartTime',
       Ltrim(Rtrim(STUFF( right( convert( varchar(26), m.EndDate, 109 ), 15 ), 7, 7, ' ' ))) 'EndTime',
       m.VenueID 'LocationID',
       m.MeetingRoomID 'RoomID'
FROM 
   Meeting m
   LEFT OUTER JOIN 
       MeetingType    mt 
       ON 
            m.MeetingTypeID=mt.ID
   LEFT OUTER JOIN 
       MeetingTrack    mt1 
       ON 
            m.ID=mt1.MeetingID
   LEFT OUTER JOIN 
       vwMeetingSessions    ms 
       ON 
            m.ID=ms.MeetingID
       Left JOIN Venue v 
       ON m.VenueID = v.ID
   LEFT OUTER JOIN 
       MeetingRoom    mr 
       ON 
            m.MeetingRoomID=mr.ID
       INNER JOIN Product p   ON m.ProductID=p.ID      --Added to show all sessions details
   
   LEFT OUTER JOIN 
       vwMeetings    m1 
       ON 
            m.ID=m1.ID
                     FULL OUTER JOIN
       (SELECT ms.MeetingID, p.FirstName +' ' +p.Lastname 'Presenter1' ,ms.SpeakerID
              FROM MeetingSpeaker ms 
              INNER JOIN Person p ON p.ID = ms.SpeakerID
              INNER JOIN Meeting m ON m.ID = ms.MeetingID
              WHERE ms.Sequence = 1 
              
              
       ) as ms1 ON ms1.MeetingID = m.ID
       FULL OUTER JOIN 
 (
       SELECT  ms.MeetingID, p.FirstName +' ' +p.Lastname 'Presenter2' ,ms.SpeakerID
    FROM MeetingSpeaker ms 
    INNER JOIN Person p ON p.ID = ms.SpeakerID
    INNER JOIN Meeting m ON m.ID = ms.MeetingID
     WHERE ms.Sequence = 2
 ) as ms2  ON ms1.MeetingID = ms2.MeetingID 
 
 FULL OUTER JOIN 
 (
       SELECT  ms.MeetingID, p.FirstName +' ' +p.Lastname 'Presenter3' ,ms.SpeakerID
    FROM MeetingSpeaker ms 
    INNER JOIN Person p ON p.ID = ms.SpeakerID
    INNER JOIN Meeting m ON m.ID = ms.MeetingID
    WHERE ms.Sequence = 3 
     
 ) as ms3  ON ms2.MeetingID = ms3.MeetingID 
 FULL OUTER JOIN 
 (
       SELECT  ms.MeetingID, p.FirstName +' ' +p.Lastname 'Presenter4' ,ms.SpeakerID
    FROM MeetingSpeaker ms 
    INNER JOIN Person p ON p.ID = ms.SpeakerID
    INNER JOIN Meeting m ON m.ID = ms.MeetingID
    WHERE ms.Sequence = 4
   
 ) as ms4  ON ms3.MeetingID = ms4.MeetingID 
 FULL OUTER JOIN 
 (
       SELECT  ms.MeetingID, p.FirstName +' ' +p.Lastname 'Presenter5' ,ms.SpeakerID
    FROM MeetingSpeaker ms 
    INNER JOIN Person p ON p.ID = ms.SpeakerID
    INNER JOIN Meeting m ON m.ID = ms.MeetingID
    WHERE ms.Sequence = 5
     
 ) as ms5  ON ms4.MeetingID = ms5.MeetingID 

Anchor
_genInfo
_genInfo
Event General Information

MBV Display Name: GeneralInfo
View: EventGeneralInfo
Fields:

 
Code Block
languagesql
CREATE VIEW [dbo].vwGeneralInfos
AS
 
SELECT 
   m.ID  'ID',
   m.MeetingTitle  'Title', 
   p.ProductCategory  'Category', 
   p.Description  'Description', 
   a.Line1  'Address1', 
   a.Line2  'Address2', 
   a.Line3  'Address3', 
   a.City  'City', 
   a.StateProvince  'State', 
   a.PostalCode  'ZipCode', 
   CASE WHEN pn.AreaCode is null then pn.Phone else '(' + RTRIM(pn.AreaCode) +')'+ pn.Phone end  'Phone', 
   p.WebProductPage  'Website', 
   m1.MeetingID  'MeetingID',
       m.ProductID 'MeetingProductID'
FROM 
   Meeting m
   INNER JOIN 
       vwProducts    p 
       ON 
            m.ProductID=p.ID
   LEFT OUTER JOIN 
       Address    a 
       ON 
            m.AddressID=a.ID
   LEFT OUTER JOIN 
       PhoneNumber    pn 
       ON 
            m.PhoneID=pn.ID
   LEFT OUTER JOIN 
       vwMeetings    m1 
       ON 
            m.ID=m1.ID 

Anchor
_tracks
_tracks
Event Tracks

MBV Display Name: Track
View: EventTracks
Fields:

Code Block
languagesql
CREATE VIEW [dbo].vwTracks
AS
SELECT 
   m.ID  'ID', 
   mt.Name  'TrackName', 
   mt.Description  'Description',
       mt.ID 'TrackID'
FROM 
   Meeting m
   LEFT OUTER JOIN 
      MeetingTrack    mt 
      ON 
            m.ID=mt.MeetingID