I want to add to this report that only shows me expired warranties for the following:

Jan 1. 2010-Jan 31 2012
Fac/Staff Label Only

I have been trying to add what I think should be in the current report SQL statement and it wont' work.
Any help would be appreciated

Thanks

0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

Rating comments in this legacy AppDeploy message board thread won't reorder them,
so that the conversation will remain readable.
1

Hey Scarpent,

Is this what your looking for?

SELECT DISTINCT
M. NAME AS MACHINE_NAME,
M.CS_MODEL AS MODEL,
DA.SERVICE_TAG,
DA.SHIP_DATE,
M.USER_LOGGED AS LAST_LOGGED_IN_USER,
DW.SERVICE_LEVEL_CODE,
DW.SERVICE_LEVEL_DESCRIPTION,
DW.END_DATE AS EXPIRATION_DATE
FROM
KBSYS.DELL_WARRANTY DW
LEFT JOIN KBSYS.DELL_ASSET DA ON (
DW.SERVICE_TAG = DA.SERVICE_TAG
)
LEFT JOIN MACHINE M ON (
M.BIOS_SERIAL_NUMBER = DA.PARENT_SERVICE_TAG
OR M.BIOS_SERIAL_NUMBER = DA.SERVICE_TAG
)
LEFT JOIN MACHINE_LABEL_JT ML ON (M.ID = ML.MACHINE_ID)
LEFT JOIN LABEL L ON (ML.LABEL_ID = L.ID)
WHERE
M.CS_MANUFACTURER LIKE '%dell%'
AND M.BIOS_SERIAL_NUMBER != '
AND DA.DISABLED != 1
AND DW.END_DATE < NOW()
AND DW.SERVICE_TAG NOT IN (
SELECT
SERVICE_TAG
FROM
KBSYS.DELL_WARRANTY
WHERE
END_DATE > NOW()
)
AND DW.END_DATE > '2010-01-01' -- START DATE
AND DW.END_DATE < '2012-01-31' -- END DATE
AND L.`NAME` IN ('WORKSTATIONS') -- LABEL NAME HERE

Answered 03/05/2012 by: dchristian
Red Belt

Please log in to comment
Answer this question or Comment on this question for clarity