| Author |
 Topic  |
|
|
maddyslayer
Yak Posting Veteran
53 Posts |
 Posted - 03/12/2013 : 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
19 Posts |
Posted - 03/12/2013 : 18:10:08
|
| select cus_id from <tablename> where code_state = 'Y' |
 |
|
|
maddyslayer
Yak Posting Veteran
53 Posts |
Posted - 03/13/2013 : 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
India
47023 Posts |
Posted - 03/13/2013 : 10:58:45
|
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/
|
|
|
|
maddyslayer
Yak Posting Veteran
53 Posts |
Posted - 03/13/2013 : 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
India
47023 Posts |
Posted - 03/13/2013 : 11:48:28
|
insert record where?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
maddyslayer
Yak Posting Veteran
53 Posts |
Posted - 03/13/2013 : 11:49:33
|
| into the same table |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 03/13/2013 : 11:57:48
|
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 |
|
|
|
maddyslayer
Yak Posting Veteran
53 Posts |
Posted - 03/13/2013 : 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
India
47023 Posts |
Posted - 03/13/2013 : 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
53 Posts |
Posted - 03/13/2013 : 13:16:25
|
| How do insert a new record with cus_code = 'xy' for each customer? |
|
|
|
Andywin
Starting Member
USA
3 Posts |
Posted - 04/05/2013 : 04:40:56
|
| unspammed |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 04/09/2013 : 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
|
|
|
| |
Topic |
|
select statement help
