SELECT A20.NAME AS FIELD_20,ASSET_DATA_5.FIELD_23,MACHINE.NAME AS SYSTEM_NAME,CS_MODEL,CHASSIS_TYPE,RAM_TOTAL,OS_NAME,SERVICE_PACK,BIOS_SERIAL_NUMBER FROM MACHINE  LEFT JOIN ASSET ON ASSET.MAPPED_ID = MACHINE.ID AND ASSET.ASSET_TYPE_ID=5 LEFT JOIN ASSET_ASSOCIATION J20 ON J20.ASSET_ID = ASSET.ID AND J20.ASSET_FIELD_ID=20
                                 LEFT JOIN ASSET A20 ON A20.ID = J20.ASSOCIATED_ASSET_ID LEFT JOIN ASSET_DATA_5 ON ASSET_DATA_5.ID = ASSET.ASSET_DATA_ID WHERE (A20.NAME != '')  ORDER BY FIELD_20,FIELD_23

Answer Summary:
SELECT A20.NAME AS FIELD_20,ASSET_DATA_5.FIELD_23,MACHINE.NAME AS SYSTEM_NAME,CS_MODEL,CHASSIS_TYPE,RAM_TOTAL,OS_NAME,SERVICE_PACK,BIOS_SERIAL_NUMBER ,D.SHIP_DATE FROM MACHINE LEFT JOIN ASSET ON ASSET.MAPPED_ID = MACHINE.ID AND ASSET.ASSET_TYPE_ID=5 LEFT JOIN ASSET_ASSOCIATION J20 ON J20.ASSET_ID = ASSET.ID AND J20.ASSET_FIELD_ID=20 LEFT JOIN ASSET A20 ON A20.ID = J20.ASSOCIATED_ASSET_ID LEFT JOIN ASSET_DATA_5 ON ASSET_DATA_5.ID = ASSET.ASSET_DATA_ID JOIN KBSYS.DELL_ASSET D ON D.SERVICE_TAG = M.BIOS_SERIAL_NUMBER WHERE (A20.NAME != '') ORDER BY FIELD_20,FIELD_23
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Community Chosen Answer

3

Add the lines in bold. This should work for you. Also, I would change the column name with the AS command. So instead of using A20.NAME AS FIELD_20 use something like A20.NAME AS 'Asset 1 Name'

SELECT A20.NAME AS FIELD_20,ASSET_DATA_5.FIELD_23,MACHINE.NAME AS SYSTEM_NAME,CS_MODEL,CHASSIS_TYPE,RAM_TOTAL,OS_NAME,SERVICE_PACK,BIOS_SERIAL_NUMBER

,D.SHIP_DATE

FROM MACHINE

LEFT JOIN ASSET ON ASSET.MAPPED_ID = MACHINE.ID AND ASSET.ASSET_TYPE_ID=5

LEFT JOIN ASSET_ASSOCIATION J20 ON J20.ASSET_ID = ASSET.ID AND J20.ASSET_FIELD_ID=20

LEFT JOIN ASSET A20 ON A20.ID = J20.ASSOCIATED_ASSET_ID LEFT JOIN ASSET_DATA_5 ON ASSET_DATA_5.ID = ASSET.ASSET_DATA_ID

JOIN KBSYS.DELL_ASSET D ON D.SERVICE_TAG = M.BIOS_SERIAL_NUMBER

WHERE (A20.NAME != '')

