Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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
52326 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
52326 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
52326 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
52326 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
52326 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
52326 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
52326 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
52326 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
52326 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
52326 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
 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.13 seconds. Powered By: Snitz Forums 2000