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
 SQL Server 2012 Forums
 Transact-SQL (2012)
 select statement help

Author  Topic 

maddyslayer
Yak Posting Veteran

57 Posts

Posted - 2013-03-12 : 17:15:12
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

35 Posts

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

maddyslayer
Yak Posting Veteran

57 Posts

Posted - 2013-03-13 : 10:47:40
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

52326 Posts

Posted - 2013-03-13 : 10:58:45
[code]
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
[/code]

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

Go to Top of Page

maddyslayer
Yak Posting Veteran

57 Posts

Posted - 2013-03-13 : 11:33:40
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

52326 Posts

Posted - 2013-03-13 : 11:48:28
insert record where?

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

Go to Top of Page

maddyslayer
Yak Posting Veteran

57 Posts

Posted - 2013-03-13 : 11:49:33
into the same table
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-13 : 11:57:48
[code]
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
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

maddyslayer
Yak Posting Veteran

57 Posts

Posted - 2013-03-13 : 12:15:26
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

52326 Posts

Posted - 2013-03-13 : 12:24:29
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 - 2013-03-13 : 13:16:25
How do insert a new record with cus_code = 'xy' for each customer?
Go to Top of Page

Andywin
Starting Member

3 Posts

Posted - 2013-04-05 : 04:40:56
unspammed
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-09 : 01:20:12
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
   

- Advertisement -