SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 UPDATE THE TABLE
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ben_53
Yak Posting Veteran

67 Posts

Posted - 08/18/2011 :  16:03:34  Show Profile  Reply with Quote
Hi Guys,

I have My_Table with four columns.
col1 = ID , col2 = city ,col3 = status, col4 = result
Now ID is identity column.
Cities can be as many as thousands but can be repeated also.
status can be either active or inactive.

now for each city, there can be only one active in status column,
if there exist more than one, flag the record FAIL in result else NULL.

For example, result set should be like :

1 LA Active NULL
2 LA ACTIVE FAIL
3 LA ACTIVE FAIL
4 LA INACTIVE NULL
5 LA INACTIVE NULL


There can not exist two or more actives for the same city.

Please help !!


SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 08/18/2011 :  16:35:07  Show Profile  Visit SwePeso's Homepage  Reply with Quote
UPDATE f
SET f.Flag = CASE WHEN cnt > 1 THEN 'FAIL' ELSE NULL END
FROM (
SELECT Flag, SUM(CASE WHEN Status = 'Active' THEN 1 ELSE 0 END) OVER (PARTITION BY City, Status) AS cnt FROM dbo.My_Table
) AS f



N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

ben_53
Yak Posting Veteran

67 Posts

Posted - 08/18/2011 :  18:22:56  Show Profile  Reply with Quote
Thanks ,

It has a little problem,
It is actually FAILing both values.
Lets get back to the sample Resultset,

The code that you provided is failing 1,2,& 3.

Please note I need to fail 2 and 3 and NULL for 1.
Thanks
Go to Top of Page

namman
Constraint Violating Yak Guru

USA
272 Posts

Posted - 08/18/2011 :  21:03:14  Show Profile  Reply with Quote
Try this ....


declare @temp table(id int identity(1,1), city varchar(10), status varchar(10), d varchar(10))
insert into @temp values('LA', 'Active', null)
insert into @temp values('LA', 'Active', null)
insert into @temp values('LA', 'Active', null)
insert into @temp values('LA', 'Inactive', null)
insert into @temp values('LA', 'Inactive', null)
insert into @temp values('SF', 'Inactive', null)
insert into @temp values('SF', 'Active', null)
insert into @temp values('SF', 'Active', null)
insert into @temp values('LA', 'Active', null)

update temp set td = d from
(
select t.d td, case when t.id=c.id then null when t.status != 'Active' then null else 'Fail' end d
from @temp t join (select min(id) id, city from @temp where status='Active' group by city) c
on t.city=c.city
) temp

select * from @temp order by city



------ test result
1 LA Active NULL
2 LA Active Fail
3 LA Active Fail
4 LA Inactive NULL
5 LA Inactive NULL
9 LA Active Fail
6 SF Inactive NULL
7 SF Active NULL
8 SF Active Fail
Go to Top of Page

ben_53
Yak Posting Veteran

67 Posts

Posted - 08/18/2011 :  21:15:12  Show Profile  Reply with Quote
Unfortunately, this didnt helped me. Sorry
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 08/18/2011 :  23:38:53  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Ben_53, why do you think my code is failing?
Using the sample data provided by namnam, my code generates this output.
declare @temp table(id int identity(1,1), city varchar(10), status varchar(10), d varchar(10))
insert into @temp values('LA', 'Active', null)
insert into @temp values('LA', 'Active', null)
insert into @temp values('LA', 'Active', null)
insert into @temp values('LA', 'Inactive', null)
insert into @temp values('LA', 'Inactive', null)
insert into @temp values('SF', 'Inactive', null)
insert into @temp values('SF', 'Active', null)
insert into @temp values('SF', 'Active', null)
insert into @temp values('LA', 'Active', null)

UPDATE f
SET f.d = CASE WHEN cnt > 1 THEN 'FAIL' ELSE NULL END
FROM (
SELECT d, SUM(CASE WHEN Status = 'Active' THEN 1 ELSE 0 END) OVER (PARTITION BY City, Status) AS cnt FROM @temp
) AS f

select * from @temp
/*
id	city	status		d
1	LA	Active		FAIL	-- 4
2	LA	Active		FAIL	-- 4
3	LA	Active		FAIL	-- 4
4	LA	Inactive	NULL
5	LA	Inactive	NULL
6	SF	Inactive	NULL
7	SF	Active		FAIL	-- 2
8	SF	Active		FAIL	-- 2
9	LA	Active		FAIL	-- 4
*/



N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 08/18/2011 :  23:41:48  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Or... Do you also mean that the logical order matter?
So in my example above, row with id #9 shouldn't fail because it is a single active according to the logical order?
Even if there are three other "LA Active", they are not consecutive according to ID column, and thus the ID 9 is not failing?

And the output you want is this?

/*
id	city	status		d
1	LA	Active		FAIL	-- 3
2	LA	Active		FAIL	-- 3
3	LA	Active		FAIL	-- 3
4	LA	Inactive	NULL
5	LA	Inactive	NULL
6	SF	Inactive	NULL
7	SF	Active		FAIL	-- 2
8	SF	Active		FAIL	-- 2
9	LA	Active		NULL
*/


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

Edited by - SwePeso on 08/18/2011 23:43:31
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 08/18/2011 :  23:51:29  Show Profile  Visit SwePeso's Homepage  Reply with Quote
quote:
Originally posted by ben_53

Thanks ,

It has a little problem,
It is actually FAILing both values.
Lets get back to the sample Resultset,

The code that you provided is failing 1,2,& 3.

Please note I need to fail 2 and 3 and NULL for 1.
Thanks

Is it because Active is spellt differently? Active and ACTIVE?



N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

ben_53
Yak Posting Veteran

67 Posts

Posted - 08/30/2011 :  15:41:35  Show Profile  Reply with Quote
Code :
UPDATE f
SET f.d = CASE WHEN cnt > 1 THEN 'FAIL' ELSE NULL END
FROM (
SELECT d, SUM(CASE WHEN Status = 'Active' THEN 1 ELSE 0 END) OVER (PARTITION BY City, Status) AS cnt FROM @temp
) AS f

is failing ALL "ACTIVE LA" however I want to keep one ACTIVE LA and fail rest.
Go to Top of Page

ben_53
Yak Posting Veteran

67 Posts

Posted - 08/30/2011 :  15:50:11  Show Profile  Reply with Quote
WITH CTE_Multiple AS
(SELECT ROW_NUMBER() OVER (PARTITION BY city, status
ORDER BY (SELECT 0)) RN,result
FROM my_table
)
UPDATE CTE_Multiple
SET result = 'FAIL'

This works like a charm.. Thanks everyone :)
WHERE RN<>1
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 08/30/2011 :  17:21:07  Show Profile  Visit SwePeso's Homepage  Reply with Quote
That will give "Inactive" as Fail too, if there are two or more "Inactive" for same city...



N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.44 seconds. Powered By: Snitz Forums 2000