Complex SQL query

I have a table that tracks emails sent from applications on my server. I would like to write a query that shows how many emails were sent by each application in a certain time period. Here is the table:

---------------------------------------------------------- 
|  emailID  |          SentDT          | ApplicationName | 
---------------------------------------------------------- 
|    1      |  2011-08-04 14:43:31.080 |    Term Form    | 
---------------------------------------------------------- 
|    2      |  2011-08-04 13:59:46.062 |    Term Form    |
---------------------------------------------------------- 
|    3      |  2011-08-03 10:38:15.015 |  Request Form   |
---------------------------------------------------------- 
|    4      |  2011-08-03 05:52:29.005 |    Term Form    |
---------------------------------------------------------- 
|    5      |  2011-08-01 19:58:31.094 | Recruiting Form |  
------------------------------------------------开发者_C百科----------

I would like to see number of emails sent Today, Last 24 hours, Last 7 days, This Month, Last Month, All time.

I know how to do each of these queries by themselves, but I have no clue how to do it in one trip to the database.

For example:

-------------------------------------------------------------- 
|  ApplicationName |  Today | Last24 | Last7days | ThisMonth |
-------------------------------------------------------------- 
|    Term Form     |    2   |   5    |   10      |    19     |
--------------------------------------------------------------
|   Request Form   |    9   |   18   |   36      |    75     |
--------------------------------------------------------------
|  Recruiting Form |    15  |   35   |   100     |    250    |
--------------------------------------------------------------

I tried using a nested select for each subset of times, but I can't use a group by in the nested select. My query that doesn't produce results:

select COUNT(emailID), ApplicationName, (select COUNT(emailID) from emaillog where SentDT > '08/02/2011') as TwoDaysAgo
 from emaillog
 group by ApplicationName
 order by ApplicationName

---

I think it's much easier to do all the date calculations up front, then you can refer to local variables with logical names instead of embedding all the datediff/case etc. calculations in the query logic.

Made a couple of assumptions here. (1) that no data in EmailLog is in the future (2) that by "Last 7 days" you mean today and the full 6 days preceding. I've also included a grand total - even though it's not listed in your desired output, it seems you were trying to get it with the COUNT() outside the subquery.

DECLARE @now SMALLDATETIME = SYSDATETIME();

DECLARE @today DATE = @now, 
        @24hrsago SMALLDATETIME = DATEADD(DAY, -1, @now);

DECLARE @7daysago DATE = DATEADD(DAY, -6, @today),
        @ThisMonth DATE = DATEADD(DAY, 1-DATEPART(DAY, @today), @today);

--SELECT @now, @today, @24hrsago, @7daysago, @ThisMonth;

WITH d AS
(
    SELECT ApplicationName, c = COUNT(*)
    FROM EmailLog
    GROUP BY ApplicationName
),
g AS
(
    SELECT
        ApplicationName,
        [Today]     = SUM(CASE WHEN SentDt >= @today     THEN 1 ELSE 0 END),
        [Last24]    = SUM(CASE WHEN SentDt >= @24hrsago  THEN 1 ELSE 0 END),
        [Last7Days] = SUM(CASE WHEN SentDt >= @7daysago  THEN 1 ELSE 0 END),
        [ThisMonth] = SUM(CASE WHEN SentDt >= @ThisMonth THEN 1 ELSE 0 END)
    FROM EmailLog
    GROUP BY ApplicationName
)
SELECT d.ApplicationName,
    Total = d.c,
    [Today] = COALESCE(g.[Today], 0),
    [Last24] = COALESCE(g.[Last24], 0),
    [Last7days] = COALESCE(g.Last7days, 0),
    [ThisMonth] = COALESCE(g.ThisMonth, 0)
FROM d LEFT OUTER JOIN g
ON d.ApplicationName = g.ApplicationName;

EDIT

If my assumption was wrong and you don't need the total count by application name, the query becomes much simpler:

DECLARE @now SMALLDATETIME = SYSDATETIME();

DECLARE @today DATE = @now, 
        @24hrsago SMALLDATETIME = DATEADD(DAY, -1, @now);

DECLARE @7daysago DATE = DATEADD(DAY, -6, @today),
        @ThisMonth DATE = DATEADD(DAY, 1-DATEPART(DAY, @today), @today);

SELECT ApplicationName,
    [Today]     = SUM(CASE WHEN SentDt >= @today     THEN 1 ELSE 0 END),
    [Last24]    = SUM(CASE WHEN SentDt >= @24hrsago  THEN 1 ELSE 0 END),
    [Last7Days] = SUM(CASE WHEN SentDt >= @7daysago  THEN 1 ELSE 0 END),
    [ThisMonth] = SUM(CASE WHEN SentDt >= @ThisMonth THEN 1 ELSE 0 END)
FROM EmailLog
GROUP BY ApplicationName;

Ordering optional of course.

---

try:

   Select ApplicationName, COunt(*) numEmails
   From table
   where SentDT Between @startDateTime and @EndDateTime
   Group By ApplicationName

NOTE: startDateTime and EndDateTime are oundary limits on records to be processed.

if you also want to establish buckets around specified datetiome ranges, you simply need to define those datetime range buckets in another group by expression (and output that same expression in the select clause ... as an example, say the datetime ranges are calendar months...

   Select DateAdd(month, DateDiff(month, 0, SentDT), 0) CalMonth,
       ApplicationName, Count(*) numEmails
   From table
   where SentDT Between @startDateTime and @EndDateTime
   Group By DateAdd(month, DateDiff(month, 0, SentDT), 0), 
            ApplicationName

---

Something like this should do the trick

select 
    ApplicationName,
    sum(case when daterange = 0 then cnt else 0 end) as Today,
    sum(case when daterange = 1 then cnt else 0 end) as yesterday,
    sum(case when daterange <=2 then cnt else 0 end) as Week,
    sum(case when daterange <=3 then cnt else 0 end) as month,
    sum(cnt) as AllTime
from
    (select 
        ApplicationName, 
        case 
            when days = 0   then '0'
            when days = 1   then '1'
            when days <= 7  then '2'
            when days <= 30 then '3'
            else 4 
        end as
        DateRange, 
        Count(emailid) cnt
    from
        (select ApplicationName, EmailID, datediff(dd, SentDT, getdate()) as Days
        from
            dbo.[YourTableGoesHere]
        ) as foo
    Group by
        ApplicationName,
        case when days < 1 then '0'
            when days = 1 then '1'
            when days <= 7 then '2'
            when days <= 30 then '3'
            else 4 
        end) as bar
group by
    ApplicationName