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