sql how to join CTE table

Hi Experts,

I want to join columns from 2 datasets and the join condition is cte.dealerid = mmd_feed..deal.dealerid.  So, the report will show like in the attachment. Can you help me to combine 2 datasets? Thank you.

1)
with cte as (
select  
dt.[NAME],
dt.[dealerid] as DealerID,
sum(ad.[WidgetInteraction]) as Pencils,
sum(ad.[WidgetLoad]) as Impressions,
isnull(dt.[SevenDayResponseAdj],0) as ResponseTime,
dt.[SFDC_Status] as Status
from MakeMyDeal_com.[dbo].[adobe_data] ad
join [MMD_Feed].[dbo].[DART] dt on ad.dealerid=dt.dealerid
where dt.[LeasingActive]='Y'
and cast(ad.date as date)>=@StartDate and cast(ad.date as date)<=@EndDate
group by dt.[NAME],dt.dealerid,isnull(dt.[SevenDayResponseAdj],0),dt.[SFDC_Status])

2)
select
d.[dealerid],
sum (case when do.offertype= 1 then 1 else 0 end) as TotalLease,
sum (case when do.offertype=0 and d.vehiclestatus = 'New' then 1 else 0 end ) as NewTotalFinanceDeals,
Sum(case when dv.[ArchiveDealStatus]= 'CustomerSold' then 1 else 0 end) as 'Sold Archived'
from mmd_feed..Deal d
inner join mmd_feed..DealResponse  doi on d.DealID=doi.dealid
inner join makemydeal_com..dealoffer do on doi.dealofferid=do.dealofferid
inner join mmd_feed..dealview dv on dv.dealid=d.dealid
inner join mmd_feed..dealer de on de.dealerid=d.dealerid
inner join mmd_feed.[dbo].[DealResponseMinAndMaxShopper] dms on dms.dealid=d.dealid and dms.FirstMessage=doi.DealResponseID
where cast(d.submittedtimestamp as date)>=@StartDate and cast(d.submittedtimestamp as date)<=@EndDate
group by d.[dealerid]

query1.JPG
tanj1035Asked:
Who is Participating?
 
Leo TorresSQL DeveloperCommented:
here try this. Not sure what the best key is but dealer id seems like a good guess

with cte as (
 select  
 dt.[NAME],
 dt.[dealerid] as DealerID,
 sum(ad.[WidgetInteraction]) as Pencils,
 sum(ad.[WidgetLoad]) as Impressions,
 isnull(dt.[SevenDayResponseAdj],0) as ResponseTime,
 dt.[SFDC_Status] as Status
 from MakeMyDeal_com.[dbo].[adobe_data] ad
 join [MMD_Feed].[dbo].[DART] dt on ad.dealerid=dt.dealerid
 where dt.[LeasingActive]='Y' 
 and cast(ad.date as date)>=@StartDate and cast(ad.date as date)<=@EndDate
 group by dt.[NAME],dt.dealerid,isnull(dt.[SevenDayResponseAdj],0),dt.[SFDC_Status]
 )


 select 
 d.[dealerid],
 sum (case when do.offertype= 1 then 1 else 0 end) as TotalLease,
 sum (case when do.offertype=0 and d.vehiclestatus = 'New' then 1 else 0 end ) as NewTotalFinanceDeals,
 Sum(case when dv.[ArchiveDealStatus]= 'CustomerSold' then 1 else 0 end) as 'Sold Archived'
 from mmd_feed..Deal d
	join mmd_feed..DealResponse  doi 
		on d.DealID=doi.dealid
	join makemydeal_com..dealoffer do 
		on doi.dealofferid=do.dealofferid
	join mmd_feed..dealview dv 
		on dv.dealid=d.dealid
	join mmd_feed..dealer de 
		on de.dealerid=d.dealerid
	join mmd_feed.[dbo].[DealResponseMinAndMaxShopper] dms 
		on dms.dealid=d.dealid and dms.FirstMessage=doi.DealResponseID
	join cte cte
		on cte.DealerID = de.dealerid 
 where cast(d.submittedtimestamp as date)>=@StartDate 
 and cast(d.submittedtimestamp as date)<=@EndDate
 group by d.[dealerid]

Open in new window

0
 
tanj1035Author Commented:
hi Leo,

If I want to add
declare @StartDate date
declare @EndDate date
set @StartDate ='2015-04-01'
set @EndDate='2015-04-08'

in CTE, how to add it? I tried it different ways, but it does not work. Thanks.
0
 
tanj1035Author Commented:
I worked it out, Never mind. Thx.

declare @StartDate date
declare @EndDate date
set @StartDate ='2015-04-01'
set @EndDate='2015-04-08';

with cte as (
0
 
tanj1035Author Commented:
I've requested that this question be closed as follows:

Accepted answer: 500 points for Leo Torres's comment #a40776836
Assisted answer: 0 points for tanj1035's comment #a40776914

for the following reason:

I found the answer for my extended question.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.