R
Randall Arnold
Greetings,
I have a need to construct a query that returns defect counts per category
per day for a periodic auditing activity. I've build 2 queries so far but
neither does exactly what I need.
The required query involves 2 tables (current queries have tables joined on
CategoryID), one a complete list of Categories that are audited and the
other a list of defects. I am currently counting the number of defects
aggregated by category but of course no results are returned if no defects
have been found on a given day for certain categories. So the result set is
inconsistent. What I need is that for every day the count of each category
is returned even if that number is zero. For each day the number of records
returned should be exactly the same; however, I will sort descending by
number of defects (pareto).
Here is an example of how my first query returns results (note inconsistent
appearance of categories per day):
AuditPARADEFailsByCategoryCountPerDay_View DateInput PeriodYear Period
WeekFrom2001 Category CategoryCount
2/14/2005 2005 2 216 MIS 2
2/14/2005 2005 2 216 Prod Handling 1
2/16/2005 2005 2 216 MIS 4
2/17/2005 2005 2 216 Prod Handling 2
2/17/2005 2005 2 216 Prod ID 1
2/18/2005 2005 2 216 Prod ID 1
2/22/2005 2005 2 217 MIS 3
2/22/2005 2005 2 217 Specs 1
2/23/2005 2005 2 217 Specs 2
Here is the SQL for the above (note: this SQL has a 1-year date range that
the second example doesn't use):
SELECT TOP 100 PERCENT dev.AuditPARADEFails_View.DateInput,
dev.AuditPARADEFails_View.PeriodYear, dev.AuditPARADEFails_View.Period,
(DATEPART(year, dev.AuditPARADEFails_View.DateInput) -
2001) * 52 + DATEPART(wk, dev.AuditPARADEFails_View.DateInput) AS
WeekFrom2001,
COUNT(dbo.AuditCategories.Category) AS CategoryCount,
dbo.AuditCategories.Category
FROM dev.AuditPARADEFails_View RIGHT OUTER JOIN
dbo.AuditCategories ON
dev.AuditPARADEFails_View.CategoryID = dbo.AuditCategories.CategoryID
GROUP BY dbo.AuditCategories.Category, dev.AuditPARADEFails_View.PeriodYear,
dev.AuditPARADEFails_View.Period, dev.AuditPARADEFails_View.DateInput,
(DATEPART(year, dev.AuditPARADEFails_View.DateInput) -
2001) * 52 + DATEPART(wk, dev.AuditPARADEFails_View.DateInput),
dbo.AuditCategories.CategoryID
HAVING (dev.AuditPARADEFails_View.DateInput BETWEEN DATEADD(wk, - 52,
DATEADD(d, DATEPART(dw, DATEADD(d, 1, GETDATE())) * - 1, GETDATE())) AND
DATEADD(d, DATEPART(dw, DATEADD(d, 1, GETDATE())) * -
1, GETDATE())) AND (NOT (dbo.AuditCategories.CategoryID IN (38, 40, 41,
55)))
ORDER BY dev.AuditPARADEFails_View.PeriodYear,
dev.AuditPARADEFails_View.DateInput, dev.AuditPARADEFails_View.Period,
COUNT(dbo.AuditCategories.Category) DESC,
dbo.AuditCategories.Category
----------------------------------------------------------------------------------------------------------------------
Here is how the second query returns them:
AuditPARADECategoryFails_View DateInput Product ID PeriodYear Period
Procedure Process Safety Housekeeping PMs Specifications Product Handling
MIS Audit Checklist
2/6/2003 0 2003 2 0 2 0 0 0 0 0 0 0
2/7/2003 0 2003 2 0 6 0 0 0 0 0 0 0
2/10/2003 0 2003 2 1 4 1 2 0 0 0 0 0
2/11/2003 0 2003 2 1 2 1 1 0 0 0 0 0
2/12/2003 0 2003 2 1 0 0 8 0 0 0 0 0
2/13/2003 0 2003 2 1 0 0 0 0 0 0 0 0
2/17/2003 0 2003 2 0 5 0 3 0 0 0 0 0
2/18/2003 0 2003 2 8 10 0 2 0 0 0 0 0
Here is the SQL for the above data set (expand window to improve
readability):
SELECT TOP 100 PERCENT dev.AuditPARADEFails_View.Period,
dev.AuditPARADEFails_View.PeriodYear,
COUNT(CASE WHEN dev.AuditPARADEFails_View.categoryid =
42 THEN 1 ELSE NULL END) AS [Procedure],
COUNT(CASE WHEN dev.AuditPARADEFails_View.categoryid =
43 THEN 1 ELSE NULL END) AS Process,
COUNT(CASE WHEN dev.AuditPARADEFails_View.categoryid =
44 OR
dev.AuditPARADEFails_View.categoryid = 50 THEN 1 ELSE
NULL END) AS Safety, COUNT(CASE WHEN dev.AuditPARADEFails_View.categoryid =
45 OR
dev.AuditPARADEFails_View.categoryid = 52 THEN 1 ELSE
NULL END) AS Housekeeping,
COUNT(CASE WHEN dev.AuditPARADEFails_View.categoryid =
46 THEN 1 ELSE NULL END) AS [Product ID],
COUNT(CASE WHEN dev.AuditPARADEFails_View.categoryid =
47 THEN 1 ELSE NULL END) AS PMs,
COUNT(CASE WHEN dev.AuditPARADEFails_View.categoryid =
48 THEN 1 ELSE NULL END) AS Specifications,
COUNT(CASE WHEN dev.AuditPARADEFails_View.categoryid =
49 THEN 1 ELSE NULL END) AS [Product Handling],
COUNT(CASE WHEN dev.AuditPARADEFails_View.categoryid =
51 THEN 1 ELSE NULL END) AS MIS,
COUNT(CASE WHEN dev.AuditPARADEFails_View.categoryid =
53 THEN 1 ELSE NULL END) AS [Audit Checklist],
dev.AuditPARADEFails_View.DateInput
FROM dbo.AuditCategories LEFT OUTER JOIN
dev.AuditPARADEFails_View ON
dbo.AuditCategories.CategoryID = dev.AuditPARADEFails_View.CategoryID
GROUP BY dev.AuditPARADEFails_View.Period,
dev.AuditPARADEFails_View.PeriodYear, dev.AuditPARADEFails_View.DateInput
ORDER BY dev.AuditPARADEFails_View.DateInput,
dev.AuditPARADEFails_View.PeriodYear, dev.AuditPARADEFails_View.Period
-------------------------------------------------------------------------------------------------------------
What I need is a hybrid of both, that returns results as shown:
DesiredAuditPARADEFailsByCategoryCountPerDay_View DateInput PeriodYear
Period WeekFrom2001 Category CategoryCount
2/14/2005 2005 2 216 Cat 1 22
2/14/2005 2005 2 216 Cat 2 11
2/14/2005 2005 2 216 Cat 3 4
2/14/2005 2005 2 216 Cat 4 2
2/15/2005 2005 2 216 Cat 3 17
2/15/2005 2005 2 216 Cat 1 12
2/15/2005 2005 2 216 Cat 4 3
2/15/2005 2005 2 216 Cat 2 1
In the example above, assume Categories 1 through 4 cover the complete list
(actual number is 12). Note that the order changes from Day 1 to Day 2
based on pareto sort criteria.
I realize this is a lot to throw at people, but I've been wracking my brain
for weeks trying to figure out a solution. I'm hoping one of the gurus here
can help.
Many thanks,
Randall Arnold
I have a need to construct a query that returns defect counts per category
per day for a periodic auditing activity. I've build 2 queries so far but
neither does exactly what I need.
The required query involves 2 tables (current queries have tables joined on
CategoryID), one a complete list of Categories that are audited and the
other a list of defects. I am currently counting the number of defects
aggregated by category but of course no results are returned if no defects
have been found on a given day for certain categories. So the result set is
inconsistent. What I need is that for every day the count of each category
is returned even if that number is zero. For each day the number of records
returned should be exactly the same; however, I will sort descending by
number of defects (pareto).
Here is an example of how my first query returns results (note inconsistent
appearance of categories per day):
AuditPARADEFailsByCategoryCountPerDay_View DateInput PeriodYear Period
WeekFrom2001 Category CategoryCount
2/14/2005 2005 2 216 MIS 2
2/14/2005 2005 2 216 Prod Handling 1
2/16/2005 2005 2 216 MIS 4
2/17/2005 2005 2 216 Prod Handling 2
2/17/2005 2005 2 216 Prod ID 1
2/18/2005 2005 2 216 Prod ID 1
2/22/2005 2005 2 217 MIS 3
2/22/2005 2005 2 217 Specs 1
2/23/2005 2005 2 217 Specs 2
Here is the SQL for the above (note: this SQL has a 1-year date range that
the second example doesn't use):
SELECT TOP 100 PERCENT dev.AuditPARADEFails_View.DateInput,
dev.AuditPARADEFails_View.PeriodYear, dev.AuditPARADEFails_View.Period,
(DATEPART(year, dev.AuditPARADEFails_View.DateInput) -
2001) * 52 + DATEPART(wk, dev.AuditPARADEFails_View.DateInput) AS
WeekFrom2001,
COUNT(dbo.AuditCategories.Category) AS CategoryCount,
dbo.AuditCategories.Category
FROM dev.AuditPARADEFails_View RIGHT OUTER JOIN
dbo.AuditCategories ON
dev.AuditPARADEFails_View.CategoryID = dbo.AuditCategories.CategoryID
GROUP BY dbo.AuditCategories.Category, dev.AuditPARADEFails_View.PeriodYear,
dev.AuditPARADEFails_View.Period, dev.AuditPARADEFails_View.DateInput,
(DATEPART(year, dev.AuditPARADEFails_View.DateInput) -
2001) * 52 + DATEPART(wk, dev.AuditPARADEFails_View.DateInput),
dbo.AuditCategories.CategoryID
HAVING (dev.AuditPARADEFails_View.DateInput BETWEEN DATEADD(wk, - 52,
DATEADD(d, DATEPART(dw, DATEADD(d, 1, GETDATE())) * - 1, GETDATE())) AND
DATEADD(d, DATEPART(dw, DATEADD(d, 1, GETDATE())) * -
1, GETDATE())) AND (NOT (dbo.AuditCategories.CategoryID IN (38, 40, 41,
55)))
ORDER BY dev.AuditPARADEFails_View.PeriodYear,
dev.AuditPARADEFails_View.DateInput, dev.AuditPARADEFails_View.Period,
COUNT(dbo.AuditCategories.Category) DESC,
dbo.AuditCategories.Category
----------------------------------------------------------------------------------------------------------------------
Here is how the second query returns them:
AuditPARADECategoryFails_View DateInput Product ID PeriodYear Period
Procedure Process Safety Housekeeping PMs Specifications Product Handling
MIS Audit Checklist
2/6/2003 0 2003 2 0 2 0 0 0 0 0 0 0
2/7/2003 0 2003 2 0 6 0 0 0 0 0 0 0
2/10/2003 0 2003 2 1 4 1 2 0 0 0 0 0
2/11/2003 0 2003 2 1 2 1 1 0 0 0 0 0
2/12/2003 0 2003 2 1 0 0 8 0 0 0 0 0
2/13/2003 0 2003 2 1 0 0 0 0 0 0 0 0
2/17/2003 0 2003 2 0 5 0 3 0 0 0 0 0
2/18/2003 0 2003 2 8 10 0 2 0 0 0 0 0
Here is the SQL for the above data set (expand window to improve
readability):
SELECT TOP 100 PERCENT dev.AuditPARADEFails_View.Period,
dev.AuditPARADEFails_View.PeriodYear,
COUNT(CASE WHEN dev.AuditPARADEFails_View.categoryid =
42 THEN 1 ELSE NULL END) AS [Procedure],
COUNT(CASE WHEN dev.AuditPARADEFails_View.categoryid =
43 THEN 1 ELSE NULL END) AS Process,
COUNT(CASE WHEN dev.AuditPARADEFails_View.categoryid =
44 OR
dev.AuditPARADEFails_View.categoryid = 50 THEN 1 ELSE
NULL END) AS Safety, COUNT(CASE WHEN dev.AuditPARADEFails_View.categoryid =
45 OR
dev.AuditPARADEFails_View.categoryid = 52 THEN 1 ELSE
NULL END) AS Housekeeping,
COUNT(CASE WHEN dev.AuditPARADEFails_View.categoryid =
46 THEN 1 ELSE NULL END) AS [Product ID],
COUNT(CASE WHEN dev.AuditPARADEFails_View.categoryid =
47 THEN 1 ELSE NULL END) AS PMs,
COUNT(CASE WHEN dev.AuditPARADEFails_View.categoryid =
48 THEN 1 ELSE NULL END) AS Specifications,
COUNT(CASE WHEN dev.AuditPARADEFails_View.categoryid =
49 THEN 1 ELSE NULL END) AS [Product Handling],
COUNT(CASE WHEN dev.AuditPARADEFails_View.categoryid =
51 THEN 1 ELSE NULL END) AS MIS,
COUNT(CASE WHEN dev.AuditPARADEFails_View.categoryid =
53 THEN 1 ELSE NULL END) AS [Audit Checklist],
dev.AuditPARADEFails_View.DateInput
FROM dbo.AuditCategories LEFT OUTER JOIN
dev.AuditPARADEFails_View ON
dbo.AuditCategories.CategoryID = dev.AuditPARADEFails_View.CategoryID
GROUP BY dev.AuditPARADEFails_View.Period,
dev.AuditPARADEFails_View.PeriodYear, dev.AuditPARADEFails_View.DateInput
ORDER BY dev.AuditPARADEFails_View.DateInput,
dev.AuditPARADEFails_View.PeriodYear, dev.AuditPARADEFails_View.Period
-------------------------------------------------------------------------------------------------------------
What I need is a hybrid of both, that returns results as shown:
DesiredAuditPARADEFailsByCategoryCountPerDay_View DateInput PeriodYear
Period WeekFrom2001 Category CategoryCount
2/14/2005 2005 2 216 Cat 1 22
2/14/2005 2005 2 216 Cat 2 11
2/14/2005 2005 2 216 Cat 3 4
2/14/2005 2005 2 216 Cat 4 2
2/15/2005 2005 2 216 Cat 3 17
2/15/2005 2005 2 216 Cat 1 12
2/15/2005 2005 2 216 Cat 4 3
2/15/2005 2005 2 216 Cat 2 1
In the example above, assume Categories 1 through 4 cover the complete list
(actual number is 12). Note that the order changes from Day 1 to Day 2
based on pareto sort criteria.
I realize this is a lot to throw at people, but I've been wracking my brain
for weeks trying to figure out a solution. I'm hoping one of the gurus here
can help.
Many thanks,
Randall Arnold