views:

1082

answers:

0

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