We have put together a number of queries that we use frequently for data analysis and we'd like to share them with you!
There may be remarked out lines (--) that you can enable so that the query produces different results.
Of course feel free to change the TableName, FieldName or any other field in the these queries to find the answers you are looking for!
This query shows how many lines each COA record has and sorts it from most lines to least lines
select Count(*) as COADetailsCount, COAPrintHeaderID from HQMS_COAPrintDetail
group by COAPrintHeaderID
order by count(*) desc
This query shows you the audit data for a particular table.
select * from HQMS_Audit_Data
where
TableName = 'HQMS_COAPrintHeader'
--and FieldName = 'ExternalItemNumber'
--and PrimaryKeyValue = 2317
order by CAST(PrimaryKeyValue as int) asc
--order by AuditID desc
This query shows templates and their inspections and tests attached to them:
SELECT
HQMS_InspectionTest.InspectionID,
HQMS_Inspection.InspectionCode,
HQMS_Inspection.Description,
HQMS_InspectionTest.TestID,
HQMS_Test.TestCode,
HQMS_COATemplateHeader.TemplateName,
HQMS_COATemplateHeader.TemplateDescription
FROM
HQMS_COATemplateDetail INNER JOIN
HQMS_COATemplateHeader ON HQMS_COATemplateDetail.COATemplateHeaderID = HQMS_COATemplateHeader.COATemplateHeaderID INNER JOIN
HQMS_InspectionTest ON HQMS_COATemplateDetail.InspectionTestID = HQMS_InspectionTest.InspectionTestID INNER JOIN
HQMS_Inspection ON HQMS_InspectionTest.InspectionID = HQMS_Inspection.InspectionID INNER JOIN
HQMS_Test ON HQMS_InspectionTest.TestID = HQMS_Test.TestID
WHERE
(HQMS_COATemplateDetail.COATemplateHeaderID = 9)