declare @year int;
declare @sum int;
truncate table dbo.temp
declare cur Cursor
for
select distinct year(ModifiedDate) from Person.Person order by year(ModifiedDate) asc ;
open cur;
fetch next from cur into @year
while @@FETCH_STATUS=0
begin
print @year
insert into dbo.temp select year(ModifiedDate) ,PersonType,count(BusinessEntityID) from Person.Person group by year(ModifiedDate),PersonType having year(ModifiedDate)=@year ;
set @sum=0
select @sum=count(BusinessEntityID) from person.Person where year(ModifiedDate)=@year
insert into dbo.temp values(null,convert(nvarchar(10),@year), @sum)
fetch next from cur into @year
end
select * from dbo.temp
close cur
DEALLOCATE cur
years persontyoe counts
2006 EM 1
2006 GC 1
NULL 2006 2
2007 EM 8
2007 GC 8
NULL 2007 16
2008 EM 83
2008 GC 83
NULL 2008 166
2009 EM 135
2009 GC 135
NULL 2009 270
2010 EM 36
2010 SP 1
2010 GC 36
NULL 2010 73
2011 EM 5
2011 IN 1201
2011 SP 9
2011 SC 238
2011 VC 63
2011 GC 14
NULL 2011 1530
2012 IN 2743
2012 SP 4
2012 SC 250
2012 VC 93
2012 GC 4
NULL 2012 3094
2013 EM 3
2013 IN 8522
2013 SP 3
2013 SC 251
2013 GC 6
NULL 2013 8785
2014 EM 2
2014 IN 6018
2014 SC 4
2014 GC 2
NULL 2014 6026
2015 SC 10
NULL 2015 10
declare @sum int;
truncate table dbo.temp
declare cur Cursor
for
select distinct year(ModifiedDate) from Person.Person order by year(ModifiedDate) asc ;
open cur;
fetch next from cur into @year
while @@FETCH_STATUS=0
begin
print @year
insert into dbo.temp select year(ModifiedDate) ,PersonType,count(BusinessEntityID) from Person.Person group by year(ModifiedDate),PersonType having year(ModifiedDate)=@year ;
set @sum=0
select @sum=count(BusinessEntityID) from person.Person where year(ModifiedDate)=@year
insert into dbo.temp values(null,convert(nvarchar(10),@year), @sum)
fetch next from cur into @year
end
select * from dbo.temp
close cur
DEALLOCATE cur
years persontyoe counts
2006 EM 1
2006 GC 1
NULL 2006 2
2007 EM 8
2007 GC 8
NULL 2007 16
2008 EM 83
2008 GC 83
NULL 2008 166
2009 EM 135
2009 GC 135
NULL 2009 270
2010 EM 36
2010 SP 1
2010 GC 36
NULL 2010 73
2011 EM 5
2011 IN 1201
2011 SP 9
2011 SC 238
2011 VC 63
2011 GC 14
NULL 2011 1530
2012 IN 2743
2012 SP 4
2012 SC 250
2012 VC 93
2012 GC 4
NULL 2012 3094
2013 EM 3
2013 IN 8522
2013 SP 3
2013 SC 251
2013 GC 6
NULL 2013 8785
2014 EM 2
2014 IN 6018
2014 SC 4
2014 GC 2
NULL 2014 6026
2015 SC 10
NULL 2015 10