Set value based on date - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Set value based on date

Author

Topic

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-07-09 : 12:17:40

I have a table in which i have three relevant columns. Offenseno, Type, and narrativedatetime.

I will have multiple duplicate Offensno values in this table. My task is to set the value of "Type" to "I" on the offenseno with the earliest narrativedatetime and set all other "Type" values for that same group of offenseno's to "S".

obviously, i'm here to get guidance on "how".



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

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-09 : 12:28:10

[code];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;[/code]

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-07-09 : 20:36:52

Thank you! worked perfectly.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources