/build/static/layout/Breadcrumb_cap_w.png

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!


0 Comments   [ + ] Show comments

Answers (1)

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
Posted by: jverbosk 11 years ago
Red Belt
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 11 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 11 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 11 years ago

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