Query 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

General SQL Server Forums

New to SQL Server Programming

Query Help

Author

Topic

aswindba1
Yak Posting Veteran

62 Posts

Posted - 2013-09-11 : 18:03:44

Hi I want to update Flag column in second table based on the Adder names.

If the Applicatiion has atleast one AIX and Adder name is UDB then the flag would be True.
If the Application has more the one AIX and Adder names are diferent then the flag would be null.

APpName OS Adder

App1 ||| Windows|||Null
App1 ||| Linux |||UDB
App1 ||| AIX |||UDB
App1 ||| Linux |||Sql

App2 ||| AIX ||| UDB
App2 ||| Windows||| UDB
App2 ||| Linux ||| UDB
App2 ||| AIX ||| UDB

OUTPUT SHOULD BE LOOK LIKE BELOW
APpName OS Adder Flag

App1||| Windows|||Null|||null
App1||| Linux |||UDB |||null
App1||| AIX |||UDB |||null
App1||| Linux |||Sql |||null

App2|||AIX ||| UDB|||TRUE
App2|||Windows||| UDB|||TRUE
App2|||Linux ||| UDB|||TRUE
App2|||AIX ||| UDB|||TRUE

Let me know fi you need addiitional information.

Thanks
Aswin

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2013-09-11 : 19:00:49

Your output doesn't seem to match the rules. The second and third line listed for App1 have an AIX and the Adder is UDB. Why isn't the flag True? Also, when is the flag ever false?

=================================================
The cure for anything is salt water -- sweat, tears, or the sea. -Isak Dinesen

aswindba1
Yak Posting Veteran

62 Posts

Posted - 2013-09-11 : 19:07:59

Hi thaks for your prompt reply.

if the application has OS AIX with different adder names then its false.

If the applications has OS AIX and all correspondig AIX has UDB adder then its TRUE

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2013-09-11 : 19:49:09

I can't figure out what your rules are. Here is some code; modify it to suit your needs:[CODE]declare @tbl table (
AppName varchar(10),
OS varchar(10),
Adder varchar(5)
)

insert into @tbl
values
('App1', 'Windows', Null),
('App1', 'Linux ', 'UDB'),
('App1', 'AIX ', 'UDB'),
('App1', 'Linux ', 'Sql'),

('App2', 'AIX', 'UDB'),
('App2', 'Windows', 'UDB'),
('App2', 'Linux', 'UDB'),
('App2', 'AIX', 'UDB')

--select * from @tbl

;with PerAppName
as (
select
AppName,
count(*) AppCnt,
sum(case when OS = 'AIX' then 1 else 0 end) AixCnt,
sum(case when Adder = 'UDB' then 1 else 0 end) UDBCnt
from @tbl
group by AppName
)
select a.*,
case
when pan.AixCnt > 0 and pan.UDBCnt = pan.AppCnt then 'True'
when pan.AixCnt > 0 then 'False'
else Null
end Flag
from
@tbl a
inner join
PerAppName pan
on a.AppName = pan.AppName
order by
a.AppName[/CODE]

=================================================
The cure for anything is salt water -- sweat, tears, or the sea. -Isak Dinesen

aswindba1
Yak Posting Veteran

62 Posts

Posted - 2013-09-11 : 19:54:34

Thank you soo much..I appreciate your help.

aswindba1
Yak Posting Veteran

62 Posts

Posted - 2013-09-12 : 13:22:42

It got work for me..Once again thank you

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources