computers with dell ship and warranty dates.sql

-- Pulls data from Dell asset tables
-- Uses maximum warranty end date as some machines have multiple warranties
 
SELECT DISTINCT(MACHINE.NAME),
MACHINE.OS_NAME,
MACHINE.CS_MODEL,
MACHINE.CS_MANUFACTURER,
DA.SHIP_DATE AS "Ship Date",
MAX(DW.END_DATE) AS "Warranty End Date",
DW.SERVICE_LEVEL_DESCRIPTION as "Service Level"
FROM MACHINE
LEFT JOIN DELL_ASSET DA on MACHINE.BIOS_SERIAL_NUMBER = DA.SERVICE_TAG
LEFT JOIN DELL_WARRANTY DW on MACHINE.BIOS_SERIAL_NUMBER = DW.SERVICE_TAG
WHERE MACHINE.CS_MANUFACTURER like 'Dell%'
GROUP BY MACHINE.NAME
ORDER BY `Warranty End Date`, MACHINE.NAME