asked
Current total per year
Hello, I would like to know how do I count total for current students for each year from 2000 onwards. they have starting date and end date. I would like to display in such manner: Year Total 2000 24 2001 30 2002 28 I've used group by before but couldnt get it too work for current total for that particular year. I keep getting ended total for that year instead. and onwards. Thanks so much
If somebody starts in 2015 and ends in 2016 I presume they count as a 1 in both years? You could build a table of years with their start and end dates, join your table to that where the two date ranges overlap, and group the counts by the year number. This code will do you a year table: if object_id('tempdb..#Year') is not null drop table #Year; create table #Year ( [Year] int not null , YearStart date not null , YearEnd date not null ); declare @Year int = 2000; while @Year ]
Using @David Wimbush suggestion and assuming that you have a table names Students which includes the columns named StartDate and EndDate, you can just join the years with students based on the condition that the year is between the year datepart of the student's start and end date. For the student who, for example, has the start date sometime in 2010 and end date sometime in 2013 the join will return (and include into count) 4 rows. For the student who started in Spring of 2012 and finished the same summer, the join will return only one row, counting him in only once. Please note that if your Students table has millions of rows then the join on the column to which the datepart function is applied is generally speaking an ill advised practice, because the predicate is non-sargable (please read the [article by Brad McGehee][1]). If you run into any problems with performance, please restate the data in the part returning the years and the join condition as needed. You can use the master.dbo.spt_values table (everyone has select permissions to it) to quickly return the data consisting of one column one row per year. Here is the query which will work: select years.number [Year], count(1) StudentCount from ( select number from master.dbo.spt_values where [type] = 'P' and number between 2000 and 2016 ) years inner join dbo.Student s on years.number between datepart(year, s.StartDate) and datepart(year, s.EndDate) group by number; Hope this helps. Oleg [1]:
http://www.sql-server-performance.com/2007/t-sql-where/
19 People are following this question.
