Solved

how to get total ticket count on days wise using mysql

Posted on 2016-11-03
24
64 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 28

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 28

Expert Comment

by:Pawan Kumar
ID: 41871817
and the expected out for the data you mentioned above?
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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 28

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 28

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 28

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 28

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 28

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 28

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 28

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 28

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 28

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

This guide whil teach how to setup live replication (database mirroring) on 2 servers for backup or other purposes. In our example situation we have this network schema (see atachment). We need to replicate EVERY executed SQL query on server 1 to…
Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

828 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