/bundles/itninjaweb/img/Breadcrumb_cap_w.png
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!
yFRWio.jpeg
Answer Summary:
0 Comments   [ + ] Show comments

Comments


Answer Chosen by the Author

0
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

Answered 03/19/2018 by: chucksteel
Red Belt

All Answers

0
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)
Answered 04/02/2018 by: billyITguy
Senior White Belt