Thanks for all of your input.  I was able to get what I needed from the scripts everyone provided.

That's pointing me in the right direction.  I was thinking more in line of pivoting on the cycle ids, but I should be able to work that out
Thanks

That did it. Thanks for all your help

; WITH Employee( EmployeeID, FirstAwardYear) AS (

SELECT tr.EmployeeID, YEAR(MIN(tr.AwardDate))

FROM #tmpRecord tr

WHERE tr.Award = 1

GROUP BY tr.EmployeeID

)

SELECT FirstAwardYear, COUNT(EmployeeID)

FROM Employee

GROUP BY Employee.FirstAwardYear

Let me clean up my data so you can get exact expected results

Getting there, but if they received an award in a previous year then they should not be counted as a first time award receiver in later years.

EX. Employee receives first...

Yes, you are correct. Sorry if I was misleading,

Also, if they received an award in a previous year then they should not be counted as a first time award...

Sample data provided in previous post expected output would be

Year Number of First Time Award recipients

2012 40

2013 ...

CREATE TABLE [dbo].[tmpRecord](

[EmployeeID] [int] NULL,

[Award] [bit] NULL,

[AwardDate] [datetime] NULL

) ON [PRIMARY]

GO

INSERT [dbo].[tmpRecord] ([EmployeeID], [Award], [AwardDate]) VALUES (1, 0, NULL)

GO

INSERT [dbo].[tmpRecord] ([EmployeeID], [Award], [AwardDate]) VALUES (2, 1, CAST(N'2011-01-22T14:10:26.560' AS DateTime))

GO

INSERT [dbo].[tmpRecord]...

Thank you, just what I needed

Thanks, sorry for the bad scripting on my part. I'll see what I can work out

Thanks for the help

Thanks, I think I can work with this. One quick question. Suppose the ticket column gets a value of 1 if it is a speeding ticket and a...

I think we're close, I'm looking to count the number of people who have received their 2nd ticket during the given time. Not the number of tickets per individual.

Thanks

EDIT:...

Thanks, that works for me.

The FY does not match up with Calendar year.

What I'm ultimately looking for are the number of records in a given FY where the RecordDate was from Oct...