select
    dept,
    max(createdate) as 'last user completion'
from
    users u
where
    not exists (
        select 1
        from
            users
        where
            u.dept = dept and
            complete is null)
group by
    dept

quote:

---

We don't need no stinkin' sample data!

---

USE Northwind
GO

CREATE TABLE Users (
	  UserId 	int		NOT NULL-- ID of this user
	, Completed 	datetime 	NULL 	-- date this user "completed"
	, CreateDate 	datetime	NOT NULL-- Date this user record was added
	, Dept 		int		NOT NULL-- Unique department identifier
)
GO

INSERT INTO Users (Dept, UserID, Completed, CreateDate) 
SELECT 5, 1, 'May 1 2003',  'Jan 1 2003' UNION ALL
SELECT 5, 2, 'May 6 2003',  'Jan 1 2003' UNION ALL
SELECT 5, 3, 'May 9 2003',  'Jan 1 2003' UNION ALL
SELECT 5, 4, 'Jun 2 2003',  'Jan 1 2003' UNION ALL
SELECT 5, 5, 'Aug 9 2003',  'Aug 1 2003' UNION ALL
SELECT 4, 6, 'May 3 2003',  'Jan 1 2003' UNION ALL
SELECT 4, 7, 'May 11 2003', 'Jan 1 2003' UNION ALL
SELECT 4, 8, 'May 18 2003', 'Jan 1 2003' UNION ALL
SELECT 4, 9, 'May 20 2003', 'Jan 1 2003' UNION ALL
SELECT 4, 10, NULL        , 'July 1 2003'
GO

DECLARE @StartEnrollPeriod datetime, @EndEnrollPeriod datetime

SELECT @StartEnrollPeriod = 'Jan 1 2003',  @EndEnrollPeriod = 'Jan 31 2003'

SELECT * FROM Users WHERE Completed = (
	SELECT MIN(Max_Completed) AS Min_Max_Completed FROM (
		  SELECT Dept, Max(Completed) As Max_Completed 
		    FROM Users 
		   WHERE CreateDate >= @StartEnrollPeriod
		     AND CreateDate <=  @EndEnrollPeriod
		GROUP BY Dept) AS XXX)


DROP TABLE Users
GO

quote:

---

Originally posted by SamC

Hi Brett and Thanks,

---

DROP TABLE dbo.xUsers
GO

CREATE TABLE dbo.xUsers (
	  UserId 	int		NOT NULL-- ID of this user
	, Completed 	datetime 	NULL 	-- date this user "completed"
	, CreateDate 	datetime	NOT NULL-- Date this user record was added
	, Dept 		int		NOT NULL-- Unique department identifier
)
GO

INSERT INTO xUsers (Dept, UserID, Completed, CreateDate) 
SELECT 5, 1, 'May 1 2003',  'Jan 1 2003' UNION ALL
SELECT 5, 2, 'May 6 2003',  'Jan 1 2003' UNION ALL
SELECT 5, 3, 'May 9 2003',  'Jan 1 2003' UNION ALL
SELECT 5, 4, 'Jun 2 2003',  'Jan 1 2003' UNION ALL
SELECT 5, 5, 'Aug 9 2003',  'Aug 1 2003' UNION ALL
SELECT 4, 6, 'May 3 2003',  'Jan 1 2003' UNION ALL
SELECT 4, 7, 'May 11 2003', 'Jan 1 2003' UNION ALL
SELECT 4, 8, 'May 18 2003', 'Jan 1 2003' UNION ALL
SELECT 4, 9, 'May 20 2003 05:31AM', 'Jan 1 2003' UNION ALL
SELECT 4, 10, NULL        , 'July 1 2003'
GO
DROP TABLE dbo.xTally
GO
CREATE TABLE dbo.xTally (NextDate DATETIME NOT NULL )
GO

-- Begin the procedure here
SET NOCOUNT ON 

-- Build a Talley Table
-- Set StartDate and EndDate to midnight each for clean boundries
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
DECLARE @NextDate DATETIME

SELECT @StartDate = CONVERT(VARCHAR(100), MIN(CreateDate), 101) FROM dbo.xUsers
SET @EndDate = CONVERT(VARCHAR(100), DATEADD(dd, 1, GETDATE()), 101)

-- Initialize the Tally table
SET @NextDate = @StartDate

WHILE @NextDate <= @EndDate BEGIN
	INSERT INTO dbo.xTally (NextDate) VALUES (@NextDate) -- this is gonna take time to run
	SET @NextDate = DATEADD(DD, 1, @NextDate) -- some days seem longer than others don't they?
END

-- Create an index now for speed
CREATE INDEX IN_Tally ON dbo.Xtally (NextDate)

-- The Tally Table enables the calculation of each department's Total Users and Completed users for every day from StartDate to EndDate

-- Group the Tally results by Department and find the earliest Date for each Department reaching 100% completion
SELECT	Dept, DATEADD(DD, -1, MIN(NextDate)) AS CompletedDate
	FROM (
		SELECT	Dept, NextDate,-- This select shows Dept, NextDate, Total Users and Completed Users, all dates
			COUNT(*) AS TotalUsers, 
			COUNT(ALL CASE WHEN Completed IS NULL THEN NULL WHEN Completed < NextDate THEN 1 ELSE NULL END) AS CompletedUsers
			FROM	dbo.xUsers U
			CROSS JOIN dbo.xTally T
			WHERE	U.CreateDate < T.NextDate
			GROUP BY NextDate, Dept
		) A
	WHERE TotalUsers = CompletedUsers
	GROUP BY Dept

RETURN

DROP TABLE dbo.xUsers
DROP TABLE dbo.xTally
GO