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)
 Insert Record
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

maddyslayer
Yak Posting Veteran

57 Posts

Posted - 03/06/2013 :  11:52:57  Show Profile  Reply with Quote
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
52249 Posts

Posted - 03/06/2013 :  12:12:00  Show Profile  Reply with Quote
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/

Go to Top of Page

maddyslayer
Yak Posting Veteran

57 Posts

Posted - 03/06/2013 :  13:01:10  Show Profile  Reply with Quote
I also want to check for customer id like when cus_id = '1'. How do I incorporate that?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 03/06/2013 :  13:02:57  Show Profile  Reply with Quote
add it as a condition inside NOT EXISTS using AND

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

Go to Top of Page

maddyslayer
Yak Posting Veteran

57 Posts

Posted - 03/06/2013 :  13:03:38  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 03/06/2013 :  13:11:47  Show Profile  Reply with Quote
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/

Go to Top of Page

maddyslayer
Yak Posting Veteran

57 Posts

Posted - 03/06/2013 :  14:26:53  Show Profile  Reply with Quote
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.
Go to Top of Page

maddyslayer
Yak Posting Veteran

57 Posts

Posted - 03/06/2013 :  14:34:32  Show Profile  Reply with Quote
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?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 03/06/2013 :  23:20:13  Show Profile  Reply with Quote

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/

Go to Top of Page

maddyslayer
Yak Posting Veteran

57 Posts

Posted - 03/07/2013 :  10:13:38  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 03/07/2013 :  11:00:07  Show Profile  Reply with Quote
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/

Go to Top of Page

maddyslayer
Yak Posting Veteran

57 Posts

Posted - 03/07/2013 :  14:47:38  Show Profile  Reply with Quote
The thing is I have multiple customers with different sub_codes for the same customer_id
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 03/07/2013 :  14:58:44  Show Profile  Reply with Quote
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/

Go to Top of Page

maddyslayer
Yak Posting Veteran

57 Posts

Posted - 03/07/2013 :  15:39:35  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 03/08/2013 :  00:17:33  Show Profile  Reply with Quote
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/

Go to Top of Page

maddyslayer
Yak Posting Veteran

57 Posts

Posted - 03/08/2013 :  12:05:37  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 03/08/2013 :  12:09:16  Show Profile  Reply with Quote
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/

Go to Top of Page

maddyslayer
Yak Posting Veteran

57 Posts

Posted - 03/08/2013 :  12:11:32  Show Profile  Reply with Quote
Thanks again.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 03/08/2013 :  13:07:46  Show Profile  Reply with Quote
welcome

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

Go to Top of Page

maddyslayer
Yak Posting Veteran

57 Posts

Posted - 03/11/2013 :  10:57:34  Show Profile  Reply with Quote
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?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 03/11/2013 :  12:28:28  Show Profile  Reply with Quote
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/

Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 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.14 seconds. Powered By: Snitz Forums 2000