update help - 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

SQL Server 2008 Forums

Transact-SQL (2008)

update help

Author

Topic

learntsql

524 Posts

Posted - 2011-07-08 : 06:34:46

Hi All,
I have a table with ID,Person,date and status columns

sample data like

ID - Person - date - status
1 - p1 - d1 - Y
2 - P2 - d1 - N
3 - p3 - d1 - UK
4 - P4 - d1 - N
5 - p1 - d2 - Y
6 - P2 - d2 - N
7 - p3 - d2 - Y
8 - P4 - d2 - UK

Here I have to update the current record status column value from "UK" to "N"
when previous record status and current record status are "N"
and current record value is "UK".

for this should i use looping concept are any other ways.
Plz. guide me
TIA.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-07-08 : 06:37:12

So "p4" is the only person that is eligble to change his status in your example?

N 56°04'39.26"
E 12°55'05.63"

learntsql

524 Posts

Posted - 2011-07-08 : 06:42:23

quote:
Originally posted by learntsql

Hi All,
I have a table with ID,Person,date and status columns

sample data like

ID - Person - date - status
1 - p1 - d1 - Y
2 - P2 - d1 - N
3 - p3 - d1 - UK
4 - P4 - d1 - N
5 - p1 - d2 - Y
6 - P2 - d2 - N
7 - p3 - d2 - Y
8 - P4 - d2 - UK

Here I have to update the current record status column value from "UK" to "N"
when previous record status and Next record status are "N"
and current record value is "UK".

for this should i use looping concept are any other ways.
Plz. guide me
TIA.

sorry small change
Here I have to update the current record status column value from "UK" to "N"
when previous record status and Next record status are "N"
and current record value is "UK".

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-07-08 : 06:44:27

[code]DECLARE @Sample TABLE
(
ID INT,
Person VARCHAR(10),
date VARCHAR(10),
status VARCHAR(10)
)

INSERT @Sample
VALUES (1, 'p1', 'd1', 'Y'),
(2, 'P2', 'd1', 'N'),
(3, 'p3', 'd1', 'UK'),
(4, 'P4', 'd1', 'N'),
(5, 'p1', 'd2', 'Y'),
(6, 'P2', 'd2', 'N'),
(7, 'p3', 'd2', 'Y'),
(8, 'P4', 'd2', 'UK')

SELECT * FROM @Sample

UPDATE s
SET s.status = 'N'
FROM @Sample AS s
INNER JOIN (
SELECT Person,
MAX(date) AS date
FROM (
SELECT Person,
Status,
date,
ROW_NUMBER() OVER (PARTITION BY Person ORDER BY date DESC) AS SeqID
FROM @Sample
) AS d
WHERE SeqID BETWEEN 1 AND 2
AND (SeqID = 1 AND Status = 'UK' OR SeqID = 2 AND Status = 'N')
GROUP BY Person
HAVING COUNT(*) = 2
) AS d ON d.Person = s.Person
AND d.date = s.date

SELECT * FROM @Sample[/code]

N 56°04'39.26"
E 12°55'05.63"

learntsql

524 Posts

Posted - 2011-07-08 : 06:52:18

Thanks for ur reply SwePeso,

I am sorry Here I have to update ID value 3 record.
because for this record prev. is "N" and next is "N"
and curr. is "UK".
TIA.

learntsql

524 Posts

Posted - 2011-07-08 : 06:55:14

quote:
Originally posted by learntsql

Thanks for ur reply SwePeso,

I am sorry Here I have to update ID value 3 record.
because for this record prev. is "N" and next is "N"
and curr. is "UK".

and here persons are repeated daily...
I have taken only 2 records...
and same person will not be duplicated for same date.

TIA.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-07-08 : 07:29:51

So what is your final and expected output based on this sample data?
Also explain the logic why.

DECLARE	@Sample TABLE
	(
		ID INT,
		Person VARCHAR(10),
		date VARCHAR(10),
		status VARCHAR(10)
	)

INSERT	@Sample
VALUES	(1, 'p1', 'd1', 'Y'),
	(2, 'P2', 'd1', 'N'),
	(3, 'p3', 'd1', 'UK'),
	(4, 'P4', 'd1', 'N'),
	(5, 'p1', 'd2', 'Y'),
	(6, 'P2', 'd2', 'N'),
	(7, 'p3', 'd2', 'Y'),
	(8, 'P4', 'd2', 'UK')

N 56°04'39.26"
E 12°55'05.63"

learntsql

524 Posts

Posted - 2011-07-09 : 01:09:50

Sorry let me explain bit more clear...
Here UK Means "Unknown"
when prev and next status vaues are "N" and current one has "UK" then update it to "N" i.e "NO".

I hope am clear..
PLz. guide me.
TIA.

Sachin.Nand

2937 Posts

Posted - 2011-07-09 : 10:55:49

This??



DECLARE	@Sample TABLE
	(
		ID INT,
		Person VARCHAR(10),
		date VARCHAR(10),
		status VARCHAR(10)
	)

INSERT	@Sample
VALUES	(1, 'p1', 'd1', 'Y'),
	(2, 'P2', 'd1', 'N'),
	(3, 'p3', 'd1', 'UK'),
	(4, 'P4', 'd1', 'N'),
	(5, 'p1', 'd2', 'Y'),
	(6, 'P2', 'd2', 'N'),
	(7, 'p3', 'd2', 'UK'),
	(8, 'P4', 'd2', 'J')


declare @st varchar(10)=''

;with cte
as
(
select * from @Sample S
outer apply(select top 1  status S from @Sample S1 where S1.status='N' and S.ID=S1.ID-1)T
)

update S set 
@st=S.status=case when @st='N' and S.status='UK' and S='N' then 'N' else S.status end,@st=S.status from cte S

select * from @Sample

The above code will only work if your id's increment in a serial format.If not then you have make sure you have them by using row_number function or something like that.

PBUH

learntsql

524 Posts

Posted - 2011-07-11 : 03:11:29

Thanks a lot ,
this is what i wanted.

learntsql

524 Posts

Posted - 2011-07-11 : 07:35:21

Sachin.Nand I have one more query,

in between if more than one "UK" come then how to change the logic.
for example

DECLARE @Sample TABLE
(
ID INT,
Person VARCHAR(10),
date VARCHAR(10),
status VARCHAR(10)
)

INSERT @Sample
VALUES (1, 'p1', 'd1', 'Y'),
(2, 'P2', 'd1', 'N'),
(3, 'p3', 'd1', 'UK'),
(4, 'P4', 'd1', 'UK'),
(5, 'p1', 'd2', 'N'),
(6, 'P2', 'd2', 'N'),
(7, 'p3', 'd2', 'Y'),
(8, 'P4', 'd2', 'UK')

SELECT * FROM @Sample

Here IDs 3 and 4 should also change to "N".

learntsql

524 Posts

Posted - 2011-07-11 : 23:52:51

Hi All,
any idea?
Plz...
TIA.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources