SQL, does not return values

Hi Experts,
Please see my query below

declare @StartDate date
declare @EndDate date
declare @Dealerid int
set @StartDate ='2015-05-01'
set @EndDate='2015-05-27'
set @Dealerid= 307;


with cte as
(select dv.dealerid,
sum(case when dr.ShopperResponseStatus='Accepted' then 1 else 0 end) as 'Acceptedbyshopper',
sum(case when dr.DealerResponseStatus='Accepted' then 1 else 0 end) as 'Acceptedbydealer'
from MMD_Feed..Dealresponse dr
inner join MMD_Feed..Dealview dv on dr.dealid=dv.dealid
where cast(timestamp as date) between @StartDate and @EndDate
and dv.dealerid=@Dealerid
group by dv.dealerid)

select cte.dealerid, Acceptedbyshopper,Acceptedbydealer,count(*) as ExpiredDeals
from cte cte
inner join MMD_Feed..Deal  d on d.dealerid=cte.dealerid
where cast(submittedtimestamp as date) between @StartDate and @EndDate
and d.dealerid=@Dealerid
and dealstatus in ('Expired', 'Expired-Attention')
group by cte.dealerid,Acceptedbyshopper,Acceptedbydealer

My questions is
The dealer ID 37 has 0 expired deals. If I run a different query, it can return 0
select count(*) as expireddeals
from MMD_Feed..Deal
where dealerid=307
and cast(submittedtimestamp as date) between '2015-05-01' and '2015-05-27'
and dealstatus in ('Expired', 'Expired-Attention')

But, when I run that big query, the table does not return any values for expired deals, Acceptedbyshopper,Acceptedbydealer

Please help me on this. Thanks!!
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.

Deepak ChauhanSQL Server DBACommented:
Try this

select count(*) as expireddeals
 from MMD_Feed..Deal
where dealerid=307
 and cast(submittedtimestamp as date) >= '2015-05-01' and cast(submittedtimestamp as date) <='2015-05-27'
 and dealstatus in ('Expired', 'Expired-Attention')
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>where cast(timestamp as date) between @StartDate and @EndDate
apart from the "problem", this sounds bad usage.
if the field is date field already, the cast should not be needed :
http://www.experts-exchange.com/articles/1499/DATE-and-TIME-don%27t-be-scared-and-do-it-right-the-first-time.html


apart from that, your problem seems to be a data "issue" about the inner join here:
from cte cte
inner join MMD_Feed..Deal  d on d.dealerid=cte.dealerid

please double-check the data
0
tanj1035Author Commented:
Thanks for your reply. Based on your recommendations, I have revised my query, but it still did not return the value.
 If the dealerid=99 which has 4 expired deals for example, the whole query will return the values. I assume the issue could be that if the dealerID has 0 expired deal, the query would not work.
So, do you have any recommendations? Thanks.

declare @StartDate date
declare @EndDate date
declare @Dealerid int
set @StartDate ='2015-05-01'
set @EndDate='2015-05-27'
set @Dealerid= 307;


with cte as
(select dv.dealerid,
sum(case when dr.ShopperResponseStatus='Accepted' then 1 else 0 end) as 'Acceptedbyshopper',
sum(case when dr.DealerResponseStatus='Accepted' then 1 else 0 end) as 'Acceptedbydealer'
from MMD_Feed..Dealresponse dr
inner join MMD_Feed..Dealview dv on dr.dealid=dv.dealid
where cast(timestamp as date) between @StartDate and @EndDate
and dv.dealerid=@Dealerid
group by dv.dealerid)

select Acceptedbyshopper,Acceptedbydealer,
 count(*) as ExpiredDeals
from cte cte
join MMD_Feed..Deal  d on d.dealerid=cte.dealerid
where submittedtimestamp  >= @StartDate and submittedtimestamp <=@EndDate
--and d.dealerid=@Dealerid
and dealstatus in ('Expired', 'Expired-Attention')
group by Acceptedbyshopper,Acceptedbydealer
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.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
again, with only the SQL it will be difficult to impossible.
please use http://sqlfiddle.com/ to set up a data set/example of the input data to run your sql against
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Try to use LEFT JOIN instead of INNER JOIN:
with cte as 
	(select dv.dealerid,
		sum(case when dr.ShopperResponseStatus='Accepted' then 1 else 0 end) as 'Acceptedbyshopper',
		sum(case when dr.DealerResponseStatus='Accepted' then 1 else 0 end) as 'Acceptedbydealer'
	from MMD_Feed..Dealresponse dr
		LEFT join MMD_Feed..Dealview dv on dr.dealid=dv.dealid
	where cast(timestamp as date) between @StartDate and @EndDate and dv.dealerid=@Dealerid
	group by dv.dealerid)
select cte.dealerid, Acceptedbyshopper,Acceptedbydealer,count(*) as ExpiredDeals
from cte cte 
	LEFT join MMD_Feed..Deal  d on d.dealerid=cte.dealerid
where cast(submittedtimestamp as date) between @StartDate and @EndDate
	and d.dealerid=@Dealerid
	and dealstatus in ('Expired', 'Expired-Attention') 
group by cte.dealerid,Acceptedbyshopper,Acceptedbydealer

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:
Thanks, Vitor, I tried it, but it does not work.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Please post some sample data.
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.