How to get totals per day

Question

Hi I have a table that looks like the following:

                  Table Name: Emails
    ID        |CreatedDate             |finalStatus
    115173922 |2013-04-09 12:33:23.234 |DELIVERED
    115123432 |2013-04-09 08:21:23.234 |FAILED
    115143212 |2013-04-09 12:24:23.234 |DELIVERED
    115173922 |2013-04-09 05:05:23.234 |DELIVERED
    111233922 |2013-04-10 12:44:23.234 |PENDING
    115123912 |2013-04-10 12:05:23.234 |DELIVERED
    115173922 |2013-04-11 22:09:23.234 |DELIVERED
    111233922 |2013-04-11 13:05:23.234 |PENDING
    115123912 |2013-04-11 05:23:23.234 |DELIVERED

What I need to do is get the total amount of DELIVERED, FAILED and PENDING finalStatus's per day for the month. I have tried to modify MySQL code that people have given in previous answers such as this: SQL query for Calculating Total No. of Orders per Day? but have not been able to get it working.

Here is the code that I have so far:

    SELECT DISTINCT  (CAST(CreatedDate as DATE)) as Date,

    (SELECT COUNT(finalStatus)
    FROM [Emails]
    WHERE finalStatus = 'DELIVERED') AS Delivered,

    (SELECT COUNT(finalStatus)
    FROM [Emails]
    WHERE finalStatus = 'FAILED') AS Failed,

    (SELECT COUNT(finalStatus)
    FROM [Emails]
    WHERE finalStatus = 'PENDING') AS Pending

    FROM [Emails]
    GROUP BY (CAST(CreatedDate as DATE))

If anyone could help me that would be amazing. I have been stuck on this for a few hours now and may go crazy soon...

http://stackoverflow.com/questions/943968/help-with-multiple-group-by-sql-server-query group by year than group by month.. — SenthilPrabhu, Apr 15 '13 at 10:51
do you still want to show the date even if it has no transaction on that day? — John Woo, Apr 15 '13 at 11:11

score 4 · Accepted Answer · answered Apr 15 '13 at 11:01

4

Since this is SQL Server 2008, make use of casting the CREATEDDATE into DATE only using CAST(),

SELECT CAST(E.CreatedDate AS DATE) DateCreated,
       COUNT(case when E.finalStatus = 'DELIVERED' then 1 end) as DELIVERED,
       COUNT(case when E.finalStatus = 'FAILED' then 1 end) as FAILED,
       COUNT(case when E.finalStatus = 'PENDING' then 1 end) as PENDING
FROM    TableName E
GROUP   BY CAST(E.CreatedDate AS DATE)

http://www.sqlfiddle.com/#!3/dc195/4

answered Apr 15 '13 at 11:01

Skinny Pipes

1,025
6
14

Thanks soo much. I have been looking for this for a while but I just could not seem to find any answers that I could convert to my exact situation. There are lots which do this without the where clause (joining simple select statements) but non helped. Thanks – DeanMWake Apr 15 '13 at 11:24
There is perhaps a weakness in this approach; if it is possible that a new status ('RETURNED') might be possible in the future, then the query will have to be rewritten. You can include the status column in the `GROUP BY` clause to get round this. – Hugh Jones Apr 15 '13 at 11:36
Thats not possible as these values are static and are returned by a service provider. Thanks for the feedback. No need for the data to be flushed at the start of every month. I simply used that as an example so the question was easier to understand. – DeanMWake Apr 15 '13 at 12:15
@HughJones don't judge it by saying a weakness approach unless proven wrong. What you are saying are assumptions that need to be proven. – Skinny Pipes Apr 15 '13 at 12:43
@SkinnyPipes - I was very careful to say PERHAPS a weakness - I cannot say for sure because we do not know much about the OPs requirement. In some circumstances it might be a distinct advantage that future statuses are ignored by the query. – Hugh Jones Apr 15 '13 at 14:01
@Skinnypipes - Also - I suspect you are taking my comment as a criticism of your answer, which it is not. You copied some logic from the OPs original question which is perfectly valid. However, there is a nuance which I think has been missed - namely that there is an opportunity to `GROUP BY` both the created day AND the final status. To my eye this was potentially a simple missing piece in the OPs logic. – Hugh Jones Apr 15 '13 at 14:07

Prahalad Gaggar · Answer 2 · 2013-04-15T11:00:44.253

1

I think you need something like this:

DATENAME

