Need Assistance with SQL for a Report
Hey folks,
I have a report I'm trying to generate that shows our software compliance and if we're out of compliance how much it will cost to get back into compliance. I was able to use the report wizard to greating most of what I need, however I don't know SQL and when I look at the code I feel like I'm staring at a foreign language. So I was hoping there were a few SQL experts out there whom might be able to help translate for me.
Right now this is what my code looks like:
SELECT A2.NAME AS FIELD_2,ASSET_DATA_7.FIELD_1 AS FIELD_1,(SELECT COUNT(DISTINCT MACHINE_SOFTWARE_JT.MACHINE_ID) FROM ASSET_ASSOCIATION JX2
LEFT JOIN ASSET AX2 ON AX2.ID = JX2.ASSOCIATED_ASSET_ID
LEFT JOIN MACHINE_SOFTWARE_JT ON (SOFTWARE_ID = AX2.MAPPED_ID OR SOFTWARE_ID IN
(SELECT SOFTWARE_ID FROM ASSET_DATA_6 JOIN SOFTWARE_LABEL_JT
ON LABEL_ID = ASSET_DATA_6.FIELD_10001
WHERE ASSET_DATA_6.ID = AX2.ASSET_DATA_ID))
WHERE JX2.ASSET_ID = ASSET.ID AND JX2.ASSET_FIELD_ID=2
) AS INSTALLED_COUNT,(ASSET_DATA_7.FIELD_1 - (SELECT COUNT(DISTINCT MACHINE_SOFTWARE_JT.MACHINE_ID) FROM ASSET_ASSOCIATION JX2
LEFT JOIN ASSET AX2 ON AX2.ID = JX2.ASSOCIATED_ASSET_ID
LEFT JOIN MACHINE_SOFTWARE_JT ON (SOFTWARE_ID = AX2.MAPPED_ID OR SOFTWARE_ID IN
(SELECT SOFTWARE_ID FROM ASSET_DATA_6 JOIN SOFTWARE_LABEL_JT
ON LABEL_ID = ASSET_DATA_6.FIELD_10001
WHERE ASSET_DATA_6.ID = AX2.ASSET_DATA_ID))
WHERE JX2.ASSET_ID = ASSET.ID AND JX2.ASSET_FIELD_ID=2
)) AS REMAINING_COUNT,ASSET_DATA_7.FIELD_6 AS FIELD_6 FROM ASSET_DATA_7 LEFT JOIN ASSET ON ASSET_DATA_7.ID = ASSET.ASSET_DATA_ID AND ASSET.ASSET_TYPE_ID=7 LEFT JOIN ASSET_ASSOCIATION J2 ON J2.ASSET_ID = ASSET.ID AND J2.ASSET_FIELD_ID=2
LEFT JOIN ASSET A2 ON A2.ID = J2.ASSOCIATED_ASSET_ID
LEFT JOIN ASSET_DATA_6 AD2 ON AD2.ID = A2.ASSET_DATA_ID
ORDER BY FIELD_2
And it returns this:
Looks good and it's almost there, but what I was wanting to do would be to add another column that is "Compliance Cost" and would return "In Compliance" or "0" (if that's easier) if there are still seats remaining, and if there aren't any seats left I'd want it to return the cost of getting it back into compliance. (Seats remaining * Unit Cost). I ran a test with a license asset out of seats on purpose to see what it looks like and it returns a negative number. I don't know if that tidbit of info would help or not, but I thought I should throw it in there.
So! If at all possible I'd love some help from the ITninja gurus, as all I've been able to do is break it over and over.
Thanks!
Answers (1)
The code below is the answer to this question SELECT A2.NAME AS FIELD_2, ASSET_DATA_7.FIELD_1 AS FIELD_1, (SELECT COUNT(DISTINCT MACHINE_SOFTWARE_JT.MACHINE_ID) FROM ASSET_ASSOCIATION JX2 LEFT JOIN ASSET AX2 ON AX2.ID = JX2.ASSOCIATED_ASSET_ID LEFT JOIN MACHINE_SOFTWARE_JT ON (SOFTWARE_ID = AX2.MAPPED_ID OR SOFTWARE_ID IN (SELECT SOFTWARE_ID FROM ASSET_DATA_6 JOIN SOFTWARE_LABEL_JT ON LABEL_ID = ASSET_DATA_6.FIELD_10001 WHERE ASSET_DATA_6.ID = AX2.ASSET_DATA_ID)) WHERE JX2.ASSET_ID = ASSET.ID AND JX2.ASSET_FIELD_ID=2) AS INSTALLED_COUNT, (ASSET_DATA_7.FIELD_1 - (SELECT COUNT(DISTINCT MACHINE_SOFTWARE_JT.MACHINE_ID) FROM ASSET_ASSOCIATION JX2 LEFT JOIN ASSET AX2 ON AX2.ID = JX2.ASSOCIATED_ASSET_ID LEFT JOIN MACHINE_SOFTWARE_JT ON (SOFTWARE_ID = AX2.MAPPED_ID OR SOFTWARE_ID IN (SELECT SOFTWARE_ID FROM ASSET_DATA_6 JOIN SOFTWARE_LABEL_JT ON LABEL_ID = ASSET_DATA_6.FIELD_10001 WHERE ASSET_DATA_6.ID = AX2.ASSET_DATA_ID)) WHERE JX2.ASSET_ID = ASSET.ID AND JX2.ASSET_FIELD_ID=2)) AS REMAINING_COUNT, ASSET_DATA_7.FIELD_6 AS FIELD_6, CASE WHEN ((ASSET_DATA_7.FIELD_1 - (SELECT COUNT(DISTINCT MACHINE_SOFTWARE_JT.MACHINE_ID) FROM ASSET_ASSOCIATION JX2 LEFT JOIN ASSET AX2 ON AX2.ID = JX2.ASSOCIATED_ASSET_ID LEFT JOIN MACHINE_SOFTWARE_JT ON (SOFTWARE_ID = AX2.MAPPED_ID OR SOFTWARE_ID IN (SELECT SOFTWARE_ID FROM ASSET_DATA_6 JOIN SOFTWARE_LABEL_JT ON LABEL_ID = ASSET_DATA_6.FIELD_10001 WHERE ASSET_DATA_6.ID = AX2.ASSET_DATA_ID)) WHERE JX2.ASSET_ID = ASSET.ID AND JX2.ASSET_FIELD_ID=2)) >= 0) THEN 'In Compliance' WHEN ((ASSET_DATA_7.FIELD_1 - (SELECT COUNT(DISTINCT MACHINE_SOFTWARE_JT.MACHINE_ID) FROM ASSET_ASSOCIATION JX2 LEFT JOIN ASSET AX2 ON AX2.ID = JX2.ASSOCIATED_ASSET_ID LEFT JOIN MACHINE_SOFTWARE_JT ON (SOFTWARE_ID = AX2.MAPPED_ID OR SOFTWARE_ID IN (SELECT SOFTWARE_ID FROM ASSET_DATA_6 JOIN SOFTWARE_LABEL_JT ON LABEL_ID = ASSET_DATA_6.FIELD_10001 WHERE ASSET_DATA_6.ID = AX2.ASSET_DATA_ID)) WHERE JX2.ASSET_ID = ASSET.ID AND JX2.ASSET_FIELD_ID=2)) < 0) THEN (((SELECT COUNT(DISTINCT MACHINE_SOFTWARE_JT.MACHINE_ID) FROM ASSET_ASSOCIATION JX2 LEFT JOIN ASSET AX2 ON AX2.ID = JX2.ASSOCIATED_ASSET_ID LEFT JOIN MACHINE_SOFTWARE_JT ON (SOFTWARE_ID = AX2.MAPPED_ID OR SOFTWARE_ID IN (SELECT SOFTWARE_ID FROM ASSET_DATA_6 JOIN SOFTWARE_LABEL_JT ON LABEL_ID = ASSET_DATA_6.FIELD_10001 WHERE ASSET_DATA_6.ID = AX2.ASSET_DATA_ID)) WHERE JX2.ASSET_ID = ASSET.ID AND JX2.ASSET_FIELD_ID=2) - ASSET_DATA_7.FIELD_1) * ASSET_DATA_7.FIELD_6) ELSE 'Not Installed' END AS COMPLIANCE_COST FROM ASSET_DATA_7 LEFT JOIN ASSET ON ASSET_DATA_7.ID = ASSET.ASSET_DATA_ID AND ASSET.ASSET_TYPE_ID=7 LEFT JOIN ASSET_ASSOCIATION J2 ON J2.ASSET_ID = ASSET.ID AND J2.ASSET_FIELD_ID=2 LEFT JOIN ASSET A2 ON A2.ID = J2.ASSOCIATED_ASSET_ID LEFT JOIN ASSET_DATA_6 AD2 ON AD2.ID = A2.ASSET_DATA_ID ORDER BY FIELD_2
This could probably be condensed considerably, but without access to your tables and playing around I don't know how successful that version would be. Regardless, hopefully this is a (long) step in the right direction - I tried to break up some of the statements so they are a little easier to follow. If you'd like a primer on SQL, search on my name and/or SQL primer.
Hope this works! ^_^
John
________________________
SELECT A2.NAME AS FIELD_2,
ASSET_DATA_7.FIELD_1 AS FIELD_1,
(SELECT COUNT(DISTINCT MACHINE_SOFTWARE_JT.MACHINE_ID)
FROM ASSET_ASSOCIATION JX2
LEFT JOIN ASSET AX2 ON AX2.ID = JX2.ASSOCIATED_ASSET_ID
LEFT JOIN MACHINE_SOFTWARE_JT ON
(SOFTWARE_ID = AX2.MAPPED_ID OR SOFTWARE_ID IN
(SELECT SOFTWARE_ID
FROM ASSET_DATA_6
JOIN SOFTWARE_LABEL_JT ON LABEL_ID = ASSET_DATA_6.FIELD_10001
WHERE ASSET_DATA_6.ID = AX2.ASSET_DATA_ID))
WHERE JX2.ASSET_ID = ASSET.ID
AND JX2.ASSET_FIELD_ID=2) AS INSTALLED_COUNT,
(ASSET_DATA_7.FIELD_1 -
(SELECT COUNT(DISTINCT MACHINE_SOFTWARE_JT.MACHINE_ID)
FROM ASSET_ASSOCIATION JX2
LEFT JOIN ASSET AX2 ON AX2.ID = JX2.ASSOCIATED_ASSET_ID
LEFT JOIN MACHINE_SOFTWARE_JT ON
(SOFTWARE_ID = AX2.MAPPED_ID OR SOFTWARE_ID IN
(SELECT SOFTWARE_ID
FROM ASSET_DATA_6
JOIN SOFTWARE_LABEL_JT ON LABEL_ID = ASSET_DATA_6.FIELD_10001
WHERE ASSET_DATA_6.ID = AX2.ASSET_DATA_ID))
WHERE JX2.ASSET_ID = ASSET.ID
AND JX2.ASSET_FIELD_ID=2)) AS REMAINING_COUNT,
ASSET_DATA_7.FIELD_6 AS FIELD_6,
CASE
WHEN
((ASSET_DATA_7.FIELD_1 -
(SELECT COUNT(DISTINCT MACHINE_SOFTWARE_JT.MACHINE_ID)
FROM ASSET_ASSOCIATION JX2
LEFT JOIN ASSET AX2 ON AX2.ID = JX2.ASSOCIATED_ASSET_ID
LEFT JOIN MACHINE_SOFTWARE_JT ON
(SOFTWARE_ID = AX2.MAPPED_ID OR SOFTWARE_ID IN
(SELECT SOFTWARE_ID
FROM ASSET_DATA_6
JOIN SOFTWARE_LABEL_JT ON LABEL_ID = ASSET_DATA_6.FIELD_10001
WHERE ASSET_DATA_6.ID = AX2.ASSET_DATA_ID))
WHERE JX2.ASSET_ID = ASSET.ID
AND JX2.ASSET_FIELD_ID=2)) >= 0)
THEN 'In Compliance'
WHEN
((ASSET_DATA_7.FIELD_1 -
(SELECT COUNT(DISTINCT MACHINE_SOFTWARE_JT.MACHINE_ID)
FROM ASSET_ASSOCIATION JX2
LEFT JOIN ASSET AX2 ON AX2.ID = JX2.ASSOCIATED_ASSET_ID
LEFT JOIN MACHINE_SOFTWARE_JT ON
(SOFTWARE_ID = AX2.MAPPED_ID OR SOFTWARE_ID IN
(SELECT SOFTWARE_ID
FROM ASSET_DATA_6
JOIN SOFTWARE_LABEL_JT ON LABEL_ID = ASSET_DATA_6.FIELD_10001
WHERE ASSET_DATA_6.ID = AX2.ASSET_DATA_ID))
WHERE JX2.ASSET_ID = ASSET.ID
AND JX2.ASSET_FIELD_ID=2)) < 0)
THEN
(((SELECT COUNT(DISTINCT MACHINE_SOFTWARE_JT.MACHINE_ID)
FROM ASSET_ASSOCIATION JX2
LEFT JOIN ASSET AX2 ON AX2.ID = JX2.ASSOCIATED_ASSET_ID
LEFT JOIN MACHINE_SOFTWARE_JT ON
(SOFTWARE_ID = AX2.MAPPED_ID OR SOFTWARE_ID IN
(SELECT SOFTWARE_ID
FROM ASSET_DATA_6
JOIN SOFTWARE_LABEL_JT ON LABEL_ID = ASSET_DATA_6.FIELD_10001
WHERE ASSET_DATA_6.ID = AX2.ASSET_DATA_ID))
WHERE JX2.ASSET_ID = ASSET.ID
AND JX2.ASSET_FIELD_ID=2) - ASSET_DATA_7.FIELD_1) * ASSET_DATA_7.FIELD_6)
ELSE 'Not Installed'
END AS COMPLIANCE_COST
FROM ASSET_DATA_7
LEFT JOIN ASSET ON ASSET_DATA_7.ID = ASSET.ASSET_DATA_ID AND ASSET.ASSET_TYPE_ID=7
LEFT JOIN ASSET_ASSOCIATION J2 ON J2.ASSET_ID = ASSET.ID AND J2.ASSET_FIELD_ID=2
LEFT JOIN ASSET A2 ON A2.ID = J2.ASSOCIATED_ASSET_ID
LEFT JOIN ASSET_DATA_6 AD2 ON AD2.ID = A2.ASSET_DATA_ID
ORDER BY FIELD_2
Comments:
-
This nailed it! Thank you so much for your help! - samzeeco 12 years ago
-
Glad to hear it! ^_^
If you want to understand how queries are constructed, check this out:
http://www.itninja.com/blog/view/primer-for-writing-select-statement-queries-on-the-k1000-w-custom-sql-report-example
Your query was a bit more complex with subqueries, but otherwise it was pretty straightforward (at least once I reorganized it).
John - jverbosk 12 years ago -
I'll definitely do so! I appreciate the link. I was hoping it wouldn't be too difficult because it was one I was able to generate with the report wizard. But hearing that makes me extra grateful for the assistance. I definitely want to get to the point where I can understand enough to help my company with Reports. - samzeeco 12 years ago
