August 18, 2011 at 3:33 pm
This is kinda complicated. I have a data table T_Leads with the following fields:
CampaignID int null
AffiliateID int null
IndustryID int not null
and a lookup table T_Campaigns with the same fields. I'm trying to do an INSERT into a 3rd table so need a single SELECT that will satisfy a couple conditions:
If T_Leads.CampaignID is not null join on CampaignID and the select is done
otherwise
join on AffiliateID and IndustryID but only return data if COUNT(*) in Campaigns = 1. There could be zero, 1 or multiple records in T_Campaigns with matching values for Affiliate and Industry
This is what I have but it doesn't work.
select l.CaptureDate, l.LeadID, l.[Date], l.Affiliate, l.SubAffiliate, c.CampaignID, c.PayoutAmount, c.PayoutPercentage
FROM T_Campaigns c
JOIN T_Leads l on l.CampaignID = c.CampaignID
WHERE l.CampaignID is not null or (lCampaignID is null and
(SELECT count(*)
FROM T_Campaigns c
JOIN T_Leads l on l.CampaignID = c.CampaignID
WHERE (MONTH(l.CaptureDate)=8 AND YEAR(l.CaptureDate)=2011)
AND c.IndustryID = l.IndustryID AND c.AffiliateID = l.Affiliate and c.Status=1
GROUP BY c.CampaignID
HAVING COUNT(*) = 1))
I'm relatively inexperienced with T_SQL, any suggestions would be appreciated.
August 19, 2011 at 3:46 am
This was removed by the editor as SPAM
August 19, 2011 at 10:59 am
This is brilliant Stewart, more complicated than I can comprehend so I'm going to break it apart to try to learn what is going on. Thanks so much!
August 19, 2011 at 12:24 pm
Stewart can you give me a pointer how to include the above with INSERT INTO?
What seems logical is
; With SingleCampaign AS
....
UNION ALL
SELECT INTO T_AffiliateLeads (CaptureDate....)
(SELECT l.CaptureDate
...
Where l.LeadID IS NULL)
but sql is complaining about the insert into. It is a compound statement as you have shown, how do I convince SQL of this?
Thanks, Stu
August 19, 2011 at 4:18 pm
Nevermind, I got by placing the above into an EXECUTE 😛
August 22, 2011 at 1:31 am
This was removed by the editor as SPAM
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply