| Author |
 Topic  |
|
|
ben_53
Yak Posting Veteran
67 Posts |
 Posted - 08/18/2011 : 16:03:34
|
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
29138 Posts |
Posted - 08/18/2011 : 16:35:07
|
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" |
 |
|
|
ben_53
Yak Posting Veteran
67 Posts |
Posted - 08/18/2011 : 18:22:56
|
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 |
|
|
|
namman
Constraint Violating Yak Guru
USA
258 Posts |
Posted - 08/18/2011 : 21:03:14
|
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 |
|
|
|
ben_53
Yak Posting Veteran
67 Posts |
Posted - 08/18/2011 : 21:15:12
|
| Unfortunately, this didnt helped me. Sorry |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 08/18/2011 : 23:38:53
|
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" |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 08/18/2011 : 23:41:48
|
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 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 08/18/2011 : 23:51:29
|
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" |
|
|
|
ben_53
Yak Posting Veteran
67 Posts |
Posted - 08/30/2011 : 15:41:35
|
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. |
|
|
|
ben_53
Yak Posting Veteran
67 Posts |
Posted - 08/30/2011 : 15:50:11
|
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 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 08/30/2011 : 17:21:07
|
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" |
|
|
| |
Topic |
|
|
|
UPDATE THE TABLE
