select statement 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

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'

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

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/

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?

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/

maddyslayer
Yak Posting Veteran

57 Posts

Posted - 2013-03-13 : 11:49:33

into the same table

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/

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?

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/

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?

Andywin
Starting Member

3 Posts

Posted - 2013-04-05 : 04:40:56

unspammed

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources