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
 Query Help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

aswindba1
Yak Posting Veteran

USA
62 Posts

Posted - 09/11/2013 :  18:03:44  Show Profile  Reply with Quote


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
Flowing Fount of Yak Knowledge

USA
1754 Posts

Posted - 09/11/2013 :  19:00:49  Show Profile  Reply with Quote
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
Go to Top of Page

aswindba1
Yak Posting Veteran

USA
62 Posts

Posted - 09/11/2013 :  19:07:59  Show Profile  Reply with Quote
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
Go to Top of Page

Bustaz Kool
Flowing Fount of Yak Knowledge

USA
1754 Posts

Posted - 09/11/2013 :  19:49:09  Show Profile  Reply with Quote
I can't figure out what your rules are. Here is some code; modify it to suit your needs:
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


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

Edited by - Bustaz Kool on 09/11/2013 19:54:24
Go to Top of Page

aswindba1
Yak Posting Veteran

USA
62 Posts

Posted - 09/11/2013 :  19:54:34  Show Profile  Reply with Quote
Thank you soo much..I appreciate your help.

Go to Top of Page

aswindba1
Yak Posting Veteran

USA
62 Posts

Posted - 09/12/2013 :  13:22:42  Show Profile  Reply with Quote
It got work for me..Once again thank you
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.14 seconds. Powered By: Snitz Forums 2000