ChoklatStu ChoklatStu - 6 months ago 22
MySQL Question

MySQL Help joining tables to make a jasper ireport

Let me start by saying I'm not a sql expert or a jasper-reports expert, but i'm trying to generate a report that shows who logged into my project by company and client per month. I also know that I'm designing the query to look 1 month into the future so that I have data to handle the chance that A company signs up creates a client or 2 and then doesn't log in or do anything.

This is what I want the returned data to look like:

Date Company Client Client_ID Authentications Unique_Users
2016-may Company-A client-1 1 24 1
2016-may Company-A client-2 2 10 2
2016-may Company-A client-3 3 0 0
2016-June Company-A client-1 1 0 0
2016-June Company-A client-2 2 0 0
2016-June Company-A client-3 3 0 0


I have this query that will return the Date, company name, client name, client id, auths and unique users for months that actions occurred in. This query leaves out rows that have no authentications.

SELECT
DATE_FORMAT(rq.requestTime, '%Y-%M') AS Date,
company.name AS Company,
client.name AS Client,
client.id AS client_id,
COUNT(rq.id) AS Authentications,
COUNT(DISTINCT rq.personguid) AS Unique_Users
FROM
company JOIN CLIENT ON company.id = client.company_id
LEFT JOIN request_queue rq ON rq.clientid = client.id
WHERE
company.id = 19
AND rq.status = 'complete' AND rq.request_type LIKE "%authorize%"
AND MONTH(rq.requestTime) >= MONTH("2016-05-01")
AND MONTH(rq.requestTime) <= MONTH("2016-06-01")
GROUP BY
client_id, Date
ORDER BY Date ASC, client_id ASC;


This would return something like this because some clients have not logged in in May and June has no records yet, basically no request_queue data no rows:

Date Company Client Client_ID Authentications Unique_Users
2016-may Company-A client-1 1 24 1
2016-may Company-A client-2 2 10 2


At this point I came up with this.

SELECT
dateTable.mydate AS Date,
clientTable.ClientName AS Client,
clientTable.CompanyName AS Company,
clientTable.client_id AS Client_ID
FROM
(SELECT
client.name AS ClientName,
client.id AS client_id,
company.name AS CompanyName
FROM
company JOIN CLIENT ON company.id = client.company_id
WHERE company.id = 19) clientTable
JOIN
(SELECT
DATE_FORMAT(temp.thedate,'%Y-%M') AS mydate,
temp.thedate AS theDate FROM (
SELECT
DATE_ADD("2016-05-01", INTERVAL u.i MONTH) AS thedate
FROM i AS u ORDER BY thedate) temp
WHERE temp.thedate <= "2016-06-01") dateTable;


This gives me a table that has dates, company names, client names, and client ids without the request_queue data.

This table looks like this:

Date Company Client Client_ID
2016-may Company-A client-1 1
2016-may Company-A client-2 2
2016-may Company-A client-3 3
2016-June Company-A client-1 1
2016-June Company-A client-2 2
2016-June Company-A client-3 3


Is there a way to combine this data to get the chart located at the top of this post?

Additional info:
The goal is to pass the results of my query to the jasper report designer ireport. I'm using the data to create a crosstab with dates across the top and clients down the left side and putting the authentications in the cross rows.

I haven't touched sql since college years ago. Any thoughts or guidance would be appreciated.

Answer

I've worked my way from your last query and this seems to be producing what you need:

SELECT
    dateTable.mydate AS Date,
    clientTable.ClientName AS Client,
    clientTable.CompanyName AS Company,
    clientTable.client_id AS Client_ID,
    COUNT(rq.id) AS Authentications,
    COUNT(DISTINCT rq.personguid) AS Unique_Users  
FROM
(SELECT 
    client.name AS ClientName, 
    client.id AS client_id,
    company.name AS CompanyName 
    FROM company JOIN CLIENT ON company.id = client.company_id 
    WHERE company.id = 19) clientTable
JOIN (SELECT 
    DATE_FORMAT(temp.thedate,'%Y-%M') AS mydate, 
    temp.thedate AS theDate
    FROM (SELECT
            DATE_ADD("2016-05-01", INTERVAL u.i MONTH) AS thedate
            FROM i AS u ORDER BY thedate) temp
            WHERE temp.thedate <= "2016-06-01") dateTable
LEFT JOIN request_queue rq on (rq.clientid = clientTable.client_ID and DATE_FORMAT(rq.requestTime,'%Y-%M') = dateTable.mydate)
GROUP BY Client_ID, Date
ORDER BY Date ASC, Client_ID ASC;

Of course, this is the simplified version that does not take into account the request type and status, but I think you can easily work it from here.