Solved

how to get total ticket count on days wise using mysql

Posted on 2016-11-03
24
40 Views
Last Modified: 2016-11-08
HI Experts,

tickets table

ticket id,
created time,
ticket type,
groupname


groupname  total_tickets       1day      3days        5days
 
test101            10                      3                 6                10


how to get total ticket count on days wise using mysql.
0
Comment
Question by:srikotesh
  • 13
  • 11
24 Comments
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
Pls Provide some sample data ? or can you explain me

1day      3days        5days
 3                 6                10
or
total ticket count on days?
0
 
LVL 1

Author Comment

by:srikotesh
Comment Utility
group name      ticketid              date      
      
L1_R          39108             2008-12-31 11:55:30
L1_R          39986             2009-01-03 06:05:53
L2_NET        40090             2009-01-03 13:47:35
L1_NET      42646             2009-01-10 17:00:31
L1_R          43955             2009-01-15 11:28:29



all the tickets count should be based on group name
order by groupname is needed
0
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
and the expected out for the data you mentioned above?
0
 
LVL 1

Author Comment

by:srikotesh
Comment Utility
L1_R          39108             2016-11-03 11:55:30
L1_R          39986             2016-11-03 06:05:53
L1_R          43955             2016-10-31 11:28:29


1DAY   >=CURRENTDATE-1DAY
3DAYS  >=CURRENTDATE-3DAYS
5DAYS  >=CURRENTDATE-5DAYS

L1_R          3(TOTAL)         2(1D)          2(3D)        3(5D)
0
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
Here it is ..

CREATE TABLE tickets
(
	groupname varchar(100),
	ticketid int ,			
	createdtime datetime
)
GO

INSERT INTO tickets VALUES
('L1_R'      ,    39108      ,       '2016-11-02 11:55:30'),
('L1_R'      ,    39986     ,        '2016-11-02 06:05:53'),
('L2_NET'    ,    40090     ,        '2009-01-02 13:47:35'),
('L1_NET'    ,    42646     ,        '2009-01-10 17:00:31'),
('L1_R'      ,    43955     ,        '2016-10-31 11:28:29')

GO

SELECT t.groupname, COUNT(*) cnt 
, COUNT ( CASE WHEN DATEDIFF(NOW(),createdtime) >= 1  AND DATEDIFF(NOW(),createdtime) < 3 THEN 1 END ) [1Day]
, COUNT ( CASE WHEN DATEDIFF(NOW(),createdtime) >= 3  AND DATEDIFF(NOW(),createdtime) < 5 THEN 1 END ) [3Day]
, COUNT ( CASE WHEN DATEDIFF(NOW(),createdtime) >= 5 THEN 1 END ) [5Day]
FROM tickets t
GROUP BY t.groupname
ORDER BY t.groupname

--

Open in new window


Output
--------
groupname      cnt      1Day      3Day      5Day
L1_NET               1          0                   0               1
L1_R               3          2                   1                 0
L2_NET              1          0                   0                1

Hope it helps !
0
 
LVL 1

Author Comment

by:srikotesh
Comment Utility
I am looking for count of ticket ids.
i didn't understand which count we are calculating here..
COUNT(CASE WHEN DATEDIFF(NOW(),created_time) >= 1  AND DATEDIFF(NOW(),created_time) < 3 THEN 1 END )
, COUNT(CASE WHEN DATEDIFF(NOW(),created_time) >= 3  AND DATEDIFF(NOW(),created_time) < 5 THEN 1 END )
, COUNT(CASE WHEN DATEDIFF(NOW(),created_time) >= 5 THEN 1 END )


the above not giving any result
0
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
Post your entire query you are executing?
0
 
LVL 1

Author Comment

by:srikotesh
Comment Utility
query:
======
SELECT ag.group_name, COUNT(*) cnt
, COUNT(CASE WHEN DATEDIFF(NOW(),created_time) >= 1  AND DATEDIFF(NOW(),created_time) < 3 THEN 1 END )
, COUNT(CASE WHEN DATEDIFF(NOW(),created_time) >= 3  AND DATEDIFF(NOW(),created_time) < 5 THEN 1 END )
, COUNT(CASE WHEN DATEDIFF(NOW(),created_time) >= 5 THEN 1 END )
from tickets io
left outer join group_master ag on ag.id = io.assigned_to_group
left outer join statusmaster st on st.status_id = io.status_id
GROUP BY ag.group_name;


Result:
=======
group_name: Support
                                                                                                 cnt: 18
COUNT(CASE WHEN DATEDIFF(NOW(),created_time) >= 1  AND DATEDIFF(NOW(),created_time) < 3 THEN 1 END ): 0
COUNT(CASE WHEN DATEDIFF(NOW(),created_time) >= 3  AND DATEDIFF(NOW(),created_time) < 5 THEN 1 END ): 0
                                      COUNT(CASE WHEN DATEDIFF(NOW(),created_time) >= 5 THEN 1 END ): 18

                                                                                          group_name: ONSITE
                                                                                                 cnt: 5
COUNT(CASE WHEN DATEDIFF(NOW(),created_time) >= 1  AND DATEDIFF(NOW(),created_time) < 3 THEN 1 END ): 0
COUNT(CASE WHEN DATEDIFF(NOW(),created_time) >= 3  AND DATEDIFF(NOW(),created_time) < 5 THEN 1 END ): 0
                                      COUNT(CASE WHEN DATEDIFF(NOW(),created_time) >= 5 THEN 1 END ): 5
0
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
ohh my bad try this

CREATE TABLE tickets
(
	groupname varchar(100),
	ticketid int ,			
	createdtime datetime
)
GO

INSERT INTO tickets VALUES
('L1_R'      ,    39108      ,       '2016-11-02 11:55:30'),
('L1_R'      ,    39986     ,        '2016-11-02 06:05:53'),
('L2_NET'    ,    40090     ,        '2009-01-02 13:47:35'),
('L1_NET'    ,    42646     ,        '2009-01-10 17:00:31'),
('L1_R'      ,    43955     ,        '2016-10-31 11:28:29')

GO

SELECT t.groupname, COUNT(*) cnt 
, SUM ( CASE WHEN DATEDIFF(NOW(),createdtime) >= 1  AND DATEDIFF(NOW(),createdtime) < 3 THEN 1 END ) [1Day]
, SUM ( CASE WHEN DATEDIFF(NOW(),createdtime) >= 3  AND DATEDIFF(NOW(),createdtime) < 5 THEN 1 END ) [3Day]
, SUM ( CASE WHEN DATEDIFF(NOW(),createdtime) >= 5 THEN 1 END ) [5Day]
FROM tickets t
GROUP BY t.groupname
ORDER BY t.groupname

--

Open in new window

0
 
LVL 1

Author Comment

by:srikotesh
Comment Utility
WHATEVER U POSTED THAT IS WORKING FINE

IN MY ACTUAL QUERY I HAVE ADDED ONE LEFT OUTER JOIN

PLEASE VERIFY MY ACTUAL QUERY
I DIDN'T UNDERSTAND WHY IT IS NOT WORKING
I TRIED WITH SUM AND COUNT AS WELL

QUERY:
SELECT ag.group_name, COUNT(*) cnt
, COUNT(CASE WHEN DATEDIFF(NOW(),created_time) >= 1  AND DATEDIFF(NOW(),created_time) < 3 THEN 1 END ) as 1day
, COUNT(CASE WHEN DATEDIFF(NOW(),created_time) >= 3  AND DATEDIFF(NOW(),created_time) < 5 THEN 1 END ) as 3day
, COUNT(CASE WHEN DATEDIFF(NOW(),created_time) >= 5 THEN 1 END ) as 5day
from tickets io
left outer join group_master ag on ag.id = io.assigned_to_group
GROUP BY ag.group_name
ORDER BY ag.group_name;
0
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
Are you getting error or incorrect output ?  Query look ok to me.

You want records that are present in Tickets table only ?
0
 
LVL 1

Author Comment

by:srikotesh
Comment Utility
Are you getting error or incorrect output ?  Query look ok to me.

no error

result is:

 group_name               | cnt    | 1day | 3day | 5day   |
+--------------------------+--------+------+------+--------+
|  TCLMUM-BKC              |      1 |    0 |    0 |      1 |
| Amada_Accel              |      2 |    0 |    0 |      2 |
| Calcom Project           |      1 |    0 |    0 |      1 |
| Engagement Managers      |     34 |    0 |    0 |     34 |

if u see above result cnt column values 5day column values are same


You want records that are present in Tickets table only ?

group name i am getting from another table group_master

left outer join group_master ag on ag.id = io.assigned_to_group
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
Ok try below 2 options...

Try 1

SELECT ag.group_name, COUNT(*) cnt 
, COUNT(CASE WHEN DATEDIFF(NOW(),created_time) >= 1  AND DATEDIFF(NOW(),created_time) < 3 THEN 1 END ) as 1day
, COUNT(CASE WHEN DATEDIFF(NOW(),created_time) >= 3  AND DATEDIFF(NOW(),created_time) < 5 THEN 1 END ) as 3day
, COUNT(CASE WHEN DATEDIFF(NOW(),created_time) = 5 THEN 1 END ) as 5day
from tickets io 
LEFT OUTER JOIN group_master ag on ag.id = io.assigned_to_group 
GROUP BY ag.group_name
ORDER BY ag.group_name;

Open in new window


Try 2...

--

SELECT ag.group_name, COUNT(*) cnt 
, SUM(CASE WHEN DATEDIFF(NOW(),created_time) >= 1  AND DATEDIFF(NOW(),created_time) < 3 THEN 1 END ) as 1day
, SUM(CASE WHEN DATEDIFF(NOW(),created_time) >= 3  AND DATEDIFF(NOW(),created_time) < 5 THEN 1 END ) as 3day
, SUM(CASE WHEN DATEDIFF(NOW(),created_time) = 5 THEN 1 END ) as 5day
from tickets io 
LEFT OUTER JOIN group_master ag on ag.id = io.assigned_to_group 
GROUP BY ag.group_name
ORDER BY ag.group_name;

 

Open in new window


Hope it helps!!
0
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
Hi srikotesh,

Is it done ?

Regards,
Pawan
0
 
LVL 1

Author Comment

by:srikotesh
Comment Utility
same query only I have posted in my last comment

I have tried with both sum,count

is there any difference?
0
 
LVL 1

Author Comment

by:srikotesh
Comment Utility
same query only I have posted in my last comment

I have tried with both sum,count

is there any difference?
0
 
LVL 1

Author Comment

by:srikotesh
Comment Utility
same query only I have posted in my last comment

I have tried with both sum,count

is there any difference?
0
 
LVL 17

Accepted Solution

by:
Pawan Kumar Khowal earned 500 total points
Comment Utility
No I have changed below , Try it out...

, SUM(CASE WHEN DATEDIFF(NOW(),created_time) = 5 THEN 1 END ) as 5day
from tickets io

SELECT ag.group_name, COUNT(*) cnt 
, SUM(CASE WHEN DATEDIFF(NOW(),created_time) >= 1  AND DATEDIFF(NOW(),created_time) < 3 THEN 1 END ) as 1day
, SUM(CASE WHEN DATEDIFF(NOW(),created_time) >= 3  AND DATEDIFF(NOW(),created_time) < 5 THEN 1 END ) as 3day
, SUM(CASE WHEN DATEDIFF(NOW(),created_time) = 5 THEN 1 END ) as 5day
from tickets io 
LEFT OUTER JOIN group_master ag on ag.id = io.assigned_to_group 
GROUP BY ag.group_name
ORDER BY ag.group_name;

Open in new window

0
 
LVL 1

Author Comment

by:srikotesh
Comment Utility
NOW I am getting the below result:



 group_name               | cnt    | 1day | 3day | 5day   |
+--------------------------+--------+------+------+--------+
|  TCLMUM-BKC              |    150|    1 |    0 |      0 |


but ticket creation time 2nd november it should be in 3day coulmn
bcoz ticket is not created on yesterday

| group_name | ticket_id   | created_time        |
+------------+-------------+---------------------+
| TCLMUM-BKC |     2755329 | 2016-11-02 16:45:09 |
+------------+-------------+-------------------
0
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
Hi srikotesh,

Todays date is  is 2016-11-04.

Now 2016-11-04 - 2016-11-02 is 2 so why it should be in 3days section.

For 3days  we should have the difference = 3. right ?
0
 
LVL 1

Author Comment

by:srikotesh
Comment Utility
that logic is fine...

i have created new ticket with current time.
this should come in 1day column i am not seeing that data
bcoz 1day column is taking only previous day information only it is not taking newly created tickets into 1day column

+---------------+-------------+---------------------+
| group_name    | incident_id | created_time        |
+---------------+-------------+---------------------+
| LINUX         |     2755327 | 2016-11-08 09:29:08 |
0
 
LVL 1

Author Comment

by:srikotesh
Comment Utility
i have changed the condition like below
COUNT(CASE WHEN DATEDIFF(NOW(),created_time) <3 THEN 1 END ) as 1day
it is working now
0
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
Great that its working !!
0
 
LVL 1

Author Closing Comment

by:srikotesh
Comment Utility
Superb
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Fore-Foreword Today (2016) Maxmind has a new approach to the distribution of its data sets.  This article may be obsolete.  Instead of using the examples here, have a look at the MaxMind API (https://www.maxmind.com/en/geolite2-developer-package). …
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now