ORDER BY FIELD_20,FIELD_23
Answered 11/05/2012 by: dugullett
Red Belt

  • Thanks, appreciate the assist since the answer was yours orginally. :-)
  • Thanks dugullet Got this to work

    SELECT A20.NAME AS FIELD_20,ASSET_DATA_5.FIELD_23,MACHINE.NAME AS
    SYSTEM_NAME,CS_MODEL,CHASSIS_TYPE,RAM_TOTAL,OS_NAME,SERVICE_PACK,BIOS_SERIAL_NUMBER,D.SHIP_DATE
    FROM MACHINE
    LEFT JOIN ASSET ON ASSET.MAPPED_ID = MACHINE.ID AND ASSET.ASSET_TYPE_ID=5
    LEFT JOIN ASSET_ASSOCIATION J20 ON J20.ASSET_ID = ASSET.ID AND J20.ASSET_FIELD_ID=20
    LEFT JOIN ASSET A20 ON A20.ID = J20.ASSOCIATED_ASSET_ID
    LEFT JOIN ASSET_DATA_5 ON ASSET_DATA_5.ID = ASSET.ASSET_DATA_ID
    JOIN KBSYS.DELL_ASSET D ON D.SERVICE_TAG = BIOS_SERIAL_NUMBER
    WHERE (A20.NAME != '')
    ORDER BY FIELD_20,FIELD_23
  • Are you able to remove the time from the ship date?
    • There are different ways to format it based on how you want the output. Do a search for DATE_FORMAT. More on that can be found here http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-format

      SELECT A20.NAME AS FIELD_20,ASSET_DATA_5.FIELD_23,MACHINE.NAME AS
      SYSTEM_NAME,CS_MODEL,CHASSIS_TYPE,RAM_TOTAL,OS_NAME,SERVICE_PACK,BIOS_SERIAL_NUMBER,
      DATE_FORMAT (D.SHIP_DATE, '%M %D, %Y') AS 'SHIP DATE'
      FROM MACHINE
      LEFT JOIN ASSET ON ASSET.MAPPED_ID = MACHINE.ID AND ASSET.ASSET_TYPE_ID=5
      LEFT JOIN ASSET_ASSOCIATION J20 ON J20.ASSET_ID = ASSET.ID AND J20.ASSET_FIELD_ID=20
      LEFT JOIN ASSET A20 ON A20.ID = J20.ASSOCIATED_ASSET_ID
      LEFT JOIN ASSET_DATA_5 ON ASSET_DATA_5.ID = ASSET.ASSET_DATA_ID
      JOIN KBSYS.DELL_ASSET D ON D.SERVICE_TAG = BIOS_SERIAL_NUMBER
      WHERE (A20.NAME != '')
      ORDER BY FIELD_20,FIELD_23

      This will give you an output of Month Day, Year.
Please log in to comment

Answers

0
Answered 11/05/2012 by: SMal.tmcc
Red Belt

  • also other posts:
    http://www.itninja.com/searchbeta?q=report+ship+date
  • I saw this response, I just wasn't sure how to add it into my current script
  • SELECT A20.NAME AS FIELD_20,ASSET_DATA_5.FIELD_23,MACHINE.NAME, D.SHIP_DATE AS
    SYSTEM_NAME,CS_MODEL,CHASSIS_TYPE,RAM_TOTAL,OS_NAME,SERVICE_PACK,BIOS_SERIAL_
    NUMBER FROM MACHINE LEFT JOIN ASSET ON ASSET.MAPPED_ID = MACHINE.ID AND
    ASSET.ASSET_TYPE_ID=5 LEFT JOIN ASSET_ASSOCIATION J20 ON J20.ASSET_ID = ASSET.ID
    AND J20.ASSET_FIELD_ID=20 LEFT JOIN KBSYS.DELL_ASSET D ON D.SERVICE_TAG = M.BIOS_SERIAL_NUMBER
    LEFT JOIN ASSET A20 ON A20.ID = J20.ASSOCIATED_ASSET_ID LEFT JOIN
    ASSET_DATA_5 ON ASSET_DATA_5.ID = ASSET.ASSET_DATA_ID WHERE (A20.NAME != '')
    ORDER BY FIELD_20,FIELD_23

    It doesn't like the M.BIOS_SERIAL_NUMBER part
  • This content is currently hidden from public view.
    Reason: Removed by user request
    For more information, visit our FAQ's.
  • Are you able to remove the time from the ship date?
  • That's prefect thank you!
Please log in to comment
Answer this question or Comment on this question for clarity