| Author |
 Topic  |
|
maddyslayer
Yak Posting Veteran
53 Posts |
 Posted - 03/06/2013 : 11:52:57
|
Hi,
Here's the "Customer" table I have
Cus_ID Cus_code Cus_State
1 X Y
2 y N
3 Z N
How do I insert a new record into the same Customer table with new Cus_code = 'P' when Cus_code is X or Y and Cus_State is Y for those respective customer codes. I don't want any duplicate records to be inserted. |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 03/06/2013 : 12:12:00
|
do you mean this?
INSERT INTO Customer (Cus_Code,Cus_State)
SELECT 'P',
'y'
FROM Customer
WHERE EXISTS (SELECT 1 FROM Customer WHERE Cus_Code IN ('x','y') )
AND NOT EXISTS (SELECT 1 FROM Customer WHERE Cus_Code ='P' AND Cus_State ='y')
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
 |
|
|
maddyslayer
Yak Posting Veteran
53 Posts |
Posted - 03/06/2013 : 13:01:10
|
| I also want to check for customer id like when cus_id = '1'. How do I incorporate that? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 03/06/2013 : 13:02:57
|
add it as a condition inside NOT EXISTS using AND
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
maddyslayer
Yak Posting Veteran
53 Posts |
Posted - 03/06/2013 : 13:03:38
|
Actually the cus_id is not a unique key. There could be different rows with same customer id.
Cus_id cus_code cus_state
1 X Y
1 Y N
1 Z N
Now, How do I insert a new record into the same Customer table with new Cus_code = 'P' when Cus_code is X or Y and Cus_State is Y for those respective customer codes. I don't want any duplicate records to be inserted. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 03/06/2013 : 13:11:47
|
so in above case how many new records it will insert? can you show table output after insertion?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
maddyslayer
Yak Posting Veteran
53 Posts |
Posted - 03/06/2013 : 14:26:53
|
| Basically I want to insert a new row for every cus_id which has cus_code = X or cus_code = Y or cus_code = both x and y and cus_state = y for either one of them. |
|
|
|
maddyslayer
Yak Posting Veteran
53 Posts |
Posted - 03/06/2013 : 14:34:32
|
Here's the actual table customer
sub_id list_id cust_id ss_id sub_codesub_state
4363835 1 3275330 0 B2B N
4363836 1 3275330 0 CC Y
4363837 1 3275330 0 CU N
4363838 1 3275330 0 FND Y
4363839 1 3275330 0 MA N
4363840 1 3275330 0 MB Y
4363841 1 3275330 0 MNE N
4363842 1 3275330 0 MP N
4363843 1 3275330 0 MR N
4363844 1 3275330 0 MTL N
4363845 1 3275330 0 PD Y
4363846 1 3275330 0 PDD Y
4363847 1 3275330 0 SO Y
4363848 1 3275330 0 TV Y
4363849 1 3275330 0 WEB Y
4363850 1 3275330 0 YOC Y
I want to insert an additional record with sub_code = 'AWD' sub_state = 'y' for each customer who has sub_code = 'PDD' or 'TV' and sub_state = "Y". How do i do that? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 03/06/2013 : 23:20:13
|
INSERT Customer (list_id,cust_id,ss_id,sub_code,sub_state)
SELECT list_id,cust_id,ss_id,'AWD','y'
FROM Customer
GROUP BY list_id,cust_id,ss_id
HAVING SUM(CASE WHEN sub_code IN ('PDD','TV') AND sub_State='y' THEN 1 ELSE 0 END) >0
AND SUM(CASE WHEN sub_code = 'AWD' AND sub_state = 'y' THEN 1 ELSE 0 END)=0
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
maddyslayer
Yak Posting Veteran
53 Posts |
Posted - 03/07/2013 : 10:13:38
|
| This results in inserting two duplicate records with AWD when both PDD and TV cus_state is Y |
Edited by - maddyslayer on 03/07/2013 10:14:04 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 03/07/2013 : 11:00:07
|
nope..it will insert only a single record so far as list_id,cust_id,ss_id values are same from them as per your sample data.
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
maddyslayer
Yak Posting Veteran
53 Posts |
Posted - 03/07/2013 : 14:47:38
|
| The thing is I have multiple customers with different sub_codes for the same customer_id |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 03/07/2013 : 14:58:44
|
so what. i'm not grouping by sub_code so you wont get any duplicates as per your earlier posted sample data
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
maddyslayer
Yak Posting Veteran
53 Posts |
Posted - 03/07/2013 : 15:39:35
|
This is what I get when I run the following query.
INSERT AMA_SUB_SUBSCRIBER_SUBSCRIPTIONS (List_ID, Master_Customer_id, SS_ID, Subscription_code, Subscription_state, Add_Date)
SELECT List_ID, Master_Customer_id, SS_ID,'AWD','y', '2013-03-06 11:40:20.400'
FROM AMA_SUB_SUBSCRIBER_SUBSCRIPTIONS Where Master_Customer_id = '03275330'
GROUP BY List_ID, Master_Customer_id, SS_ID, Add_Date
HAVING SUM(CASE WHEN Subscription_code IN ('PDD','TV') AND Subscription_state='y' THEN 1 ELSE 0 END) >0
AND SUM(CASE WHEN Subscription_code = 'AWD' AND Subscription_state = 'y' THEN 1 ELSE 0 END)=0
i don't want two records with the same subscription code AWD for the same customer id
sub_id list_id mastcustid ssid subcode substate adddate
4363835 1 3275330 0 B2B N 40:20.4
4363836 1 3275330 0 CC Y 40:20.4
4363837 1 3275330 0 CU N 40:20.4
4363838 1 3275330 0 FND Y 40:20.4
4363839 1 3275330 0 MA N 40:20.4
4363840 1 3275330 0 MB Y 40:20.4
4363841 1 3275330 0 MNE N 40:20.4
4363842 1 3275330 0 MP N 40:20.4
4363843 1 3275330 0 MR N 40:20.4
4363844 1 3275330 0 MTL N 40:20.4
4363845 1 3275330 0 PD Y 40:20.4
4363846 1 3275330 0 PDD Y 40:20.4
4363847 1 3275330 0 SO Y 40:20.4
4363848 1 3275330 0 TV Y 40:20.4
4363849 1 3275330 0 WEB Y 40:20.4
4363850 1 3275330 0 YOC Y 40:20.4
8304011 1 3275330 0 AWD y 40:20.4
8304012 1 3275330 0 AWD y 40:20.4
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 03/08/2013 : 00:17:33
|
quote:
Originally posted by maddyslayer
This is what I get when I run the following query.
INSERT AMA_SUB_SUBSCRIBER_SUBSCRIPTIONS (List_ID, Master_Customer_id, SS_ID, Subscription_code, Subscription_state, Add_Date)
SELECT List_ID, Master_Customer_id, SS_ID,'AWD','y', '2013-03-06 11:40:20.400'
FROM AMA_SUB_SUBSCRIBER_SUBSCRIPTIONS Where Master_Customer_id = '03275330'
GROUP BY List_ID, Master_Customer_id, SS_ID, Add_Date
HAVING SUM(CASE WHEN Subscription_code IN ('PDD','TV') AND Subscription_state='y' THEN 1 ELSE 0 END) >0
AND SUM(CASE WHEN Subscription_code = 'AWD' AND Subscription_state = 'y' THEN 1 ELSE 0 END)=0
i don't want two records with the same subscription code AWD for the same customer id
sub_id list_id mastcustid ssid subcode substate adddate
4363835 1 3275330 0 B2B N 40:20.4
4363836 1 3275330 0 CC Y 40:20.4
4363837 1 3275330 0 CU N 40:20.4
4363838 1 3275330 0 FND Y 40:20.4
4363839 1 3275330 0 MA N 40:20.4
4363840 1 3275330 0 MB Y 40:20.4
4363841 1 3275330 0 MNE N 40:20.4
4363842 1 3275330 0 MP N 40:20.4
4363843 1 3275330 0 MR N 40:20.4
4363844 1 3275330 0 MTL N 40:20.4
4363845 1 3275330 0 PD Y 40:20.4
4363846 1 3275330 0 PDD Y 40:20.4
4363847 1 3275330 0 SO Y 40:20.4
4363848 1 3275330 0 TV Y 40:20.4
4363849 1 3275330 0 WEB Y 40:20.4
4363850 1 3275330 0 YOC Y 40:20.4
8304011 1 3275330 0 AWD y 40:20.4
8304012 1 3275330 0 AWD y 40:20.4
this was not i suggested
modify like above
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
maddyslayer
Yak Posting Veteran
53 Posts |
Posted - 03/08/2013 : 12:05:37
|
Thanks dude, It worked.
What does the following mean?
HAVING SUM(CASE WHEN Subscription_code IN ('PDD','TV') AND Subscription_state='y' THEN 1 ELSE 0 END) >0
AND SUM(CASE WHEN Subscription_code = 'AWD' AND Subscription_state = 'y' THEN 1 ELSE 0 END)=0 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 03/08/2013 : 12:09:16
|
the first condition checks for presence of records in table with Subscription_code IN ('PDD','TV') AND Subscription_state='y' for the same List_ID, Master_Customer_id, SS_ID value combination.
the second condition make sure you add the record with 'AWD','y' only if its not already present
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
maddyslayer
Yak Posting Veteran
53 Posts |
Posted - 03/08/2013 : 12:11:32
|
| Thanks again. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 03/08/2013 : 13:07:46
|
welcome
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
maddyslayer
Yak Posting Veteran
53 Posts |
Posted - 03/11/2013 : 10:57:34
|
| Can you please let me know how do I get all the customers with subscription codes "pdd" and "tv" and subscription state = y for both those codes? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 03/11/2013 : 12:28:28
|
do you mean only records of customers having subscription codes "pdd" and "tv" and subscription state = y like below
SELECT *
FROM AMA_SUB_SUBSCRIBER_SUBSCRIPTIONS
WHERE Subscription_code IN ('PDD','TV') AND Subscription_state='y'
or all records of customers who have records with subscription codes "pdd" and "tv" and subscription state = y
ie like
SELECT *
FROM
(
SELECT *,SUM(CASE WHEN Subscription_code IN ('PDD','TV') AND Subscription_state='y' THEN 1 ELSE 0 END) OVER (PARTITION BY Master_Customer_id) AS Cnt
FROM AMA_SUB_SUBSCRIBER_SUBSCRIPTIONS
)t
WHERE Cnt >0
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
Topic |
|
Insert Record
