April 29, 2012 at 1:26 am
Hello,
this is rather urgent..
I have a population of people who got less than 3 coupons, and i need to insert one specific coupon to that population (the one that they didn't receive yet; so that they will all have 3 coupons). This is the code for this population (householdid = customerId):
select (householdid) from visa_coupons_output
where householdid in
(select Cust_id from panel_anality
where coupon_code not in('019')
and deb_crd_day in (2,10))
group by householdid
having count(coupon_code) = 2
It returns 9,633 rows. Now i need to insert for 9,324 of them coupon '019'
I just can't get it to work.. please help
April 29, 2012 at 1:29 am
Mikalina (4/29/2012)
Hello,this is rather urgent..
I have a population of people who got less than 3 coupons, and i need to insert one specific coupon to that population (the one that they didn't receive yet; so that they will all have 3 coupons). This is the code for this population (householdid = customerId):
select (householdid) from visa_coupons_output
where householdid in
(select Cust_id from panel_anality
where coupon_code not in('019')
and deb_crd_day in (2,10))
group by householdid
having count(coupon_code) = 2
It returns 9,633 rows. Now i need to insert for 9,324 of them coupon '019'
I just can't get it to work.. please help
Need a bit more info.
Explain the difference between 9,633 and 9,324.
What do you mean by "can't get it to work"? What have you tried?
April 29, 2012 at 1:48 am
Need a bit more info.
Explain the difference between 9,633 and 9,324.
What do you mean by "can't get it to work"? What have you tried?
First of all i performed a test so that i know how many customers can receive this specific coupon that i want to assign to them (019) = how many of them meet the conditions (there were some more conditions which i deleted here, they are not relevant to the question). I got 9633 = people who CAN receive.
9,324 is the number of customers that are actually missing one coupon.
So i need to add the coupon only for those who are missing it, which is 9,324 people.
I tried this, i'm not sure whether it's the correct way..
insert into dbo.visa_coupons_output(householdid, coupon_code)
select top (9324) householdid, '019'
from visa_coupons_output
where householdid in
(select (householdid) from visa_coupons_output
where householdid in
(select Cust_id from panel_anality
where coupon_code not in('019')
and deb_crd_day in (2,10))
group by householdid
having count(coupon_code) = 2
)
April 29, 2012 at 1:57 am
I haven't tried to understand your SELECT query and, as you did not expand on what you meant by "not working", I had to guess that there was a syntax error somewhere along the way.
I have recast your query to use a CTE as the source of data for your input - see below (untested).
;with Coupons as (
select top (9324) householdid, '019' Coupon_Code
from visa_coupons_output
where householdid in
(select (householdid) from visa_coupons_output
where householdid in
(select Cust_id from panel_anality
where coupon_code not in('019')
and deb_crd_day in (2,10))
group by householdid
having count(coupon_code) = 2
))
insert dbo.visa_coupons_output(householdid, coupon_code)
select HouseholdId, Coupon_Code
from Coupons
April 30, 2012 at 10:19 am
Hi, sorry for late response, but i managed with the help of my colleague
Thank you for trying to help:)
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply