I'm trying to create a report that will include the ship date as one of the fields. How to I go about this? Below is my current script file
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
0 Comments
[ + ] Show comments
Answers (2)
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
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
Please log in to answer
Posted by:
dugullett
12 years ago
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
Comments:
-
Thanks, appreciate the assist since the answer was yours orginally. :-) - SMal.tmcc 12 years ago
-
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 - mnutbrown 12 years ago -
Are you able to remove the time from the ship date? - mnutbrown 12 years ago
-
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. - dugullett 12 years ago
Posted by:
SMal.tmcc
12 years ago
see dugullett's reply
http://www.itninja.com/question/report-of-machines-by-ship-date
Comments:
-
also other posts:
http://www.itninja.com/searchbeta?q=report+ship+date - SMal.tmcc 12 years ago -
I saw this response, I just wasn't sure how to add it into my current script - mnutbrown 12 years ago
-
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 - mnutbrown 12 years ago -
Are you able to remove the time from the ship date? - mnutbrown 12 years ago
-
That's prefect thank you! - mnutbrown 12 years ago
