have recently finished a question to get this result which allows me to return the year, the month, the counted total and the male and female stats for a registration table.
the table schema has the following..
id, firstname, lastname, registration_date (yyyymmdd), sex, dateofbirth
the thing I also need to incorporate into this sp is age grouping,
if this is possible the results will return as they do with the current structure of the sp although it will also look at the registrations date of birth and spit the results into age groups, i.e. 18-30, 31- 40 etc...
hopefully this isnt too much work with the current structure.
current format of DOB is 18/May/1930
please provide an example, many thanks
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
alter PROCEDURE [dbo].[stp_GetCountMonthWise]
SELECT TheYear = MAX(YEAR(registration_date)),
TheMonth = MAX(DATENAME(mm,registration_date)),
TheTotal = COUNT(id),
female = SUM(case when isnull(sex,'Unknown') = 'Female' then 1 else 0 end),
male = SUM(case when isnull(sex,'Unknown') = 'Male' then 1 else 0 end)
WHERE registration_date >= @startdate
AND registration_date <= @finishdate
group by YEAR(registration_date), datepart(mm,registration_date)
Order by YEAR(registration_date), datepart(mm,registration_date)