SQL, How to query Max in having clause

Hi Experts,

I got an error message from the query below. Can you help me on it? Thanks.

Column 'MakeMyDeal_com.dbo.TestDrivelocal.CreatedOn' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.



select count(distinct d.dealid) as testdrive,
case t.timeofday 
when 0 then 'Morning'
when 1 then 'Afternoon'
when 2 then 'Evening'
end as timeofday 
from 
[MakeMyDeal_com].[dbo].[TestDrivelocal] t 
inner join Mmd_feed..dealview d on t.dealid=d.dealid 
inner join Mmd_feed..dart dt on dt.dealerid=d.dealerid
where 
cast(d.submittedtimestamp as date)>=@StartDate and cast(d.submittedtimestamp as date)<=@EndDate
and d.dealstatus='sent'
and d.alternatedeal ='no'
and d.manualdeal='no'
group by 
case t.timeofday when 0 then 'Morning'
when 1 then 'Afternoon'
when 2 then 'Evening' end
having t.createdon= max(createdon)

Open in new window

tanj1035Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

ste5anSenior DeveloperCommented:
What's the purpose of your HAVING clause?

SELECT TOP 1
        COUNT(DISTINCT d.dealid) AS testdrive ,
        CASE t.timeofday
          WHEN 0 THEN 'Morning'
          WHEN 1 THEN 'Afternoon'
          WHEN 2 THEN 'Evening'
        END AS timeofday
FROM    [MakeMyDeal_com].[dbo].[TestDrivelocal] t
        INNER JOIN Mmd_feed..dealview d ON t.dealid = d.dealid
        INNER JOIN Mmd_feed..dart dt ON dt.dealerid = d.dealerid
WHERE   CAST(d.submittedtimestamp AS DATE) >= @StartDate
        AND CAST(d.submittedtimestamp AS DATE) <= @EndDate
        AND d.dealstatus = 'sent'
        AND d.alternatedeal = 'no'
        AND d.manualdeal = 'no'
GROUP BY t.createdon ,
        CASE t.timeofday
          WHEN 0 THEN 'Morning'
          WHEN 1 THEN 'Afternoon'
          WHEN 2 THEN 'Evening'
        END
ORDER BY t.createdon DESC;

Open in new window

tanj1035Author Commented:
hi , thanks for your reply. I have tried your query, but it does not work to my case. It returned only.

TestDrive   TimeofDay
   1               Afternoon
tanj1035Author Commented:
Since each dealID may have multiple "createdon" & "timeofday",  I want to use the max "createdon" to pullout "timeofday". That is the purpose. Thanks.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

ste5anSenior DeveloperCommented:
Please post some sample date and the desired output. I don't understand your requirement..
Deepak ChauhanSQL Server DBACommented:
Try this if this is what you need.
select count(distinct d.dealid) as testdrive,
case t.timeofday 
when 0 then 'Morning'
when 1 then 'Afternoon'
when 2 then 'Evening'
end as timeofday 
from 
[MakeMyDeal_com].[dbo].[TestDrivelocal] t 
inner join Mmd_feed..dealview d on t.dealid=d.dealid 
inner join Mmd_feed..dart dt on dt.dealerid=d.dealerid
where 
cast(d.submittedtimestamp as date)>=@StartDate and cast(d.submittedtimestamp as date)<=@EndDate
and d.dealstatus='sent'
and d.alternatedeal ='no'
and d.manualdeal='no'
group by 
case t.timeofday when 0 then 'Morning'
when 1 then 'Afternoon'
when 2 then 'Evening' end
having max(createdon) in (select createdon from  [MakeMyDeal_com].[dbo].[TestDrivelocal])

Open in new window

tanj1035Author Commented:
Sure, I posted the sample data of major table " makemydeal_com..testdrivelocal".
As you see , for the DealID 25026, it has 2 rows. I want to use its max( createdon) which is "2015-06-04 12:58"  to pull out "time of day" which is "0".

The same concept to other DealIDs, I want to use its max(createdon) to pull out the value of "time of day", then count for each datename, how many total dealID.

Hope my explanation is clear to you. Thanks.

Book2.xlsx
tanj1035Author Commented:
Hi Deepark,

Thanks for your reply.

The max (createdon) in your query seems not working in my case. I still got 2 " createdon" values returned for the DealID 25026. Please see the sample data in my previous post. Thanks.
Deepak ChauhanSQL Server DBACommented:
date is same but time different for this ID

25026      NULL      6/5/15 10:00 PM      1      6/4/15 12:53 PM      20555      2
25026      NULL      6/4/15 10:00 AM      1      6/4/15 12:58 PM      20555      0



having convert(varchar(10),max(createdon), 102) in (select convert(varchar(10),createdon, 102) from  [MakeMyDeal_com].[dbo].[TestDrivelocal])
tanj1035Author Commented:
Sorry, the DealID 25206 still returns 2 rows even I convert the "createon".
I think the issue is in the beginning of my query, the distinct d.dealID pulls out 2 "createdon", even we use MAX. But I have to keep distinct d.dealID.

Any solutions? Thanks.
awking00Information Technology SpecialistCommented:
I'm a little confused. It looks like you're wanting to group by timeofday (represented as morning, afternoon, or evening) and with the sample data, I would expect to see something like
3 Morning
3 Afternoon
6 Evening

However, your expected results are showing counts that don't match the sample data and group by the [day created on]. What is it you really want? Some real data and precise expected results would be a great help.
tanj1035Author Commented:
Yes, I want to know that
Time         count(dealID)
morning    3
afternoon  3
evening      6.

In order to count a correct number of dealID, I have to only count the DealID with max (createdon) since 1 dealID may have multiple createdon.

Hope this can give you a better explanation. I also attached the excel with all tables. Thanks for your help.
Copy-of-Book2.xlsx
awking00Information Technology SpecialistCommented:
So, in reality, you would want to see
Time         count(dealID)
 morning    3
 afternoon  3
 evening     5
since the earlier record for dealid showed a timeofday of 2 (evening) and shouldn't be counted?
If that's the case -
select count(dealid), timeofday from
(select dealid,
 case timeofday
 when 0 then 'Morning'
 when 1 then 'Afternoon'
 when 2 then 'Evening'
 end as timeofday,
 row_number() over (partition by dealid order by createdon desc) rn
 from
 [MakeMyDeal_com].[dbo].[TestDrivelocal] t
 inner join Mmd_feed..dealview d on t.dealid=d.dealid
 inner join Mmd_feed..dart dt on dt.dealerid=d.dealerid)) x
where x.rn = 1
group by timeofday;
Vitor MontalvãoMSSQL Senior EngineerCommented:
Please check your expected result in the Excel file:
Expected Result      
DealID Count          Day of Createdon
30                          Sun
20                          Monday
32                          Tues
20                          Wed
11                          Thur
12                          Fri
15                          Sat
1                          Sun



That is different from what you're saying here:
Yes, I want to know that
 Time         count(dealID)
 morning    3
 afternoon  3
 evening      6.
Vitor MontalvãoMSSQL Senior EngineerCommented:
And the data sample you sent doesn't have the correct records to perform the joins:
select * 
from [TestDrivelocal] t 
	inner join dealview d on t.dealid=d.dealid 
	inner join dart dt on dt.dealerid=d.dealerid

Open in new window

All ID's are different from tables. There is not even a match!

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
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.