You can use conditional sum for this calculation something as
select
date_format(created,'%d-%m') as created_date ,
sum( case when status='open' then 1 else 0 end )+
sum( case when status='new' then 1 else 0 end )+
sum( case when status='resolved' and date(resolved) > date(created) then 1 else 0 end ) as open
from table_name
where
YEAR(Created) = '2015'
group by created_date ;
Here is a test case
mysql> select * from test ;
+------+---------------------+---------------------+----------+
| id | created | resolved | status |
+------+---------------------+---------------------+----------+
| 1 | 2015-05-10 00:00:00 | 1970-01-01 00:00:00 | open |
| 2 | 2015-05-10 00:00:00 | 1970-01-01 00:00:00 | new |
| 3 | 2015-05-10 00:00:00 | 2015-05-12 00:00:00 | resolved |
| 4 | 2015-05-11 00:00:00 | 1970-01-01 00:00:00 | open |
| 5 | 2015-05-11 00:00:00 | 1970-01-01 00:00:00 | new |
| 6 | 2015-05-11 00:00:00 | 2015-05-11 00:00:00 | resolved |
+------+---------------------+---------------------+----------+
6 rows in set (0.00 sec)
mysql> select
-> date_format(created,'%d-%m') as created_date ,
-> sum( case when status='open' then 1 else 0 end )+
-> sum( case when status='new' then 1 else 0 end )+
-> sum( case when status='resolved' and date(resolved) > date(created) then 1 else 0 end ) as open
-> from test
-> where
-> YEAR(Created) = '2015'
-> group by created_date ;
+--------------+------+
| created_date | open |
+--------------+------+
| 10-05 | 3 |
| 11-05 | 2 |
+--------------+------+
2 rows in set (0.00 sec)
UPDATE
The count of open is a incremental as per the comment. So the following should do the trick
select
created_date,
@open:= @open+open_ticket-resolved as open
from(
select
date_format(created,'%d-%m') as created_date ,
sum( case when status='open' then 1 else 0 end )+
sum( case when status='new' then 1 else 0 end )+
sum( case when status='resolved' and ( date(resolved) > date(created) or date(resolved) = date(created)) then 1 else 0 end ) as open_ticket,
sum(case when status = 'resolved' and date(resolved) = date(created) then 1 else 0 end) as resolved
from test
where
YEAR(Created) = '2015'
group by created_date
)x,(select @open:=0)r order by created_date ;
From the above sample data it will have
+--------------+------+
| created_date | open |
+--------------+------+
| 10-05 | 3 |
| 11-05 | 5 |
+--------------+------+
UPDATE:
Count the previous tickets which got resolved on a given date and deduct that from the current day open ticket, for that you need a left join
something as
select
created_date,
@open:= @open+open_ticket-resolved_ticket as open
from(
select
date_format(t.created,'%d-%m') as created_date ,
sum( case when t.status='open' then 1 else 0 end )+
sum( case when t.status='new' then 1 else 0 end )+
sum( case when t.status='resolved' and ( date(t.resolved) > date(created) or date(t.resolved) = date(created)) then 1 else 0 end ) as open_ticket,
coalesce(tot,0) as resolved_ticket
from test t left join(
select count(*) as tot, date(resolved) as resolved from test where status='resolved' group by date(resolved)
)x on date(x.resolved) = date(t.created)
where
YEAR(t.created) = '2015'
group by created_date
)x,(select @open:=0)r order by created_date ;
OUTPUT :
+--------------+------+
| created_date | open |
+--------------+------+
| 10-05 | 3 |
| 11-05 | 5 |
| 12-05 | 7 |
+--------------+------+
You should use the GROUPING
function
GROUPING returns 1 for aggregated or 0 for not aggregated in the
result set.
The advantage of this is that it will deal correctly with the case that B2B_Week
is itself NULL
and not incorrectly treat it as a total row.
WITH T
AS (SELECT DATEPART(ISO_WEEK, ig.IN_EVT_DT_TM) AS B2B_Week,
bm.MISSION_ID,
dl.DRIVER
FROM DD.B2B_MISSIONS bm
INNER JOIN dd.IN_GATE ig
ON bm.MISSION_ID = ig.IN_MISSION_ID
INNER JOIN dd.XNS_DRIVER_LOGIN dl
ON ig.IN_DRIVER_ID = dl.driver)
SELECT B2B_Week = CASE WHEN GROUPING(B2B_Week) = 0 THEN CAST(B2B_Week AS VARCHAR(5)) ELSE 'Total' END,
B2B_Mission = COUNT (DISTINCT MISSION_ID),
Distinct_B2B_Drivers = COUNT (DISTINCT DRIVER)
FROM T
GROUP BY B2B_Week WITH ROLLUP
ORDER BY GROUPING(B2B_Week),
T.B2B_Week
Best Answer
I think your
CASE
statements may be flawed as you're always going to havevalidity_name
andvalidity_surname
equal to 0, because the name cannot be 3 different things at once.When do you want the validity_name to equal 1?
Regardless of that fact, I like the
APPLY VALUES
method of grouping on a computed column.Using AdventureWorks as an example, this works like so:
When applied to your query, it should be something like the following (untested, use the example above to build your query):