DOB per Month - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

DOB per Month

Author

Topic

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2008-04-23 : 07:24:39

Hi

Does anyone know a way of counting the number of employees with a date of birth in each month.

ie.
Jan 16
Feb 11
Mar 20
etc...

Also how would the months be displayed as their names as opposed to the month number?

Thanks

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2008-04-23 : 07:29:40

for the first one try some thing like

Select DateName(mm, <urDOB column>, COUNT(EmployeeId)
From <tblName>
Group By DateName(mm, <urDOB column>

for the second one it is not clear what u really want...
can u be more specific

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-23 : 07:30:58

[code]DECLARE @Sample TABLE (dob DATETIME)

INSERT @Sample
SELECT '1975 Jan 16' UNION ALL
SELECT '1979 Feb 11' UNION ALL
SELECT '1979 Feb 17' UNION ALL
SELECT '1970 Mar 20'

SELECT DATEPART(YEAR, dob) AS theYear,
DATENAME(MONTH, dob) AS theMonth,
COUNT(*) AS Birthdays
FROM @Sample
GROUP BY DATEPART(YEAR, dob),
DATENAME(MONTH, dob),
DATEPART(MONTH, dob)
ORDER BY DATEPART(YEAR, dob),
DATEPART(MONTH, dob)

SELECT DATENAME(MONTH, dob) AS theMonth,
COUNT(*) AS Birthdays
FROM @Sample
GROUP BY DATENAME(MONTH, dob),
DATEPART(MONTH, dob)
ORDER BY DATEPART(MONTH, dob)[/code]

E 12°55'05.25"
N 56°04'39.16"

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2008-04-23 : 07:33:41

declare @t table(dob datetime)
insert @t
select '01/01/2007' union all
select '01/01/2008' union all
select '01/24/1980' union all
select '01/03/2007' union all
select '01/24/2007'

select
datename(month, dob) + ' ' + cast(datepart(dd, dob) as varchar),
count(*) as 'Total Employees'
from
@t
group by
datename(month, dob) + ' ' + cast(datepart(dd, dob) as varchar)

--------------------------------------------------
S.Ahamed

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2008-04-23 : 07:44:39

Thanks Guys!!!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-04-23 : 08:53:37


SELECT		DATEADD(MONTH,DATEDIFF(MONTH,0,dob),0),
		COUNT(*) AS Birthdays
FROM		@Sample
GROUP BY	DATEADD(MONTH,DATEDIFF(MONTH,0,dob),0)

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources