CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
DeanMWake
DeanMWake

Reputation: 923

How to get totals per day

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

Upvotes: 0

Views: 3290

Answers (6)

Hugh Jones
Hugh Jones

Reputation: 2694

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

Upvotes: 0

John Woo
John Woo

Reputation: 263703

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]

Upvotes: 0

Skinny Pipes
Skinny Pipes

Reputation: 1035

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

Upvotes: 4

Prahalad Gaggar
Prahalad Gaggar

Reputation: 11599

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

Upvotes: 1

Rodion
Rodion

Reputation: 886

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.

Upvotes: 1

bvr
bvr

Reputation: 4826

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

Upvotes: 0

Related Questions