Solved

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

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

777 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