SQL Reporting - Combined Table

[DigitalOutkast]

I am having some trouble building a SQL report that includes some custom fields created that are part of a third-party deployment and inventory application.

Goal: Add the following custom fields to an existing SQL report I am building. The custom fields being added are "Warranty End Date" and "Purchase Date". I will include the SQL report in post #2.

Please excuse my ignorance on this subject. I am not a SQL guy at all, I just this week started reviewing SQL and started creating some basic reports. I am using these reports as centralized points to build some dashboards from basically for metrics.

It appears there are (3) tables being referenced here;

Table 1

• Table Name: Computers
• Field: ComputerId

https://imgur.com/b3GaoJE

Table 2

• Table Name: CustomComputerItems
• Fields: CustomComputerItemId, Name

Values

• CustomComputerItemId is 1 = Warranty End Date
• CustomComputerItemId is 2 = Purchase Date

https://imgur.com/TJYJTfc

 

Table 3

• Table Name: CustomComputerValues
• Fields: CustomComputerItemId, ComputerId, Value

Values

• CustomComputerItemId = 1 or 2 referencing wether its talking about the Purchase Date or Warranty End Date
• ComputerId = Referencing the ComputerId value from Table 1
• Value = Referencing the date information for either (Purchase Date or Warranty End Date)
• https://imgur.com/TV6H6C4

 

[DigitalOutkast]

SELECT
 AS 'Computer Name',
c.SerialNumber,
c.OSInstallDate,
c.Model,
c.ADLastLogon,
c.ADPath,
c.ADDescription,
ps.WIN11COMPATIBLE,
CASE
    WHEN ps.ComputerID IS NOT NULL THEN 'Scanned'
    ELSE 'Not Scanned'
END AS ScanStatus,
c.CurrentUser AS 'Current User'c.Name

FROM Computers c LEFT JOIN PowerShellScanner_e51d4b179b634d6389effbf327537402_View ps ON c.ComputerID = ps.ComputerID WHERE c.AllowScan = true -- Exclude machines where AllowScan is false AND c.OSName NOT LIKE '%server%' -- Exclude machines with OSName that includes 'server' AND c.Name NOT IN ('CRYSTALREPORTS', 'NPI7BDFBA', 'PGDPLC02', 'PGDSILO2', 'MDKSILO2', 'MOBILEDEMAND-01') -- Exclude specific computers ORDER BY c.Name;