asked on

SQL, how to apply date range for a column.

Hi Experts,

I want to apply date range (see bold part in the query to the column "COUNT(t.dealid) as testdrive" .

I wrote this query, but It did not return any value, only the header. Please help me on it. thanks.

use MMD_Feed

SELECT dt.[dealerid]
,dt.[name]
,dt.[Alternatedealmtd]
,dt.[ManualDealmtd]
,dt.[wtd]
,dt.[mtd]
,dt.[lastmonth]
,dt.lastmonthtotal
,dt.[trending]
,COUNT(t.dealid) as testdrive
FROM [MMD_Feed].[dbo].[DART] AS dt
INNER JOIN Mmd_feed..dealview AS dv
      on dt.dealerid=dv.dealerid
INNER JOIN Mmd_feed..deal d
      on dv.dealid=d.dealid
LEFT OUTER JOIN [MakeMyDeal_com].[dbo].[TestDrive] t
      on t.dealid=d.dealid
where name in (@DealershipName)
and t.dealid in (select t.dealid from [MakeMyDeal_com].[dbo].[TestDrive]
where cast(t.createdon as date)>=DateAdd("d",-(Day(getdate())-1), getdate()) and cast(t.createdon as date)<getdate())
group by dt.[dealerid]
,[name]
,[Alternatedealmtd]
,[ManualDealmtd]
,[wtd]
,[mtd]
,[lastmonth]
,lastmonthtotal
,[trending]

Jim Horn

Illustrate for us what you mean by 'date range'. Month and year? Week? Custom range?
A data mockup would be real helpful here.

Kyle Abrahams, PMP

You don't need day:

DateAdd("d",-(Day(getdate())-1), getdate())
becomes:
DateAdd("d",-1,getdate())

tanj1035

ASKER

I am trying to get the
count (t.dealid) as testdrive from cast(t.createdon as date)>=DateAdd("d",-1), getdate()) and cast(t.createdon as date)<getdate()

So, I do not know how can I put the date range above in a right place in the query.

Thanks.

Kyle Abrahams, PMP

you had an extra ")" in the dateadd function:

count (t.dealid) as testdrive from cast(t.createdon as date)>=DateAdd("d",-1, getdate()) and cast(t.createdon as date)<getdate()

tanj1035

ASKER

Thanks, I have corrected it. but it still did not return value.

Kyle Abrahams, PMP

work on the inner query first:

select t.dealid from [MakeMyDeal_com].[dbo].[TestDrive]
where cast(t.createdon as date)>=DateAdd("d",-1, getdate()) and cast(t.createdon as date)<getdate()

And are you sure there are any records for today?

tanj1035

ASKER

If I run the query itself below
select t.dealid from [MakeMyDeal_com].[dbo].[TestDrive]
where cast(t.createdon as date)>=DateAdd("d",-1, getdate()) and cast(t.createdon as date)<getdate()

It can return the value.

But if I put it in the main query, it can not return value.

ASKER CERTIFIED SOLUTION

Kyle Abrahams, PMP

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

tanj1035

ASKER

Yes, I have to join dealid