SQL, the query returns the same value

hi Experts,

The "activatedate" returns '2015-03-09' to all dealerID. But, I want to get the result like:
DealerID       ActivateDate
99                   2015-03-09
120                 2015-05-01
122                2015-04-30

Can you help me on it? Thanks.


Open in new window

select dv.dealershipname,
d.[dealerid],
(select min(date)from [MMD_Feed].[dbo].[DealsPerDayHistoricalByType] t inner join mmd_feed..dealer d on
d.dealerid=t.dealerid where t.dealerid = d.dealerid
) as activatedate,
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' and d.vehiclestatus = 'New' then 1 else 0 end) as 'SoldArchived'
into #deals
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(doi.timestamp as date)>=@StartDate and cast(doi.timestamp as date)<=@EndDate
and dv.alternatedeal='No' and dv.manualdeal='No'
and dv.dealstatus='Sent'
group by d.[dealerid],dv.dealershipname
tanj1035Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kanti PrasadCommented:
Hi

Remove the min
so change your script from min(date)  to date
0
Anoo S PillaiCommented:
You have two aliases "d" in your query.  One in the outer query and in in the inner query that that generates 'activatedate'. Change the aliases to distinguish between inner and outer reference of dealer table. Changing the query that generates 'activatedate' to something like the following may resolve your issue ( Hope you are referring dealer table in outer query in the where clause )

select min(date)
from [MMD_Feed].[dbo].[DealsPerDayHistoricalByType] t
inner join mmd_feed..dealer dInner
on  dInner .dealerid=t.dealerid
where t.dealerid = d.dealerid
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Try this:
select dv.dealershipname, 
d.[dealerid], 
min(t.date) as activatedate,
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' and d.vehiclestatus = 'New' then 1 
		else 0 
	end) as 'SoldArchived'
 into #deals
 from mmd_feed..Deal d
	inner join [MMD_Feed].[dbo].[DealsPerDayHistoricalByType] t on d.dealerid=t.dealerid 
	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(doi.timestamp as date)>=@StartDate and cast(doi.timestamp as date)<=@EndDate
	and dv.alternatedeal='No' and dv.manualdeal='No'and dv.dealstatus='Sent'
 group by d.[dealerid],dv.dealershipname 

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

tanj1035Author Commented:
hi Annoo,

Thanks for your reply. I did not notice I have put the same alias. I have changed the alias. But the query still returned the "activated date " -2015-03-9' to all dealerID.
0
tanj1035Author Commented:
Hi Vitor,

Thanks for your reply. I have tried yours. The "activated date" returned the correct date to each dealerid, that is what I am expecting to. But, "total lease", "NewTotalFinanceDeals" and "soldarchived'" returned hug numbers which are not correct.
Do you any other recommendations?

Thanks.
0
tanj1035Author Commented:
Hi Kanti,

I have to get the min of the date, so I can not move the "min". Thanks.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
I will need to understand your data model to help you through reducing the higher values.
Let's try another solution instead:
select dv.dealershipname,
	d.[dealerid],
	(select min(date)from [MMD_Feed].[dbo].[DealsPerDayHistoricalByType] t where t.dealerid = d.dealerid) as activatedate,
	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' and d.vehiclestatus = 'New' then 1 else 0 end) as 'SoldArchived'
into #deals
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(doi.timestamp as date)>=@StartDate and cast(doi.timestamp as date)<=@EndDate
	and dv.alternatedeal='No' and dv.manualdeal='No'
	and dv.dealstatus='Sent'
 group by d.[dealerid],dv.dealershipname 

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
tanj1035Author Commented:
This is what I am looking for!!!Thanks a  lot!!!!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

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.