/build/static/layout/Breadcrumb_cap_w.png

Miscellaneous Question


Run a report with Ship date by Label

03/09/2018 1058 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!
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

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