I need to do something like the following:
select d.ID,
count(from Lead l where l.DateCreated > DATEADD(day, -30, :todays_date) AND l.DealerId=d.ID),
count(from Lead l where l.DateCreated < DATEADD(day, -30, :todays_date) and l.DateCreated >= DATEADD(day, -60, :todays_date))
FROM Dealer d
GROUP BY d.ID
Ordinarily I could do this:
SELECT DISTINCT d.DealershipID, count( l ) from LEAD as l join l.Dealer as d where l.DateCreated >= DATEADD(day, -30, :todays_date) GROUP BY d.DealershipID
But I need multiple counts, one each of leads that are 30, 60, 90 days old.
(4 columns)
I can do this is plain SQL, but can't find how to write it in HQL...