Run a report with Ship date by Label
03/09/2018 921 views
Hey Guys, Sorry I don't know SQL and am trying to figure out how to run a report.
I have all machines sorted into labels showing what its function is, such as staff, labs, loaner, etc. I need to run a report showing the number of machines in each label sorted by the year in the dell ship date. I've attached a picture of a sample report to make it more clear what I am trying to do. I figure I will need to do the macs manually, which is fine. If I can run a single report with the information for all the labels that would be great, but if we have to run a separate report for each label I don't combining the data manually.
Any help would be very much appreciated!!! Thanks!
Answer Chosen by the Author
Please log in to answer
If you place all of your desired labels into a label group, you can use this query:
SELECT LABEL.NAME as "Label", COUNT(MACHINE.NAME) as Count,
when YEAR(SHIP_DATE) = 2018 THEN 1 ELSE 0
END) as "2018",
when YEAR(SHIP_DATE) = 2017 THEN 1 ELSE 0
END) as "2017",
when YEAR(SHIP_DATE) = 2016 THEN 1 ELSE 0
END) as "2016",
when YEAR(SHIP_DATE) < 2016 THEN 1 ELSE 0
END) as "< 2016"
LEFT JOIN DELL_ASSET DA on MACHINE.BIOS_SERIAL_NUMBER = DA.SERVICE_TAG
JOIN MACHINE_LABEL_JT on MACHINE_LABEL_JT.MACHINE_ID = MACHINE.ID
JOIN LABEL on LABEL.ID = MACHINE_LABEL_JT.LABEL_ID
JOIN LABEL_LABEL_JT on LABEL.ID = LABEL_LABEL_JT.CHILD_LABEL_ID
JOIN LABEL PARENT on PARENT.ID = LABEL_LABEL_JT.LABEL_ID
WHERE MACHINE.CS_MANUFACTURER like 'Dell%'
and PARENT.NAME = "Label Group Name"
GROUP BY LABEL.NAME
ORDER BY LABEL.NAME
Answered 03/19/2018 by: chucksteel