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
 SQL Server 2012 Forums
 Transact-SQL (2012)
 select statement help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

maddyslayer
Yak Posting Veteran

57 Posts

Posted - 03/12/2013 :  17:15:12  Show Profile  Reply with Quote
How do I get the customer_ids where for any cus_code the code_state is Y?

list_id cus_id cus_code code_state

1 20 x Y
2 20 y y
3 20 z y
4 20 p N
5 21 x N
2 21 y y
3 21 z y
4 21 p N

ppatel112
Starting Member

28 Posts

Posted - 03/12/2013 :  18:10:08  Show Profile  Reply with Quote
select cus_id from <tablename> where code_state = 'Y'
Go to Top of Page

maddyslayer
Yak Posting Veteran

57 Posts

Posted - 03/13/2013 :  10:47:40  Show Profile  Reply with Quote
That wouldn't work. I want the list of cus_ids where the cus_state is Y for all cus_codes
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 03/13/2013 :  10:58:45  Show Profile  Reply with Quote

SELECT list_id, cus_id, cus_code, code_state
FROM
(
SELECT *,SUM(CASE WHEN cus_state = 'N' THEN 1 ELSE 0 END) OVER (PARTITION BY cus_id) AS Cnt
FROM table
)t
WHERE Cnt=0


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

maddyslayer
Yak Posting Veteran

57 Posts

Posted - 03/13/2013 :  11:33:40  Show Profile  Reply with Quote
Thanks, How do I insert a record for a customer with cus_code = 'XY' when that condition comes true?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 03/13/2013 :  11:48:28  Show Profile  Reply with Quote
insert record where?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

maddyslayer
Yak Posting Veteran

57 Posts

Posted - 03/13/2013 :  11:49:33  Show Profile  Reply with Quote
into the same table
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 03/13/2013 :  11:57:48  Show Profile  Reply with Quote

INSERT table (list_id, cus_id, cus_code, code_state)
SELECT list_id, cus_id, 'XY', code_state
FROM table
GROUP BY list_id, cus_id, code_state
HAVING SUM(CASE WHEN code_state = 'N' THEN 1 ELSE 0 END)=0


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Edited by - visakh16 on 03/13/2013 11:59:14
Go to Top of Page

maddyslayer
Yak Posting Veteran

57 Posts

Posted - 03/13/2013 :  12:15:26  Show Profile  Reply with Quote
How do I insert a record with cus_code='xy' for all customers other than those who currently have code_state='N' for all cuscodes?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 03/13/2013 :  12:24:29  Show Profile  Reply with Quote
just the opposite. why dont you try it yourself tweaking above query?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

maddyslayer
Yak Posting Veteran

57 Posts

Posted - 03/13/2013 :  13:16:25  Show Profile  Reply with Quote
How do insert a new record with cus_code = 'xy' for each customer?
Go to Top of Page

Andywin
Starting Member

USA
3 Posts

Posted - 04/05/2013 :  04:40:56  Show Profile  Reply with Quote
unspammed
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 04/09/2013 :  01:20:12  Show Profile  Reply with Quote
quote:
Originally posted by maddyslayer

How do insert a new record with cus_code = 'xy' for each customer?


do a cross join with customer table and use xy as value for custcode

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
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.06 seconds. Powered By: Snitz Forums 2000