Link to home
Start Free TrialLog in
Avatar of tanj1035
tanj1035

asked on

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

Avatar of ste5an
ste5an
Flag of Germany image

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

Avatar of tanj1035
tanj1035

ASKER

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
Since each dealID may have multiple "createdon" & "timeofday",  I want to use the max "createdon" to pullout "timeofday". That is the purpose. Thanks.
Please post some sample date and the desired output. I don't understand your requirement..
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

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
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.
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])
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.
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.
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
SOLUTION
Avatar of awking00
awking00
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial