Solved

how to get total ticket count on days wise using mysql

Posted on 2016-11-03
24
79 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 13
  • 11
24 Comments
 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41871759
Pls Provide some sample data ? or can you explain me

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

Author Comment

by:srikotesh
ID: 41871813
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 29

Expert Comment

by:Pawan Kumar
ID: 41871817
and the expected out for the data you mentioned above?
0
Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

 
LVL 2

Author Comment

by:srikotesh
ID: 41871862
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 29

Expert Comment

by:Pawan Kumar
ID: 41871882
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 2

Author Comment

by:srikotesh
ID: 41871999
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 29

Expert Comment

by:Pawan Kumar
ID: 41872012
Post your entire query you are executing?
0
 
LVL 2

Author Comment

by:srikotesh
ID: 41872047
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 29

Expert Comment

by:Pawan Kumar
ID: 41872216
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 2

Author Comment

by:srikotesh
ID: 41873419
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 29

Expert Comment

by:Pawan Kumar
ID: 41873430
Are you getting error or incorrect output ?  Query look ok to me.

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

Author Comment

by:srikotesh
ID: 41873461
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
 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41873466
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 29

Expert Comment

by:Pawan Kumar
ID: 41873516
Hi srikotesh,

Is it done ?

Regards,
Pawan
0
 
LVL 2

Author Comment

by:srikotesh
ID: 41873543
same query only I have posted in my last comment

I have tried with both sum,count

is there any difference?
0
 
LVL 2

Author Comment

by:srikotesh
ID: 41873544
same query only I have posted in my last comment

I have tried with both sum,count

is there any difference?
0
 
LVL 2

Author Comment

by:srikotesh
ID: 41873545
same query only I have posted in my last comment

I have tried with both sum,count

is there any difference?
0
 
LVL 29

Accepted Solution

by:
Pawan Kumar earned 500 total points
ID: 41873550
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 2

Author Comment

by:srikotesh
ID: 41873735
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 29

Expert Comment

by:Pawan Kumar
ID: 41873843
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 2

Author Comment

by:srikotesh
ID: 41878129
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 2

Author Comment

by:srikotesh
ID: 41878141
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 29

Expert Comment

by:Pawan Kumar
ID: 41878144
Great that its working !!
0
 
LVL 2

Author Closing Comment

by:srikotesh
ID: 41879971
Superb
0

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Foreword This article was written many years ago, in the days when PHP supported the MySQL extension (http://php.net/manual/en/function.mysql-connect.php).  Today (http://php.net/manual/en/migration70.removed-exts-sapis.php) you would not use MySQL…
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

626 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