I have a bit of code here that should return everything in Kace's sytem with an expired warranty. And yet, I receive a lot of double results.

For example, from the same machine (name, model, Service tag) I've received in the same report three entries for:

  •  7/21/2010 23:59
  •  7/21/2010 23:59
  •  7/21/2010 23:59

Here I see two doubles, but they shouldnt even be there if it was renewed, right? Is there any way to clean this up? I'm at a loss.

Here's the code. Thanks in advance!

 

SELECT 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)  
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());
 


 

Answer Summary:
See here: http://www.itninja.com/question/dell-expired-warranty-report-multiple-listing-for-each-machine-need-unique-only
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

0

See here: http://www.itninja.com/question/dell-expired-warranty-report-multiple-listing-for-each-machine-need-unique-only

Answered 12/21/2012 by: tshupp
Second Degree Black Belt

Please log in to comment
Answer this question or Comment on this question for clarity
Admin Script Editor
Admin Script Editor is an integrated scripting environment available free here at ITNinja

Share