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!
0 Comments
[ + ] Show comments
Answers (2)
Answer Summary:
Please log in to answer
Posted by:
chucksteel
6 years ago
Top 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,
SUM(CASE
when YEAR(SHIP_DATE) = 2018 THEN 1 ELSE 0
END) as "2018",
SUM(CASE
when YEAR(SHIP_DATE) = 2017 THEN 1 ELSE 0
END) as "2017",
SUM(CASE
when YEAR(SHIP_DATE) = 2016 THEN 1 ELSE 0
END) as "2016",
SUM(CASE
when YEAR(SHIP_DATE) < 2016 THEN 1 ELSE 0
END) as "< 2016"
FROM MACHINE
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
Posted by:
billyITguy
6 years ago
The answer by chucksteel was just what I needed. I also ended up running the below report for each of my needed labels to see all the devices in that label sorted by ship date.
select LABEL.NAME as LABEL_NAME, MACHINE.NAME as MACHINE_NAME, REPLACE(MACHINE.USER_LOGGED,'\\','\\\\') as USER_LOGGED, YEAR(SHIP_DATE), CS_MODEL
FROM MACHINE
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 = "class/lab"
ORDER BY YEAR(SHIP_DATE)