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!

Answer Summary:
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
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

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

 

Answered 10/29/2012 by: jverbosk
Red Belt

  • This nailed it! Thank you so much for your help!
  • 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
  • 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.
Please log in to comment
Answer this question or Comment on this question for clarity