Can you sort a warranty report by ship date ?
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.
0 Comments
[ + ] Show comments
Answers (1)
Answer Summary:
Please log in to answer
Posted by:
chucksteel
9 years ago
Top Answer
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
