query to return counts of all categories per day

[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

 

[Guest]

Randall:

I'm afraid I haven't had time to analyse your queries in detail but the
usual way to deal with missing dates is to include a calendar table of all
dates in your database and LEFT JOIN it on the date columns to the table with
partial dates so that a row is returned for every date within whatever date
range you are interested in. You can create a calendar table by serially
filling down a column in Excel and importing it into Access, or I've written
the function below to do it via ADO. To create a table named Calendar from
2000 to 2020 with all days of the week included you'd call it with:

MakeCalendar #01/01/2000#, #12/31/2020#, 0

Heres the function:

Public Function MakeCalendar(strTable As String, _
dtmStart As Date, _
dtmEnd As Date, _
ParamArray varDays() As Variant)

' Accepts: Name of calendar table to be created: String.
' Start date for calendar: DateTime.
' End date for calendar: DateTime.
' Days of week to be included in calendar
' as value list, e,g 2,3,4,5,6 for Mon-Fri
' (use 0 to include all days of week)

Dim cmd As ADODB.Command
Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table
Dim strSQL As String
Dim dtmDate As Date
Dim varDay As Variant

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

Set cat = New Catalog
cat.ActiveConnection = CurrentProject.Connection

' does table exist? If so get user confirmation to delete it
On Error Resume Next
Set tbl = cat(strTable)
If Err = 0 Then
If MsgBox("Replace existing table: " & _
strTable & "?", vbYesNo + vbQuestion, _
"Delete Table?") = vbYes Then
strSQL = "DROP TABLE " & strTable
cmd.CommandText = strSQL
cmd.Execute
Else
Set cmd = Nothing
Exit Function
End If
End If
On Error GoTo 0

' create new table
strSQL = "CREATE TABLE " & strTable & _
"(calDate DATETIME, " & _
"CONSTRAINT PrimaryKey PRIMARY KEY (calDate))"
cmd.CommandText = strSQL
cmd.Execute

If varDays(0) = 0 Then
' fill table with all dates
For dtmDate = dtmStart To dtmEnd
cmd.CommandText = strSQL
strSQL = "INSERT INTO " & strTable & "(calDate) " & _
"VALUES(#" & Format(dtmDate, "mm/dd/yyyy") & "#)"
cmd.CommandText = strSQL
cmd.Execute
Next dtmDate
Else
' fill table with dates of selected days of week only
For dtmDate = dtmStart To dtmEnd
For Each varDay In varDays()
If Weekday(dtmDate) = varDay Then
cmd.CommandText = strSQL
strSQL = "INSERT INTO " & strTable & "(calDate) " & _
"VALUES(#" & Format(dtmDate, "mm/dd/yyyy") & "#)"
cmd.CommandText = strSQL
cmd.Execute
End If
Next varDay
Next dtmDate
End If

Set cmd = Nothing

End Function

Ken Sheridan
Stafford, England

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

 

[Randall Arnold]

Ken, thanks for your reply. I'll print it out and see if it will help.

It's not actually dates that may be missing, but category failures to count.
I actually don't care if a date is skipped, just that on a given day x out
of y categories can have any number (including zero) of failures. I just
need to make sure that for every single day an audit was conducted
(DateInput) I can generate a list containing the count every single
categorical failure even if it's zero. The same list of all categorical
failure counts needs to be produced for every single day so that the result
set per day always has the same number of records, ordered DESC by count
value.

Sorry for the redundancy; just trying another way to explain it...

Randall

Randall:

I'm afraid I haven't had time to analyse your queries in detail but the
usual way to deal with missing dates is to include a calendar table of all
dates in your database and LEFT JOIN it on the date columns to the table
with
partial dates so that a row is returned for every date within whatever
date
range you are interested in. You can create a calendar table by serially
filling down a column in Excel and importing it into Access, or I've
written
the function below to do it via ADO. To create a table named Calendar from
2000 to 2020 with all days of the week included you'd call it with:

MakeCalendar #01/01/2000#, #12/31/2020#, 0

Heres the function:

Public Function MakeCalendar(strTable As String, _
dtmStart As Date, _
dtmEnd As Date, _
ParamArray varDays() As Variant)

' Accepts: Name of calendar table to be created: String.
' Start date for calendar: DateTime.
' End date for calendar: DateTime.
' Days of week to be included in calendar
' as value list, e,g 2,3,4,5,6 for Mon-Fri
' (use 0 to include all days of week)

Dim cmd As ADODB.Command
Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table
Dim strSQL As String
Dim dtmDate As Date
Dim varDay As Variant

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

Set cat = New Catalog
cat.ActiveConnection = CurrentProject.Connection

' does table exist? If so get user confirmation to delete it
On Error Resume Next
Set tbl = cat(strTable)
If Err = 0 Then
If MsgBox("Replace existing table: " & _
strTable & "?", vbYesNo + vbQuestion, _
"Delete Table?") = vbYes Then
strSQL = "DROP TABLE " & strTable
cmd.CommandText = strSQL
cmd.Execute
Else
Set cmd = Nothing
Exit Function
End If
End If
On Error GoTo 0

' create new table
strSQL = "CREATE TABLE " & strTable & _
"(calDate DATETIME, " & _
"CONSTRAINT PrimaryKey PRIMARY KEY (calDate))"
cmd.CommandText = strSQL
cmd.Execute

If varDays(0) = 0 Then
' fill table with all dates
For dtmDate = dtmStart To dtmEnd
cmd.CommandText = strSQL
strSQL = "INSERT INTO " & strTable & "(calDate) " & _
"VALUES(#" & Format(dtmDate, "mm/dd/yyyy") & "#)"
cmd.CommandText = strSQL
cmd.Execute
Next dtmDate
Else
' fill table with dates of selected days of week only
For dtmDate = dtmStart To dtmEnd
For Each varDay In varDays()
If Weekday(dtmDate) = varDay Then
cmd.CommandText = strSQL
strSQL = "INSERT INTO " & strTable & "(calDate) " & _
"VALUES(#" & Format(dtmDate, "mm/dd/yyyy") & "#)"
cmd.CommandText = strSQL
cmd.Execute
End If
Next varDay
Next dtmDate
End If

Set cmd = Nothing

End Function

Ken Sheridan
Stafford, England

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

 

[Guest]

Randall:

Using a Calendar table constructed as I suggested you should be able to
return a set of all date/category combinations and a count of zero where the
dates are in your first query's result set but the categories are missing for
a date, with something along the following lines. Note that I've used the
DATEDIFF function to return the values for the WeekFrom2001 column rather
than your expression, but you could use your original one, which returns
slightly different values. I've also used the YEAR function rather than the
DATEPART function. I've repeated your expression as the first part of the
WHERE clause, but there might be a simpler way to define the date range.

The one difficulty is the Period column as I don't know what this
represents. If it can be computed from the date then you could do so (that
would of course mean the Period column in the base table is redundant). For
the time being I've used a constant zero.

By combining this with AuditPARADEFailsByCategoryCountPerDay_View in a UNION
opaertion you should be able to fill in the gaps. The Order BY clause would
then go in the UNION of course not the original view.

SELECT
CalDate As DateInput,
YEAR(CalDate) AS PeriodYear,
0 AS Period,
DATEDIFF("w", #01/01/2001#, CalDate) AS WeekFrom2001,
Category,
0 AS CategoryCount
FROM
Calendar, AuditCategories
WHERE
Caldate 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 EXISTS
(SELECT *
FROM AuditPARADEFailsByCategoryCountPerDay_View
WHERE
AuditPARADEFailsByCategoryCountPerDay_View.DateInput = Calendar.Caldate)
AND NOT EXISTS
(SELECT *
FROM AuditPARADEFailsByCategoryCountPerDay_View
WHERE
AuditPARADEFailsByCategoryCountPerDay_View.DateInput = Calendar.Caldate
AND
AuditPARADEFailsByCategoryCountPerDay_View.Category =
AuditCategories.Category);

Ken Sheridan
Stafford, England