UFN_CLIENTSITES_GETEVENTWEBSITESTATUS

create function dbo.UFN_CLIENTSITES_GETEVENTWEBSITESTATUS
(
  @EVENTID uniqueidentifier
)
returns bit
with execute as caller
as begin

  declare @PUBLISHEDDATE datetime,
          @UNPUBLISHEDDATE datetime,
          @ObjectGuid uniqueidentifier,
          @PUBLISHED bit

  select @ObjectGuid=ClientSites.Guid from dbo.EVENTEXTENSION 
  join dbo.ClientSites on EVENTEXTENSION.CLIENTSITESID = ClientSites.ID
  where EVENTID = @EVENTID

    SELECT TOP 1 @PUBLISHEDDATE = AuditDate FROM dbo.Audits WHERE ObjectGuid = @ObjectGuid AND AuditCodesID = 7 ORDER BY AuditDate DESC
  SELECT TOP 1 @UNPUBLISHEDDATE = AuditDate FROM dbo.Audits WHERE ObjectGuid = @ObjectGuid AND AuditCodesID = 8 ORDER BY AuditDate DESC

  if @PUBLISHEDDATE is not null
  begin
      if @UNPUBLISHEDDATE is null
          set @PUBLISHED = 1
      else
          begin
              if @PUBLISHEDDATE > @UNPUBLISHEDDATE
                  set @PUBLISHED = 1
              else 
                  set @PUBLISHED = 0
              end    
  end
  else
      set @PUBLISHED = 0  

    return @PUBLISHED
end