speakinglog-tablesviewsetup.sql

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SpeakingLogs](
    [LogId] [int] IDENTITY(1,1) NOT NULL,
    [TalkDate] [date] NOT NULL,
    [EventName] [varchar](255) NOT NULL,
    [City] [varchar](255) NULL,
    [Country] [varchar](255) NULL,
    [Lat] [FLOAT] NULL,
    [Long] [FLOAT] NULL,
    [Attendance] [int] NULL,
    [Tags] [nvarchar](1024) NULL,
    [TalkType] [varchar](100) NULL,
 CONSTRAINT [PK_SpeakingLogs] PRIMARY KEY CLUSTERED 
(
    [LogId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[RecordCountByTags]
AS

SELECT 
    LTRIM(RTRIM(VALUE)) AS Tag,
    COUNT(*) AS RecordCount
FROM 
    [dbo].[SpeakingLogs]
    CROSS APPLY STRING_SPLIT([Tags],',')
GROUP BY
    VALUE

GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[EventLogos](
    [EventName] [varchar](255) NOT NULL,
    [ImageURL] [nvarchar](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER DATABASE [SpeakingLog] SET  READ_WRITE 
GO