Solved

How to change the value of one row per series in SQL

Posted on 2014-10-29
2
37 Views
Last Modified: 2015-03-18
I get data that looks like this:

SELECT * FROM AA_CURRENT_DERIVED


MHSF_ID LAST_NAME  ADMIT_DATE  NEW_OR_EXISTING
------- ---------- ----------- ---------------
MHSF1   JONES      1           0
MHSF1   JONES      2           0
MHSF1   JONES      3           0
MHSF5   WU         1           0
MHSF5   WU         2           0

There are multiple rows per MHSF_ID value. For each MHSF_ID I want to change the value of NEW_OR_EXISTING to 1 for one of its rows, and keep the rest 0s.

For example,  update the above to this:

MHSF_ID LAST_NAME  ADMIT_DATE  NEW_OR_EXISTING
------- ---------- ----------- ---------------
MHSF1   JONES      1           0
MHSF1   JONES      2           0
MHSF1   JONES      3           1
MHSF5   WU         1           0
MHSF5   WU         2           1

In the example, I'm changing the value for the row with the greatest value of the ADMIT_DATE.
(The real data would have actual datetime values for admit date, not integers like shown here)

The data can be created with this script:

CREATE TABLE [dbo].[AA_CURRENT_DERIVED](
	[MHSF_ID] [char](5) NULL,
	[LAST_NAME] [varchar](50) NULL,
	[ADMIT_DATE] [int] NULL,
	[NEW_OR_EXISTING] [smallint] NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[AA_CURRENT_DERIVED] ([MHSF_ID], [LAST_NAME], [ADMIT_DATE], [NEW_OR_EXISTING]) VALUES (N'MHSF1', N'JONES', 1, 0)
GO
INSERT [dbo].[AA_CURRENT_DERIVED] ([MHSF_ID], [LAST_NAME], [ADMIT_DATE], [NEW_OR_EXISTING]) VALUES (N'MHSF1', N'JONES', 2, 0)
GO
INSERT [dbo].[AA_CURRENT_DERIVED] ([MHSF_ID], [LAST_NAME], [ADMIT_DATE], [NEW_OR_EXISTING]) VALUES (N'MHSF1', N'JONES', 3, 0)
GO
INSERT [dbo].[AA_CURRENT_DERIVED] ([MHSF_ID], [LAST_NAME], [ADMIT_DATE], [NEW_OR_EXISTING]) VALUES (N'MHSF5', N'WU', 1, 0)
GO
INSERT [dbo].[AA_CURRENT_DERIVED] ([MHSF_ID], [LAST_NAME], [ADMIT_DATE], [NEW_OR_EXISTING]) VALUES (N'MHSF5', N'WU', 2, 0)
GO

Open in new window

0
Comment
Question by:cipriano555
2 Comments
 
LVL 15

Expert Comment

by:Haris Djulic
ID: 40411951
If you are going to match them based on the max(admit_date) then you can use this:

select SELECT a.MHSF_ID,a.LAST_NAME,a.ADMIT_DATE, case when a.ADMIT_DATE=b.ADMIT_DATE then 1 else 0 end as NEW_OR_EXISTING  
FROM AA_CURRENT_DERIVED a left join 
(select MHSF_ID, max(admit_date) as admit_Date from AA_CURRENT_DERIVED group by MHSF_ID) b on a.MHSF_ID=b.MHSF_ID

Open in new window


The code was prepared here : http://sqlfiddle.com/#!3/b86a3/2
0
 
LVL 11

Accepted Solution

by:
John_Vidmar earned 500 total points
ID: 40411997
SELECT	a.MHSF_ID
,	a.LAST_NAME
,	a.ADMIT_DATE
,	NEW_OR_EXISTING	=	CASE a.RN WHEN 1 THEN 1 ELSE 0 END
FROM	(	SELECT	*
		,	RN	=	ROW_NUMBER() OVER
					(	PARTITION
						BY	MHSF_ID
						ORDER
						BY	ADMIT_DATE DESC
					) 
		FROM	AA_CURRENT_DERIVED
	) a

Open in new window

0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Server Error Log - logging period 1 33
Update in Sql 7 30
Get Duration of last Status Update 4 31
My Query is not giving correct result. Please help 5 31
There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now