SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[OFF_NAR](
	[OFFENSENO] [varchar](15) NULL,
	[DATE] [datetime] NULL,
	[TIME] [datetime] NULL,
	[TYPE] [varchar](1) NULL,
	[NARRATIVE] [text] NULL,
	[OFFENSENOB] [varchar](15) NULL,
	[REPTAKER] [varchar](15) NULL,
	[REPTAKERPERNO] [varchar](15) NULL,
	[EDITDATE] [datetime] NULL,
	[EDITTIME] [datetime] NULL,
	[OFFREPPERNO] [varchar](15) NULL,
	[OFFREPNAME] [varchar](40) NULL,
	[OFFREPNO] [varchar](5) NULL,
	[FINISHED] [bit] NULL,
	[APPROVED] [bit] NULL,
	[SUPPERNO] [varchar](15) NULL,
	[SUPNAME] [varchar](40) NULL,
	[SUPDATE] [datetime] NULL,
	[SUPTIME] [varchar](5) NULL,
	[UNIQUEKEY] [varchar](22) NOT NULL,
	[OffenseReportUniqueFKey] [varchar](22) NULL,
	[NarrativeDateTime] [datetime] NULL,
	[LastModifiedDateTime] [datetime] NULL,
 CONSTRAINT [PK_OFF_NAR_UNIQUEKEY] PRIMARY KEY CLUSTERED 
(
	[UNIQUEKEY] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

;WITH cte AS
(
	SELECT  [TYPE],
		ROW_NUMBER() OVER (PARTITION BY [OFFENSENO] ORDER BY [NarrativeDateTime]) RN
	FROM [dbo].[OFF_NAR]
)
UPDATE cte SET
	[Type] = CASE WHEN RN = 1 THEN 'I' ELSE 'S' END;