Hi all, I'm trying to create a report that list all my desktop by shipping date. Here is where I got so far:

SELECT DISTINCT(MACHINE.NAME),
MACHINE.CS_MODEL, 
MACHINE.CS_MANUFACTURER, 
DA.SHIP_DATE AS "Ship Date",
MAX(DW.END_DATE) AS "Warranty End Date",
DW.SERVICE_LEVEL_DESCRIPTION as "Service Level"
FROM MACHINE
LEFT JOIN DELL_ASSET DA on MACHINE.BIOS_SERIAL_NUMBER = DA.SERVICE_TAG
LEFT JOIN DELL_WARRANTY DW on MACHINE.BIOS_SERIAL_NUMBER = DW.SERVICE_TAG
WHERE MACHINE.CHASSIS_TYPE = "Desktop"
GROUP BY MACHINE.NAME
ORDER BY "Ship Date", MACHINE.NAME

Already tried: GROUP BY "Ship Date" but it only give me 1 desktop.

Another thing is when I run the report in KACE, the date appear like this: 06/19/2015 00:00:00 but if I run the querry in MySQL Workbench it goes like this: 2015-06-19 00:00:00 wich would be perfect for sorting in excel.

Thanks in advance for your help.
Answer Summary:
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answer Chosen by the Author

0
You can't order by an alias, so use ORDER BY DA.SHIP_DATE instead.

I'm not sure why the dates appear differently when you run your report in KACE and MySQL Workbench, but if you want to force the format you can use the date_format function:
http://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_date-format

Answered 11/30/2015 by: chucksteel
Red Belt

Please log in to comment

Answers

Answer this question or Comment on this question for clarity
Admin Script Editor
Admin Script Editor is an integrated scripting environment available free here at ITNinja

Share