Solved

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

Posted on 2014-10-29
2
34 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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

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 …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

762 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

24 Experts available now in Live!

Get 1:1 Help Now