Run a report with Ship date by Label
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
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
Please log in to comment
log in to commentPlease