January 3, 2017 at 8:14 am
I'm trying to find the first occurrence of a specific incident during a given time period. An example would be a first time award winner is defined by the number of employees in a specific business having received their first award in a given year. So while the total awards might be 1000 for a year the number of those receiving their first award would be slightly less than 1000.
Each employee has a unique employee ID
Whether or not they received an award that year would be 1 - received award or 0 - did not receive award
Award date.
So, for example the results would be - 2012 there were 30 employees who received their first award, 2013 there were 102 employees who received their first award, etc
Any assistance would be greatly appreciated.
January 3, 2017 at 8:17 am
jon.wilson (1/3/2017)
I'm trying to find the first occurrence of a specific incident during a given time period. An example would be a first time award winner is defined by the number of employees in a specific business having received their first award in a given year. So while the total awards might be 1000 for a year the number of those receiving their first award would be slightly less than 1000.Each employee has a unique employee ID
Whether or not they received an award that year would be 1 - received award or 0 - did not receive award
Award date.
So, for example the results would be - 2012 there were 30 employees who received their first award, 2013 there were 102 employees who received their first award, etc
Any assistance would be greatly appreciated.
It will be much easier to answer this with some sample data please. As it is, anything you get would be a complete shot in the dark. I suspect the solution is pretty straightforward but without an idea of the data it will just be a guess.
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
January 3, 2017 at 8:31 am
It sounds like you want something like the following.
SELECT employee_id
FROM awards
GROUP BY employee_id
HAVING YEAR(MIN(award_dt)) = YEAR(GETDATE())
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 3, 2017 at 8:35 am
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] ([EmployeeID], [Award], [AwardDate]) VALUES (3, 0, NULL)
GO
INSERT [dbo].[tmpRecord] ([EmployeeID], [Award], [AwardDate]) VALUES (4, 1, CAST(N'2011-10-26T12:16:52.840' AS DateTime))
GO
INSERT [dbo].[tmpRecord] ([EmployeeID], [Award], [AwardDate]) VALUES (5, 0, NULL)
GO
INSERT [dbo].[tmpRecord] ([EmployeeID], [Award], [AwardDate]) VALUES (6, 0, NULL)
GO
INSERT [dbo].[tmpRecord] ([EmployeeID], [Award], [AwardDate]) VALUES (7, 1, CAST(N'2014-09-08T02:29:48.160' AS DateTime))
GO
INSERT [dbo].[tmpRecord] ([EmployeeID], [Award], [AwardDate]) VALUES (8, 1, CAST(N'2015-12-24T21:05:00.860' AS DateTime))
GO
INSERT [dbo].[tmpRecord] ([EmployeeID], [Award], [AwardDate]) VALUES (9, 0, NULL)
GO
INSERT [dbo].[tmpRecord] ([EmployeeID], [Award], [AwardDate]) VALUES (10, 0, NULL)
GO
INSERT [dbo].[tmpRecord] ([EmployeeID], [Award], [AwardDate]) VALUES (1, 1, CAST(N'2016-06-10T19:53:00.620' AS DateTime))
GO
INSERT [dbo].[tmpRecord] ([EmployeeID], [Award], [AwardDate]) VALUES (2, 1, CAST(N'2008-01-08T19:30:54.680' AS DateTime))
GO
INSERT [dbo].[tmpRecord] ([EmployeeID], [Award], [AwardDate]) VALUES (3, 1, CAST(N'2016-09-15T05:57:00.590' AS DateTime))
GO
INSERT [dbo].[tmpRecord] ([EmployeeID], [Award], [AwardDate]) VALUES (4, 1, CAST(N'2012-12-25T07:23:56.460' AS DateTime))
GO
INSERT [dbo].[tmpRecord] ([EmployeeID], [Award], [AwardDate]) VALUES (5, 0, NULL)
GO
INSERT [dbo].[tmpRecord] ([EmployeeID], [Award], [AwardDate]) VALUES (6, 0, NULL)
GO
INSERT [dbo].[tmpRecord] ([EmployeeID], [Award], [AwardDate]) VALUES (7, 1, CAST(N'2011-01-27T13:33:58.380' AS DateTime))
GO
INSERT [dbo].[tmpRecord] ([EmployeeID], [Award], [AwardDate]) VALUES (8, 0, NULL)
GO
INSERT [dbo].[tmpRecord] ([EmployeeID], [Award], [AwardDate]) VALUES (9, 0, NULL)
GO
INSERT [dbo].[tmpRecord] ([EmployeeID], [Award], [AwardDate]) VALUES (10, 1, CAST(N'2015-05-05T12:51:22.320' AS DateTime))
GO
INSERT [dbo].[tmpRecord] ([EmployeeID], [Award], [AwardDate]) VALUES (1, 0, NULL)
GO
INSERT [dbo].[tmpRecord] ([EmployeeID], [Award], [AwardDate]) VALUES (2, 0, NULL)
GO
INSERT [dbo].[tmpRecord] ([EmployeeID], [Award], [AwardDate]) VALUES (3, 1, CAST(N'2010-10-06T02:39:08.750' AS DateTime))
GO
INSERT [dbo].[tmpRecord] ([EmployeeID], [Award], [AwardDate]) VALUES (4, 1, CAST(N'2014-01-20T23:41:36.740' AS DateTime))
GO
INSERT [dbo].[tmpRecord] ([EmployeeID], [Award], [AwardDate]) VALUES (5, 0, NULL)
GO
INSERT [dbo].[tmpRecord] ([EmployeeID], [Award], [AwardDate]) VALUES (6, 1, CAST(N'2014-02-03T23:17:36.940' AS DateTime))
GO
INSERT [dbo].[tmpRecord] ([EmployeeID], [Award], [AwardDate]) VALUES (7, 0, NULL)
GO
INSERT [dbo].[tmpRecord] ([EmployeeID], [Award], [AwardDate]) VALUES (8, 1, CAST(N'2014-05-28T18:36:30.730' AS DateTime))
GO
INSERT [dbo].[tmpRecord] ([EmployeeID], [Award], [AwardDate]) VALUES (9, 1, CAST(N'2009-05-02T04:26:37.840' AS DateTime))
GO
INSERT [dbo].[tmpRecord] ([EmployeeID], [Award], [AwardDate]) VALUES (10, 0, NULL)
January 3, 2017 at 8:40 am
It would be helpful to have sample data and expected output. But I am thinking something like this:
select year(awarddate), employeeid, min(awarddate)
from sometable
where awardflag = 1
group by awarddate, employeeid
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 3, 2017 at 8:45 am
Sample data provided in previous post expected output would be
Year Number of First Time Award recipients
2012 40
2013 85
2014 105
January 3, 2017 at 8:49 am
jon.wilson (1/3/2017)
Sample data provided in previous post expected output would beYear Number of First Time Award recipients
2012 40
2013 85
2014 105
I see. I think you actually want the count of people who received the award for the first time in a given year.
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
January 3, 2017 at 8:53 am
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 receiver.
EX. Employee receives first award in 2012, then receives another award in 2013. They shouldn't be included in the 2013 count since their first award was in 2012.
January 3, 2017 at 9:08 am
TheSQLGuru (1/3/2017)
It would be helpful to have sample data and expected output. But I am thinking something like this:select year(awarddate), employeeid, min(awarddate)
from sometable
where awardflag = 1
group by awarddate, employeeid
I should have grouped by year(awarddate) up there.
Since it seems you just need a count, this should suffice:
;with cte as (select year(awarddate) as yr, employeeid, min(awarddate)
from sometable
where awardflag = 1
group by year(awarddate), employeeid)
select yr, count(*)
from cte
group by yr
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 3, 2017 at 9:27 am
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 award in 2012, then receives another award in 2013. They shouldn't be included in the 2013 count since their first award was in 2012.
January 3, 2017 at 9:35 am
jon.wilson (1/3/2017)
Sample data provided in previous post expected output would beYear Number of First Time Award recipients
2012 40
2013 85
2014 105
Since your sample data only contains 30 records total, it's impossible for there to be 40 first time award recipients in 2012. You're expected results MUST match the sample data.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 3, 2017 at 9:40 am
TheSQLGuru (1/3/2017)
TheSQLGuru (1/3/2017)
It would be helpful to have sample data and expected output. But I am thinking something like this:select year(awarddate), employeeid, min(awarddate)
from sometable
where awardflag = 1
group by awarddate, employeeid
I should have grouped by year(awarddate) up there.
Since it seems you just need a count, this should suffice:
;with cte as (select year(awarddate) as yr, employeeid, min(awarddate)from sometable
where awardflag = 1
group by year(awarddate), employeeid)
select yr, count(*)
from cte
group by yr
The CTE should only be grouped by the EmployeeID.
; 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
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 3, 2017 at 9:42 am
Let me clean up my data so you can get exact expected results
January 3, 2017 at 9:47 am
With your requirements amendment I think Drew's modification to my query should get you what you want.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 3, 2017 at 9:57 am
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
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply