Hello all,
I was needing a report that lists pcs and servers with their ship date arranged by IP address. I cant select the ship date without doing a SQL type report. Could anybody lend a hand with some SQL code?
Thanks in advance,
Terry
Answer Summary:
SELECT M.NAME, DA.SERVICE_TAG, DATE_FORMAT(DA.SHIP_DATE,'%Y/%m/%d')AS SHIP_DATE, M.IP FROM KBSYS.DELL_ASSET DA, MACHINE M WHERE DA.SERVICE_TAG = M.BIOS_SERIAL_NUMBER ORDER BY INET_ATON(IP)
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Community Chosen Answer

4
Is this what your looking for?

SELECT M.NAME,
DA.SERVICE_TAG,
DATE_FORMAT(DA.SHIP_DATE,'%Y/%m/%d')AS SHIP_DATE,
M.IP
FROM KBSYS.DELL_ASSET DA,
MACHINE M
WHERE DA.SERVICE_TAG = M.BIOS_SERIAL_NUMBER
ORDER BY INET_ATON(IP)
Answered 12/30/2011 by: dchristian
Red Belt

  • I took this same code and modified it a little so that it looks like this:

    SELECT USER_FULLNAME,M.NAME AS COMPUTER_NAME,DA.SERVICE_TAG,DATE_FORMAT(DA.SHIP_DATE,'%Y/%m/%d')AS SHIP_DATE FROM KBSYS.DELL_ASSET DA, MACHINE M WHERE DA.SERVICE_TAG = M.BIOS_SERIAL_NUMBER ORDER BY INET_ATON(IP)

    I know NOTHING about sql code, but looking over yours i was able to figure out 90% of what I need. The only other thing i need that I cant figure out is I would like for it to only show (by ship date) PC's that are 3 years old and older. I tried to do:

    WHERE DA.SHIP_DATE >= '1095' but that didnt work at all. Help please!!
    • I also tried this and it's giving me pc's older than 3 years:

      WHERE DA.SHIP_DATE < (CURRENT_DATE - INTERVAL 3 YEAR)

      But it's reporting all pcs with one service tag and then it does all the pc's with the next service tag and so on. :/
Please log in to comment
Rating comments in this legacy AppDeploy message board thread won't reorder them,
so that the conversation will remain readable.

Answers

0
That's what I needed. Thanks!
Answered 12/30/2011 by: tld212s
Senior Yellow Belt

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