Verified Answers

If an answer to your question is correct, click on "Verify Answer" under the "More" button.  The answer will now appear with a checkmark.  Please be sure to always mark answers that resolve your issue as verified.  Your fellow Community members will appreciate it!  Learn more.

  • State Suggested Answer
  • Replies reply
    1
  • Answers answer
    1
  • Subscribers subscribers
    8
  • Views views
    355
  • Users 0 members are here
  • Locked Locked
Related
Options
This discussion has been locked.
You can no longer post new replies to this discussion. If you have a question you can start a new discussion

SQL Query to Count Total Assets Limited to Visual Test & .NET

garecanbp

I used this query to count Total Assets per project from the database:

Select Count(TPNAME) as "Total Asset", TPASSOCIATEDPROJECTID as "Project ID"

From STW_ASSETMAIN

Group By TPASSOCIATEDPROJECTID

Now I need to limit the assets to Visual Test & .NET only, Could I know how to do that?

In the table design, there is TPOBJECTTYPE but its type is Integer and it doesn't have a relation with another database. 

  • Suggested Answer

    0  

    Hi

    The following query may be of use:

    SELECT TPNAME, COUNT(TPNAME)-1 As Versions, TPASSOCIATEDPROJECTID,
    CASE
        WHEN TPOBJECTTYPE=42 THEN 'Visual Test'
        WHEN TPOBJECTTYPE=43 THEN 'Result'
        WHEN TPOBJECTTYPE=44 THEN '.Net Script'
    END AS AssetType
    FROM STW_ASSETMAIN
    WHERE TPOBJECTTYPE IN (42, 43, 44)
    GROUP BY TPNAME, TPASSOCIATEDPROJECTID, TPOBJECTTYPE

    You should be able to modify this to meet your needs.

    -Robert

Resources

Support
Documentation
Learning Services
Partner Programs
Privacy
Compliance
Help
Company
Privacy Policy
Terms of Use
Cookies Preferences
Accessibility
Anti-Slavery Statement
Support
Contact Us
Careers
Code of Business Conduct and Ethics
The opinions expressed above are the personal opinions of the authors, not of OpenText. By using this site, you accept the Terms of Use. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.), Hewlett Packard Enterprise Company, or Micro Focus. As of January 31, 2023, the Material is now offered by OpenText, a separately owned and operated company. Any reference to the HP, Hewlett Packard Enterprise/HPE, and Micro Focus marks is historical in nature and the HP, Hewlett Packard Enterprise/HPE, and Micro Focus marks are the property of their respective owners.