Creating a Report using SQL
I am not a SQL person nor do I have one on staff. I normally use the Wizard when creating reports, but I can not get what I want with it. I was wondering if a SQL expert out there could give me a hand creating this report in the K1000.
What I would like is a report that has System model (header break) then sorted by Shipped date (from Warranty information) to also include System name, Date created, User name, User Full name and Service Tag number.
I can almost get what I want through the Wizard but it puts multiple entries for the same device that have to be expanded, I would like to not have those multiple entries.
Thanks for any help in advance.
1 Comment
[ + ] Show comment
Answers (1)
Answer Summary:
Please log in to answer
Posted by:
chucksteel
7 years ago
Top Answer
This report will only work for Dell computers:
SELECT DISTINCT(MACHINE.NAME),
MACHINE.OS_NAME,
MACHINE.CS_MODEL,
MACHINE.CS_MANUFACTURER,
DA.SHIP_DATE AS "Ship Date",
MACHINE.CREATED,
MACHINE.USER,
MACHINE.USER_FULLNAME
FROM MACHINE
LEFT JOIN DELL_ASSET DA on MACHINE.BIOS_SERIAL_NUMBER = DA.SERVICE_TAG
LEFT JOIN DELL_WARRANTY DW on MACHINE.BIOS_SERIAL_NUMBER = DW.SERVICE_TAG
WHERE MACHINE.CS_MANUFACTURER like 'Dell%'
GROUP BY MACHINE.NAME
ORDER BY CS_MODEL, `Ship Date`, MACHINE.NAME
When creating the report enter CS_MODEL for "Break on Columns"
Comments:
-
That worked perfect. Thank you! - DJSlater 7 years ago
Generally the K1000 stores data from different sections in different DB tables. The wizard is limited in creating SQL queries that pull data from each table and display in a single table output. Thus you get the nested tables when you have a report that has a subtopic.
Is all of the info you want stored under the device asset or device inventory page? - DirtySoc 7 years ago