June 27, 2014 at 6:52 am
I have two tables. The first (tblInternalAuditRequirement) houses internal audits that are required for our company. The second (tblInternalAuditAssignment) houses all audits that have been assigned. I would like to be able to find the InternalAuditRequirementID and AuditTeamID of Audit Requirements that don't have any outstanding audits (essentially where there are no audits where the Completion Date is null, AND where the audits might not have been assigned yet). The query below takes care of the first part of finding the ones that don't have any null completion dates. However, I'm having a hard time figuring out how to also include the ones that haven't been assigned yet.
Query to get results:
SELECT tblInternalAuditRequirement.InternalAuditRequirementID, AuditTeamID
FROM tblInternalAuditRequirement INNER JOIN
(SELECT tblInternalAuditAssignment.InternalAuditRequirementID
FROM tblInternalAuditAssignment
WHERE (tblInternalAuditAssignment.CompletionDate IS NOT NULL)
GROUP BY tblInternalAuditAssignment.InternalAuditRequirementID) AS CompletedAudits ON
tblInternalAuditRequirement.InternalAuditRequirementID = CompletedAudits.InternalAuditRequirementID
WHERE (Enabled = 'True')
CURRENT RESULTS:
InternalAuditRequirementIDAuditTeamID
1 1
8 1
14 1
19 1
23 1
DESIRED RESULTS:
InternalAuditRequirementIDAuditTeamID
1 1
8 1
14 1
16 2
19 1
23 1
27 3
DDL for table creation and sample data:
CREATE TABLE [tblInternalAuditAssignment](
[AuditAssignmentID] [int] IDENTITY(1,1) NOT NULL,
[EmployeeID] [int] NOT NULL,
[InternalAuditRequirementID] [int] NOT NULL,
[DateAssigned] [datetime] NOT NULL,
[CompletionDate] [datetime] NULL,
[Comments] [varchar](max) NULL,
CONSTRAINT [PK_tblInternalAuditAssignment] PRIMARY KEY CLUSTERED
(
[AuditAssignmentID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [tblInternalAuditRequirement](
[InternalAuditRequirementID] [int] IDENTITY(1,1) NOT NULL,
[AuditID] [int] NOT NULL,
[AuditDescription] [varchar](75) NOT NULL,
[Reoccurence] [tinyint] NOT NULL,
[LastAuditSent] [datetime] NULL,
[Enabled] [bit] NOT NULL,
[AuditTeamID] [int] NOT NULL,
CONSTRAINT [PK_tblInternalAuditRequirement] PRIMARY KEY CLUSTERED
(
[InternalAuditRequirementID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [tblInternalAuditAssignment] ON
GO
INSERT [tblInternalAuditAssignment] ([AuditAssignmentID], [EmployeeID], [InternalAuditRequirementID], [DateAssigned], [CompletionDate], [Comments]) VALUES (82, 191, 1, CAST(0x0000A35700000000 AS DateTime), CAST(0x0000A35700000000 AS DateTime), NULL)
GO
INSERT [tblInternalAuditAssignment] ([AuditAssignmentID], [EmployeeID], [InternalAuditRequirementID], [DateAssigned], [CompletionDate], [Comments]) VALUES (83, 940, 2, CAST(0x0000A35700000000 AS DateTime), NULL, NULL)
GO
INSERT [tblInternalAuditAssignment] ([AuditAssignmentID], [EmployeeID], [InternalAuditRequirementID], [DateAssigned], [CompletionDate], [Comments]) VALUES (84, 39, 3, CAST(0x0000A35700000000 AS DateTime), NULL, NULL)
GO
INSERT [tblInternalAuditAssignment] ([AuditAssignmentID], [EmployeeID], [InternalAuditRequirementID], [DateAssigned], [CompletionDate], [Comments]) VALUES (85, 234, 4, CAST(0x0000A35700000000 AS DateTime), NULL, NULL)
GO
INSERT [tblInternalAuditAssignment] ([AuditAssignmentID], [EmployeeID], [InternalAuditRequirementID], [DateAssigned], [CompletionDate], [Comments]) VALUES (86, 954, 5, CAST(0x0000A35700000000 AS DateTime), NULL, NULL)
GO
INSERT [tblInternalAuditAssignment] ([AuditAssignmentID], [EmployeeID], [InternalAuditRequirementID], [DateAssigned], [CompletionDate], [Comments]) VALUES (87, 619, 6, CAST(0x0000A35700000000 AS DateTime), NULL, NULL)
GO
INSERT [tblInternalAuditAssignment] ([AuditAssignmentID], [EmployeeID], [InternalAuditRequirementID], [DateAssigned], [CompletionDate], [Comments]) VALUES (88, 858, 7, CAST(0x0000A35700000000 AS DateTime), NULL, NULL)
GO
INSERT [tblInternalAuditAssignment] ([AuditAssignmentID], [EmployeeID], [InternalAuditRequirementID], [DateAssigned], [CompletionDate], [Comments]) VALUES (89, 604, 8, CAST(0x0000A35700000000 AS DateTime), CAST(0x0000A35700000000 AS DateTime), NULL)
GO
INSERT [tblInternalAuditAssignment] ([AuditAssignmentID], [EmployeeID], [InternalAuditRequirementID], [DateAssigned], [CompletionDate], [Comments]) VALUES (90, 518, 9, CAST(0x0000A35700000000 AS DateTime), NULL, NULL)
GO
INSERT [tblInternalAuditAssignment] ([AuditAssignmentID], [EmployeeID], [InternalAuditRequirementID], [DateAssigned], [CompletionDate], [Comments]) VALUES (91, 716, 10, CAST(0x0000A35700000000 AS DateTime), NULL, NULL)
GO
INSERT [tblInternalAuditAssignment] ([AuditAssignmentID], [EmployeeID], [InternalAuditRequirementID], [DateAssigned], [CompletionDate], [Comments]) VALUES (92, 891, 11, CAST(0x0000A35700000000 AS DateTime), NULL, NULL)
GO
INSERT [tblInternalAuditAssignment] ([AuditAssignmentID], [EmployeeID], [InternalAuditRequirementID], [DateAssigned], [CompletionDate], [Comments]) VALUES (93, 966, 12, CAST(0x0000A35700000000 AS DateTime), NULL, NULL)
GO
INSERT [tblInternalAuditAssignment] ([AuditAssignmentID], [EmployeeID], [InternalAuditRequirementID], [DateAssigned], [CompletionDate], [Comments]) VALUES (94, 191, 13, CAST(0x0000A35700000000 AS DateTime), NULL, NULL)
GO
INSERT [tblInternalAuditAssignment] ([AuditAssignmentID], [EmployeeID], [InternalAuditRequirementID], [DateAssigned], [CompletionDate], [Comments]) VALUES (95, 940, 14, CAST(0x0000A35700000000 AS DateTime), CAST(0x0000A35700000000 AS DateTime), NULL)
GO
INSERT [tblInternalAuditAssignment] ([AuditAssignmentID], [EmployeeID], [InternalAuditRequirementID], [DateAssigned], [CompletionDate], [Comments]) VALUES (96, 39, 15, CAST(0x0000A35700000000 AS DateTime), NULL, NULL)
GO
INSERT [tblInternalAuditAssignment] ([AuditAssignmentID], [EmployeeID], [InternalAuditRequirementID], [DateAssigned], [CompletionDate], [Comments]) VALUES (98, 234, 17, CAST(0x0000A35700000000 AS DateTime), NULL, NULL)
GO
INSERT [tblInternalAuditAssignment] ([AuditAssignmentID], [EmployeeID], [InternalAuditRequirementID], [DateAssigned], [CompletionDate], [Comments]) VALUES (99, 619, 18, CAST(0x0000A35700000000 AS DateTime), NULL, NULL)
GO
INSERT [tblInternalAuditAssignment] ([AuditAssignmentID], [EmployeeID], [InternalAuditRequirementID], [DateAssigned], [CompletionDate], [Comments]) VALUES (100, 858, 19, CAST(0x0000A35700000000 AS DateTime), CAST(0x0000A35700000000 AS DateTime), NULL)
GO
INSERT [tblInternalAuditAssignment] ([AuditAssignmentID], [EmployeeID], [InternalAuditRequirementID], [DateAssigned], [CompletionDate], [Comments]) VALUES (101, 604, 20, CAST(0x0000A35700000000 AS DateTime), NULL, NULL)
GO
INSERT [tblInternalAuditAssignment] ([AuditAssignmentID], [EmployeeID], [InternalAuditRequirementID], [DateAssigned], [CompletionDate], [Comments]) VALUES (102, 518, 21, CAST(0x0000A35700000000 AS DateTime), NULL, NULL)
GO
INSERT [tblInternalAuditAssignment] ([AuditAssignmentID], [EmployeeID], [InternalAuditRequirementID], [DateAssigned], [CompletionDate], [Comments]) VALUES (103, 716, 22, CAST(0x0000A35700000000 AS DateTime), NULL, NULL)
GO
INSERT [tblInternalAuditAssignment] ([AuditAssignmentID], [EmployeeID], [InternalAuditRequirementID], [DateAssigned], [CompletionDate], [Comments]) VALUES (104, 966, 23, CAST(0x0000A35700000000 AS DateTime), CAST(0x0000A35700000000 AS DateTime), NULL)
GO
INSERT [tblInternalAuditAssignment] ([AuditAssignmentID], [EmployeeID], [InternalAuditRequirementID], [DateAssigned], [CompletionDate], [Comments]) VALUES (105, 191, 24, CAST(0x0000A35700000000 AS DateTime), NULL, NULL)
GO
INSERT [tblInternalAuditAssignment] ([AuditAssignmentID], [EmployeeID], [InternalAuditRequirementID], [DateAssigned], [CompletionDate], [Comments]) VALUES (106, 940, 25, CAST(0x0000A35700000000 AS DateTime), NULL, NULL)
GO
INSERT [tblInternalAuditAssignment] ([AuditAssignmentID], [EmployeeID], [InternalAuditRequirementID], [DateAssigned], [CompletionDate], [Comments]) VALUES (107, 945, 26, CAST(0x0000A35700000000 AS DateTime), NULL, NULL)
GO
SET IDENTITY_INSERT [tblInternalAuditAssignment] OFF
GO
SET IDENTITY_INSERT [tblInternalAuditRequirement] ON
GO
INSERT [tblInternalAuditRequirement] ([InternalAuditRequirementID], [AuditID], [AuditDescription], [Reoccurence], [LastAuditSent], [Enabled], [AuditTeamID]) VALUES (1, 1, N'GMP Audit of Poly 1', 7, NULL, 1, 1)
GO
INSERT [tblInternalAuditRequirement] ([InternalAuditRequirementID], [AuditID], [AuditDescription], [Reoccurence], [LastAuditSent], [Enabled], [AuditTeamID]) VALUES (2, 1, N'GMP Audit of Poly 2', 7, NULL, 1, 1)
GO
INSERT [tblInternalAuditRequirement] ([InternalAuditRequirementID], [AuditID], [AuditDescription], [Reoccurence], [LastAuditSent], [Enabled], [AuditTeamID]) VALUES (3, 1, N'GMP Audit of Poly 3', 7, NULL, 1, 1)
GO
INSERT [tblInternalAuditRequirement] ([InternalAuditRequirementID], [AuditID], [AuditDescription], [Reoccurence], [LastAuditSent], [Enabled], [AuditTeamID]) VALUES (4, 1, N'GMP Audit of Poly 4', 7, NULL, 1, 1)
GO
INSERT [tblInternalAuditRequirement] ([InternalAuditRequirementID], [AuditID], [AuditDescription], [Reoccurence], [LastAuditSent], [Enabled], [AuditTeamID]) VALUES (5, 1, N'GMP Audit of Poly 3', 7, NULL, 1, 2)
GO
INSERT [tblInternalAuditRequirement] ([InternalAuditRequirementID], [AuditID], [AuditDescription], [Reoccurence], [LastAuditSent], [Enabled], [AuditTeamID]) VALUES (6, 1, N'GMP Audit of Jar Line', 21, NULL, 1, 1)
GO
INSERT [tblInternalAuditRequirement] ([InternalAuditRequirementID], [AuditID], [AuditDescription], [Reoccurence], [LastAuditSent], [Enabled], [AuditTeamID]) VALUES (7, 1, N'GMP Audit of Fruit Mixer', 14, NULL, 1, 1)
GO
INSERT [tblInternalAuditRequirement] ([InternalAuditRequirementID], [AuditID], [AuditDescription], [Reoccurence], [LastAuditSent], [Enabled], [AuditTeamID]) VALUES (8, 1, N'GMP Audit of Machine Packaging', 7, NULL, 1, 1)
GO
INSERT [tblInternalAuditRequirement] ([InternalAuditRequirementID], [AuditID], [AuditDescription], [Reoccurence], [LastAuditSent], [Enabled], [AuditTeamID]) VALUES (9, 1, N'GMP Audit of Hand Pack', 7, NULL, 1, 1)
GO
INSERT [tblInternalAuditRequirement] ([InternalAuditRequirementID], [AuditID], [AuditDescription], [Reoccurence], [LastAuditSent], [Enabled], [AuditTeamID]) VALUES (10, 1, N'GMP Audit of Hay Baler', 7, NULL, 1, 1)
GO
INSERT [tblInternalAuditRequirement] ([InternalAuditRequirementID], [AuditID], [AuditDescription], [Reoccurence], [LastAuditSent], [Enabled], [AuditTeamID]) VALUES (11, 1, N'GMP Audit of Hay Baler', 7, NULL, 1, 2)
GO
INSERT [tblInternalAuditRequirement] ([InternalAuditRequirementID], [AuditID], [AuditDescription], [Reoccurence], [LastAuditSent], [Enabled], [AuditTeamID]) VALUES (12, 1, N'GMP Audit of Stick Room', 7, NULL, 1, 1)
GO
INSERT [tblInternalAuditRequirement] ([InternalAuditRequirementID], [AuditID], [AuditDescription], [Reoccurence], [LastAuditSent], [Enabled], [AuditTeamID]) VALUES (13, 1, N'GMP Audit of Pre Mix', 7, NULL, 1, 1)
GO
INSERT [tblInternalAuditRequirement] ([InternalAuditRequirementID], [AuditID], [AuditDescription], [Reoccurence], [LastAuditSent], [Enabled], [AuditTeamID]) VALUES (14, 1, N'GMP Audit of Flow Wrapper', 7, NULL, 1, 1)
GO
INSERT [tblInternalAuditRequirement] ([InternalAuditRequirementID], [AuditID], [AuditDescription], [Reoccurence], [LastAuditSent], [Enabled], [AuditTeamID]) VALUES (15, 1, N'GMP Audit of Mixing', 7, NULL, 1, 1)
GO
INSERT [tblInternalAuditRequirement] ([InternalAuditRequirementID], [AuditID], [AuditDescription], [Reoccurence], [LastAuditSent], [Enabled], [AuditTeamID]) VALUES (16, 1, N'GMP Audit of Mixing', 7, NULL, 1, 2)
GO
INSERT [tblInternalAuditRequirement] ([InternalAuditRequirementID], [AuditID], [AuditDescription], [Reoccurence], [LastAuditSent], [Enabled], [AuditTeamID]) VALUES (17, 1, N'GMP Audit of Grain Handling', 7, NULL, 1, 1)
GO
INSERT [tblInternalAuditRequirement] ([InternalAuditRequirementID], [AuditID], [AuditDescription], [Reoccurence], [LastAuditSent], [Enabled], [AuditTeamID]) VALUES (18, 2, N'GMP Audit of Warehouse A', 14, NULL, 1, 1)
GO
INSERT [tblInternalAuditRequirement] ([InternalAuditRequirementID], [AuditID], [AuditDescription], [Reoccurence], [LastAuditSent], [Enabled], [AuditTeamID]) VALUES (19, 2, N'GMP Audit of Warehouse B', 14, NULL, 1, 1)
GO
INSERT [tblInternalAuditRequirement] ([InternalAuditRequirementID], [AuditID], [AuditDescription], [Reoccurence], [LastAuditSent], [Enabled], [AuditTeamID]) VALUES (20, 2, N'GMP Audit of Warehouse C', 14, NULL, 1, 1)
GO
INSERT [tblInternalAuditRequirement] ([InternalAuditRequirementID], [AuditID], [AuditDescription], [Reoccurence], [LastAuditSent], [Enabled], [AuditTeamID]) VALUES (21, 2, N'GMP Audit of Warehouse D', 14, NULL, 1, 1)
GO
INSERT [tblInternalAuditRequirement] ([InternalAuditRequirementID], [AuditID], [AuditDescription], [Reoccurence], [LastAuditSent], [Enabled], [AuditTeamID]) VALUES (22, 2, N'GMP Audit of Warehouse E', 14, NULL, 1, 1)
GO
INSERT [tblInternalAuditRequirement] ([InternalAuditRequirementID], [AuditID], [AuditDescription], [Reoccurence], [LastAuditSent], [Enabled], [AuditTeamID]) VALUES (23, 2, N'GMP Audit of Warehouse F', 14, NULL, 1, 1)
GO
INSERT [tblInternalAuditRequirement] ([InternalAuditRequirementID], [AuditID], [AuditDescription], [Reoccurence], [LastAuditSent], [Enabled], [AuditTeamID]) VALUES (24, 2, N'GMP Audit of Warehouse G', 14, NULL, 1, 1)
GO
INSERT [tblInternalAuditRequirement] ([InternalAuditRequirementID], [AuditID], [AuditDescription], [Reoccurence], [LastAuditSent], [Enabled], [AuditTeamID]) VALUES (25, 2, N'GMP Audit of Warehouse H', 14, NULL, 1, 1)
GO
INSERT [tblInternalAuditRequirement] ([InternalAuditRequirementID], [AuditID], [AuditDescription], [Reoccurence], [LastAuditSent], [Enabled], [AuditTeamID]) VALUES (26, 3, N'Exterior Inspection (condition of exterior of plant)', 14, NULL, 1, 3)
GO
INSERT [tblInternalAuditRequirement] ([InternalAuditRequirementID], [AuditID], [AuditDescription], [Reoccurence], [LastAuditSent], [Enabled], [AuditTeamID]) VALUES (27, 4, N'Food Defense Inspection', 14, NULL, 1, 3)
GO
SET IDENTITY_INSERT [tblInternalAuditRequirement] OFF
GO
Thanks for any insight.
June 27, 2014 at 7:25 am
Thank you for the nice sample and well formed question!
You are slightly over-complicating the query, check the parenthesis in the where clause.
😎
SELECT
AR.InternalAuditRequirementID
,AR.AuditTeamID
FROM tblInternalAuditRequirement AR
LEFT OUTER JOIN tblInternalAuditAssignment AA
ON AR.InternalAuditRequirementID = AA.InternalAuditRequirementID
WHERE (AA.CompletionDate IS NOT NULL OR AA.InternalAuditRequirementID IS NULL)
AND AR.Enabled = 'True'
Results
InternalAuditRequirementID AuditTeamID
-------------------------- -----------
1 1
8 1
14 1
16 2
19 1
23 1
27 3
June 27, 2014 at 9:10 am
Thanks for the compliment on the sample and explanation... However, I still didn't explain it 100% correctly :-). The query you gave is exactly what I asked for, but I need to throw another wrench in to the mix. Each audit can be assigned more than once. It's possible that one is completed, and the other one isn't. If there are ANY that are not complete, I would like to eliminate that from the results.
To simulate this with the data already created, add a record in tblInternalAuditAssignment with an InternalAuditRequirementID of 8 and with no completion date (the rest of the fields can be arbitrary numbers/dates). The results should be the same, except with the InternalAuditRequirementID of 8 missing because there is at least one audit with that number that isn't completed. I think that's what I was trying to accomplish when I was using a GROUP clause, but didn't do it correctly. The desired results would be as shown below, but the results of the query you gave would produce the same results as before:
InternalAuditRequirementID AuditTeamID
-------------------------- -----------
1 1
14 1
16 2
19 1
23 1
27 3
Sorry for not being 100% clear - no matter how hard I try, I always forget something.
Kevin
June 27, 2014 at 10:23 am
While as a rule, I'm somewhat loathe to the idea of using correlated subqueries, I believe this should do what you're asking. This keeps the left join and ensures there are no records in the right table with a null completion date. At this point, the additional check where aa.InternalAuditRequirementID is null is moot because it's covered by the subquery.
select
ar.InternalAuditRequirementID,
ar.AuditTeamID,
*
from tblInternalAuditRequirement ar
left outer join tblInternalAuditAssignment aa
on ar.InternalAuditRequirementID = aa.InternalAuditRequirementID
where not exists (select aa2.InternalAuditRequirementID
from tblInternalAuditAssignment aa2
where aa.InternalAuditRequirementID = aa2.InternalAuditRequirementID
and aa2.CompletionDate is null)
and ar.Enabled = 'true'
-- EDIT: I threw that query together somewhat hastily. It seems to work for the sample data, but make sure to verify that it does everything you want against the full data set. There's quite a bit of funky null logic going on.
June 27, 2014 at 1:11 pm
skilly2 (6/27/2014)
Thanks for the compliment on the sample and explanation... However, I still didn't explain it 100% correctly :-). The query you gave is exactly what I asked for, but I need to throw another wrench in to the mix. Each audit can be assigned more than once. It's possible that one is completed, and the other one isn't. If there are ANY that are not complete, I would like to eliminate that from the results.To simulate this with the data already created, add a record in tblInternalAuditAssignment with an InternalAuditRequirementID of 8 and with no completion date (the rest of the fields can be arbitrary numbers/dates). The results should be the same, except with the InternalAuditRequirementID of 8 missing because there is at least one audit with that number that isn't completed. I think that's what I was trying to accomplish when I was using a GROUP clause, but didn't do it correctly. The desired results would be as shown below, but the results of the query you gave would produce the same results as before:
InternalAuditRequirementID AuditTeamID-------------------------- -----------
1 1
14 1
16 2
19 1
23 1
27 3
Sorry for not being 100% clear - no matter how hard I try, I always forget something.
Kevin
No worries, here it is
😎
USE tempdb;
GO
;WITH AUDIT_ASSIGNEMENT AS
(
SELECT
X.InternalAuditRequirementID
,X.AuditAssignmentID
,ISNULL(X.CompletionDate,CAST(0 AS DATETIME)) AS CompletionDate
FROM tblInternalAuditAssignment X
)
,AUDIT_LIST AS
(
SELECT
AR.InternalAuditRequirementID
,AR.AuditTeamID
,AA.AuditAssignmentID
,NULLIF((MIN(AA.CompletionDate) OVER
(
PARTITION BY AA.InternalAuditRequirementID
)),CAST(0 AS DATETIME)) AS CompletionDate
FROM tblInternalAuditRequirement AR
LEFT OUTER JOIN AUDIT_ASSIGNEMENT AA
ON AR.InternalAuditRequirementID = AA.InternalAuditRequirementID
WHERE AR.Enabled = 'True'
)
SELECT
AL.InternalAuditRequirementID
,AL.AuditTeamID
FROM AUDIT_LIST AL
WHERE (AL.CompletionDate IS NOT NULL OR AL.AuditAssignmentID IS NULL)
ORDER BY AL.InternalAuditRequirementID;
Results
InternalAuditRequirementID AuditTeamID
-------------------------- -----------
1 1
14 1
16 2
19 1
23 1
27 3
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply