Search code examples
mysqlsqljasper-reportscrosstab

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.

Solution

  • 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.