select CAST(CreatedDate as DATE),
       sum(case when E.finalStatus = 'DELIVERED' then 1 else 0 end) as DELIVERED,
       sum(case when E.finalStatus = 'FAILED' then 1 else 0 end) as FAILED,
       sum(case when E.finalStatus = 'PENDING' then 1 else 0 end) as PENDING
from Emails E
where DATENAME(MONTH,E.CreatedDate)='your month name'
group by CAST(E.CreatedDate as DATE)

SQL Fiddle

edited Apr 15 '13 at 11:00

answered Apr 15 '13 at 10:52

Prahalad Gaggar

11,389
16
53
71

OP wants ***per day for the month***, hence the **where**. None of the answer has taken that into consideration. – Prahalad Gaggar Apr 15 '13 at 11:09

score 1 · Answer 3 · answered Apr 15 '13 at 10:57

1

You can differ each month like this:

SELECT year(datestart), month(datestart)
finalStatus,
COUNT(finalStatus)
FROM [Emails]
GROUP BY year(datestart), month(datestart),finalStatus

And grouping like this is a much faster way, than subquerying.

answered Apr 15 '13 at 10:57

Rodion

886
10
24

Thanks, I tried this but its not exactly what I was looking for I dont want to have to specify anything. As it should run by itself each month without extra input from me. – DeanMWake Apr 15 '13 at 11:22
That is just a matter of supplying the correct parameter each month. That can easily be automated with date arithmetic. Rudy's answer is a good one you should think about it. – Hugh Jones Apr 15 '13 at 11:42

score 0 · Answer 4 · answered Apr 15 '13 at 10:56

Try this

 SELECT DISTINCT  (CAST(CreatedDate as DATE)) as Date,

    (SELECT COUNT(finalStatus)
    FROM [Emails] E1
    WHERE finalStatus = 'DELIVERED'  AND 
    CAST(E1.CreatedDate as DATE) = CAST(E.CreatedDate as DATE)) AS Delivered,

    (SELECT COUNT(finalStatus)
    FROM [Emails] E2
    WHERE finalStatus = 'FAILED' AND 
     CAST(E2.CreatedDate as DATE) = CAST(E.CreatedDate as DATE)) AS Failed,

    (SELECT COUNT(finalStatus)
    FROM [Emails] E3
    WHERE finalStatus = 'PENDING' AND 
     CAST(E3.CreatedDate as DATE) = CAST(E.CreatedDate as DATE)) AS Pending

    FROM [Emails] E
    GROUP BY (CAST(CreatedDate as DATE))

Thanks this worked although I am using Luv's one as it is a lot shorter. Thanks for the help :) — DeanMWake, Apr 15 '13 at 11:21

Hugh Jones · Answer 5 · 2013-04-15T11:37:49.333

0

with daily_figures as
(
select 
 cast(CreatedDate as date) as CreatedDate,
 finalStatus,
 Count(*) as DayCount
From 
  EMails
Group 
  by cast(CreatedDate as date), FinalStatus
)
select * from daily_figures 
Where 
  datepart(month, CreatedDate) = 4 and
  datepart(year, CreatedDate) = 2013

It depends on the circumstances of your application how you supply the necessary year.

Sql Fiddle

edited Apr 15 '13 at 11:37

answered Apr 15 '13 at 11:11

Hugh Jones

2,706
19
30

score 0 · Answer 6 · answered Apr 15 '13 at 11:17

If you only want to get the dates that has a transaction, disregard this answer since both LUV and Skinny Pipes has already demonstrated it.

Considering that you want to get all dates for the whole month of APRIL, you need first to generate a calendar table for the whole month of APRIL and then joined it with you table. This query uses recursive CTE to create a calendar.

WITH April_Calendar
AS
(
  SELECT CAST('20130401' as datetime) AS [date]
  UNION ALL
  SELECT DATEADD(dd, 1, [date])
  FROM   April_Calendar
  WHERE  DATEADD(dd, 1, [date]) <= '20130430'
)
SELECT  a.[Date] DateCreated,
        COUNT(case when E.finalStatus = 'DELIVERED' then 1 end) as DELIVERED,
        COUNT(case when E.finalStatus = 'FAILED' then 1 end) as FAILED,
        COUNT(case when E.finalStatus = 'PENDING' then 1 end) as PENDING
FROM    April_Calendar a
        LEFT JOIN Emails E
            ON a.[date] = CAST(E.CreatedDate AS DATE)
GROUP   BY a.[Date]

SQLFiddle Demo

How to get totals per day

6 Answers6

Linked