iFr4g iFr4g - 5 months ago 9
SQL Question

Return values even if null, 0 or not present

I want to create some reports so I can grab data quickly regarding our helpdesk, I am using GLPI which is running on MySQL 5.5

I have the below code and it returns some useful information but not enough so it can be put into a pretty stacked graph which is what he has requested. To get enough data to achieve this I need the urgency's that are zero and the days that are also zero, then I can convert the data into a table in Excel and build the graph.

Current Query:

SELECT DATE_FORMAT(date,'%d/%m/%Y') AS Date,Urgency,COUNT(*) as Tickets
FROM glpi.glpi_tickets
WHERE month(date)=month(NOW())
GROUP BY urgency ORDER BY date,urgency ASC;


This returns:

# Date, Urgency, Tickets
'07/06/2016', '3', '10'
'10/06/2016', '2', '1'
'14/06/2016', '1', '1'
'14/06/2016', '5', '1'


I would ideally like it to display as below:

# Date, Urgency, Tickets
'07/06/2016', '1', '0'
'07/06/2016', '2', '0'
'07/06/2016', '3', '10'
'07/06/2016', '4', '0'
'07/06/2016', '5', '0'
'08/06/2016', '1', '0'
'08/06/2016', '2', '0'
'08/06/2016', '3', '0'
'08/06/2016', '4', '0'
'08/06/2016', '5', '0'
...
'14/06/2016', '1', '1'
'14/06/2016', '2', '0'
'14/06/2016', '3', '0'
'14/06/2016', '4', '0'
'14/06/2016', '5', '1'


And so on.

I am kind of getting the hang of SQL (self teaching) so all and any help is much appreciated.

EDIT: Schema added, I think this is what you was asking for (hope it works) http://sqlfiddle.com/#!9/715c7

Answer

So this is what I ended up doing and it appears to be supplying the data just how I want it.

First of all I created a new table to store the data in:

    CREATE TABLE glpi_plugin_ns_ticketstats
    (
    id INT(11),
    daterun date,
    timerun time,
    totaltickets INT(11),
    verylow INT(11),
    low INT(11),
    med INT(11),
    high INT(11),
    veryhigh INT(11));

Then I built a stored procedure to collect and populate the data:

USE `glpi`;
DROP procedure IF EXISTS `Daily_Ticket_Stats`;

DELIMITER $$
USE `glpi`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `Daily_Ticket_Stats`()
BEGIN
declare todayd date;
## Declaring the variable for the daterun column ##
declare todayt time;
## Declaring the variable for the timerun column ##
declare totalt int(11);
## Declaring the variable for totaltickets column ##
declare vlow int (11);
## Declaring the variable for verylow column ##
declare low int(11);
## Declaring the variable for low column ##
declare med int(11);
## Declaring the variable for med column ##
declare high int (11);
## Declaring the variable for high column ##
declare vhigh int(11);
## Declaring the variable for veryhigh column ##
    set todayd = CURDATE();
    ## Set date to today ##
    set todayt = CURTIME();
    ## Set time to now ##
    set totalt = (SELECT COUNT(*) as ttickets 
        FROM glpi.glpi_tickets
    WHERE day(date)=day(NOW()));
    ## This has set the total for the total tickets variable ##
    set vlow = (SELECT COUNT(*) as vltickets 
        FROM glpi.glpi_tickets
    WHERE day(date)=day(NOW())
        AND urgency = '1');
    ## This has set the total for the very low urgency tickets variable ##
    set low = (SELECT COUNT(*) as ltickets 
        FROM glpi.glpi_tickets
    WHERE day(date)=day(NOW())
        AND urgency = '2');
    ## This has set the total for the low urgency tickets variable ##
    set med = (SELECT COUNT(*) as mtickets 
        FROM glpi.glpi_tickets
    WHERE day(date)=day(NOW())
        AND urgency = '3');
    ## This has set the total for the medium urgency tickets variable ##
    set high = (SELECT COUNT(*) as htickets 
        FROM glpi.glpi_tickets
    WHERE day(date)=day(NOW())
        AND urgency = '4');
    ## This has set the total for the high urgency tickets variable ##
    set vhigh = (SELECT COUNT(*) as vhtickets 
        FROM glpi.glpi_tickets
    WHERE day(date)=day(NOW())
        AND urgency = '5');

IF EXISTS(
    SELECT *
    FROM glpi.glpi_plugin_ns_ticketstats 
    WHERE daterun = CURDATE())
    THEN
BEGIN
    UPDATE glpi.glpi_plugin_ns_ticketstats 
SET 
    timerun = CURTIME(),
    totaltickets = totalt,
    verylow = vlow,
    low = low,
    med = med,
    high = high,
    veryhigh = vhigh
WHERE
    daterun = CURDATE();
END;
ELSE
INSERT INTO glpi.glpi_plugin_ns_ticketstats VALUES (NULL,todayd,todayt,totalt,vlow,low,med,high,vhigh);
END IF;
END
#$$

DELIMITER ;

I then set this procedure to run every hour so the stats are recent if my manager wishes to refer to them during the day (I entered this directly onto the mysql server terminal):

CREATE EVENT TicketStatusUpdate
ON SCHEDULE EVERY 1 HOUR
STARTS CURRENT_TIMESTAMP + INTERVAL 29 MINUTE
DO
CALL Daily_Ticket_Stats();

The 29 minute is because I wanted it to run as close to the hour as I could get it.

This now outputs lovely data in a format that allows me to create a stacked graph in Excel (Spoofed entries from 1st-16th June so I had entries from start of month):

# id, daterun, timerun, totaltickets, verylow, low, med, high, veryhigh
'1', '2016-06-01', '23:00:00', '0', '0', '0', '0', '0', '0'
'2', '2016-06-02', '23:00:00', '0', '0', '0', '0', '0', '0'
'3', '2016-06-03', '23:00:00', '0', '0', '0', '0', '0', '0'
'4', '2016-06-04', '23:00:00', '0', '0', '0', '0', '0', '0'
'5', '2016-06-05', '23:00:00', '0', '0', '0', '0', '0', '0'
'6', '2016-06-06', '23:00:00', '0', '0', '0', '0', '0', '0'
'7', '2016-06-07', '23:00:00', '0', '0', '0', '0', '0', '0'
'8', '2016-06-08', '23:00:00', '0', '0', '0', '0', '0', '0'
'9', '2016-06-09', '23:00:00', '0', '0', '0', '0', '0', '0'
'10', '2016-06-10', '23:00:00', '0', '0', '0', '0', '0', '0'
'11', '2016-06-11', '23:00:00', '0', '0', '0', '0', '0', '0'
'12', '2016-06-12', '23:00:00', '0', '0', '0', '0', '0', '0'
'13', '2016-06-13', '23:00:00', '0', '0', '0', '0', '0', '0'
'14', '2016-06-14', '23:00:00', '0', '0', '0', '0', '0', '0'
'15', '2016-06-15', '23:00:00', '0', '0', '0', '0', '0', '0'
'16', '2016-06-16', '23:00:00', '0', '0', '0', '0', '0', '0'
'17', '2016-06-17', '12:31:22', '4', '1', '0', '0', '0', '3'

This then lets me do a select so I only get the current month imported into Excel:

select * from glpi.glpi_plugin_ns_ticketstats
where month(daterun)=month(NOW())

I'll leave this here so if anyone wants to use it they can, thanks everyone for your time and help :)

iFr4g

Comments