Hi,

I'm trying to create a report similar to the report "Dell Warranty Expires in the next 30 Days". But in my report I would like add OS Name and SP version and sort it by ship date and remove Service Label Code, Service Level Description, and Service Provider. Also prevent duplicate machine names from appearing.

I get close but I still get dups when I use the below syntax. I try to proceed further by removing and attemping to add what I want but it fails: 

SELECT M.NAME AS MACHINE_NAME,M.CS_MODEL AS MODEL, OS_NAME, SERVICE_PACK, DA.SERVICE_TAG, SHIP_DATE, M.USER_LOGGED AS LAST_LOGGED_IN_USER, 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.SERVICE_TAG)
WHERE M.CS_MANUFACTURER LIKE '%dell%'
AND DA.DISABLED != 1
AND DW.END_DATE > NOW()
AND DW.END_DATE <= DATE_ADD(NOW(),INTERVAL 90 DAY)
AND DW.END_DATE = (SELECT MAX(END_DATE) FROM KBSYS.DELL_WARRANTY DW2 WHERE
ORDER BY SHIP_DATE;

 

The orignial syntax is this:

  

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.SERVICE_PROVIDER,
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.SERVICE_TAG OR M.BIOS_SERIAL_NUMBER = DA.PARENT_SERVICE_TAG)
WHERE M.CS_MANUFACTURER LIKE '%dell%'
AND M.BIOS_SERIAL_NUMBER!=''
AND DA.DISABLED != 1
AND DW.END_DATE > NOW()
AND DW.END_DATE <= DATE_ADD(NOW(),INTERVAL 60 DAY)
AND DW.END_DATE = (SELECT MAX(END_DATE) FROM KBSYS.DELL_WARRANTY DW2 WHERE
DW2.SERVICE_TAG=DW.SERVICE_TAG AND DW2.SERVICE_LEVEL_CODE=DW.SERVICE_LEVEL_CODE);

 

Here is an image where i'm trying to get at:

 

 

 

 

Thanks much!!

Kirk

 

 

Answer Summary:
SELECT DISTINCT M.NAME AS MACHINE_NAME, M.OS_NAME, M.SERVICE_PACK, M.CS_MODEL AS MODEL, DA.SERVICE_TAG, DA.SHIP_DATE, M.USER_LOGGED AS LAST_LOGGED_IN_USER, 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.SERVICE_TAG OR M.BIOS_SERIAL_NUMBER = DA.PARENT_SERVICE_TAG) WHERE M.CS_MANUFACTURER LIKE '%dell%' AND M.BIOS_SERIAL_NUMBER!='' AND DA.DISABLED != 1 AND DW.END_DATE > NOW() AND DW.END_DATE <= DATE_ADD(NOW(),INTERVAL 90 DAY) AND DW.END_DATE = (SELECT MAX(END_DATE) FROM KBSYS.DELL_WARRANTY DW2 WHERE DW2.SERVICE_TAG=DW.SERVICE_TAG AND DW2.SERVICE_LEVEL_CODE=DW.SERVICE_LEVEL_CODE) ORDER BY DA.SHIP_DATE
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

0

Something like this will work. The reason you are getting duplicates is more than likely there are multiple warranties for those machines (battery, extended, etc.).

SELECT DISTINCT M.NAME AS MACHINE_NAME, M.OS_NAME, M.SERVICE_PACK, M.CS_MODEL AS MODEL, DA.SERVICE_TAG, DA.SHIP_DATE, M.USER_LOGGED AS LAST_LOGGED_IN_USER,

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.SERVICE_TAG OR M.BIOS_SERIAL_NUMBER = DA.PARENT_SERVICE_TAG)

WHERE M.CS_MANUFACTURER LIKE '%dell%'

AND M.BIOS_SERIAL_NUMBER!=''

AND DA.DISABLED != 1

AND DW.END_DATE > NOW()

AND DW.END_DATE <= DATE_ADD(NOW(),INTERVAL 90 DAY)

AND DW.END_DATE = (SELECT MAX(END_DATE) FROM KBSYS.DELL_WARRANTY DW2 WHERE DW2.SERVICE_TAG=DW.SERVICE_TAG AND DW2.SERVICE_LEVEL_CODE=DW.SERVICE_LEVEL_CODE)

ORDER BY DA.SHIP_DATE
Answered 09/17/2012 by: dugullett
Red Belt

  • That works great! Now one more question. Our machines are located by location. Example of a computer name be BX1111. BX would mean it's in B Company. How would I pull out the machines only containing BX. Thanks again for your help! My head is about to burst. :)
    • You want to add where M.NAME LIKE 'BX%'. % is a wildcard in SQL so anything that starts with BX will be covered.

      The same would work for M.OS_NAME LIKE 'MICROSOFT WINDOWS 7%'. This would cover all Windows 7 versions.

      SELECT DISTINCT M.NAME AS MACHINE_NAME, M.OS_NAME, M.SERVICE_PACK, M.CS_MODEL AS MODEL, DA.SERVICE_TAG, DA.SHIP_DATE, M.USER_LOGGED AS LAST_LOGGED_IN_USER,

      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.SERVICE_TAG OR M.BIOS_SERIAL_NUMBER = DA.PARENT_SERVICE_TAG)

      WHERE M.CS_MANUFACTURER LIKE '%dell%' AND M.NAME LIKE 'BX%'

      AND M.BIOS_SERIAL_NUMBER!=''

      AND DA.DISABLED != 1

      AND DW.END_DATE > NOW()

      AND DW.END_DATE <= DATE_ADD(NOW(),INTERVAL 90 DAY)

      AND DW.END_DATE = (SELECT MAX(END_DATE) FROM KBSYS.DELL_WARRANTY DW2 WHERE DW2.SERVICE_TAG=DW.SERVICE_TAG AND DW2.SERVICE_LEVEL_CODE=DW.SERVICE_LEVEL_CODE)

      ORDER BY DA.SHIP_DATE


      Also can you tag "K1000 reporting" so that others can find this easier later?
  • That worked perfectly. Thanks again for your help and I've tagged it, per your suggestion. Thanks!
Please log in to comment
Answer this question or Comment on this question for clarity