Useage stats.sql

useagestats.sql

create table #SIRSusers (iCount int, cName varchar(100))
create table #TLXEusers (iCount int, cName varchar(100))
create table #HLSusers (iCount int, cName varchar(100))
create table #HLCusers (iCount int, cName varchar(100))
create table #totals (iCount int, cName varchar(100))



--HLS users
insert into #HLSusers 
select 
	COUNT(school.cName),
	school.cName
from (
	select 
		ROW_NUMBER() over (partition by cusername order by cusername ) as [rowid], 
		cusername, 
		cApplication
	from log
	where cUserName != ''
	and lSuccess = 1
	and cApplication = 'HomeLogic'
) a
join Student
	on student.cStudentNumber = a.cUserName
join School
	on school.iSchoolID = student.iSchoolID
where a.rowid = 1
and student.lCurrent = 1
group by school.cname 


--HLC users
insert into #HLCusers 
select 
	COUNT(school.cName),
	school.cName
from (
	select 
		ROW_NUMBER() over (partition by cusername order by cusername ) as [rowid], 
		cusername, 
		cApplication
	from log
	where cUserName != ''
	and lSuccess = 1
	and cApplication = 'HomeLogic'
) a
join Contact
	on contact.cusername = a.cUserName
join ContactRelation 
	on ContactRelation.iContactID = contact.iContactID
join student 
	on student.iStudentID = ContactRelation.iStudentID
join School
	on school.iSchoolID = student.iSchoolID
where a.rowid = 1
and student.lCurrent = 1
group by school.cname 



--SIRS users
insert into #SIRSusers 
select 
	COUNT(school.cName),
	school.cName
from (
	select 
		ROW_NUMBER() over (partition by cusername order by cusername ) as [rowid], 
		cusername, 
		cApplication
	from log
	where cUserName != ''
	and lSuccess = 1
	and cApplication = ''
) a
join Staff 
	on staff.cUserName = a.cUserName
join School
	on school.iSchoolID = staff.iSchoolID
where a.rowid = 1
group by school.cName



--TLXE users
insert into #TLXEusers 
select 
	COUNT(school.cName),
	school.cName
from (
	select 
		ROW_NUMBER() over (partition by cusername order by cusername ) as [rowid], 
		left(cusername, charindex('@', cusername)-1) as [cusername], 
		cApplication
	from log
	where cUserName != ''
	and cApplication like 'Teacherlogic XE'
) a
join Staff 
	on staff.cUserName = a.cUserName
join School
	on school.iSchoolID = staff.iSchoolID
where a.rowid = 1
group by school.cName



--total staff counts
insert into #totals
select count(school.cName), school.cName
from Staff
join School 
	on staff.iSchoolID = school.ischoolid
group by school.cName
order by 1 DESC


---

-- staff whole district
select
	'Calgary Board of Education' as 'District Name',
	SUM(#totals.iCount) as 'Total Staff in District',
	(isnull((select SUM(#SIRSusers.iCount) from #SIRSusers), 0) * 100 / SUM(#totals.iCount) ) as 'SIRS adoption %',
	(isnull((select SUM(#TLXEusers.iCount) from #TLXEusers), 0) * 100 / SUM(#totals.iCount) ) as 'TLXE adoption %',
	(select SUM(#SIRSusers.iCount) from #SIRSusers) as 'SIRS users (all time)',
	(select SUM(#TLXEusers.iCount) from #TLXEusers) as 'TLXE users (all time)'
from #totals


--students whole district
select top 1
	'Calgary Board of Education' as 'District Name',
	(select COUNT(*) from student where lCurrent = 1) as 'HL students (currently enrolled)',
	(	select COUNT(*) from contact 
		join ContactRelation 
			on ContactRelation.iContactID = contact.iContactID 
		join student 	
			on student.iStudentID = ContactRelation.iStudentID
		where student.lCurrent = 1) as 'HL contacts (of currently enrolled students)',
	(isnull((select SUM(#HLSusers.iCount) from #HLSusers), 0) * 100 / (select COUNT(*) from student where lCurrent = 1) ) as 'HL student adoption %',
	(isnull((select SUM(#HLSusers.iCount) from #HLSusers), 0) * 100 / (
		 select COUNT(*) from contact 
			join ContactRelation 
				on ContactRelation.iContactID = contact.iContactID 
			join student 	
				on student.iStudentID = ContactRelation.iStudentID
			where student.lCurrent = 1)) as 'HL contact adoption %',
	(isnull((select SUM(#HLSusers.iCount) from #HLSusers), 0)) as 'HL student users',
	(isnull((select SUM(#HLCusers.iCount) from #HLCusers), 0)) as 'HL contacts users'
from #HLSusers


--staff by school
select 
	#totals.cName as 'School Name',
	#totals.iCount as 'Total Staff in School',
	(isnull(#SIRSusers.iCount, 0) * 100 / #totals.iCount ) as 'SIRS adoption %',
	(isnull(#TLXEusers.iCount, 0) * 100 / #totals.iCount ) as 'TLXE adoption %',
	isnull(#SIRSusers.iCount, 0) as 'SIRS users (all time)',
	isnull(#TLXEusers.iCount, 0) as 'TLXE users (all time)'
from #totals
full join #SIRSusers
	on #totals.cName = #SIRSusers.cName
full join #TLXEusers
	on #totals.cName = #TLXEusers.cName
drop table #SIRSusers
drop table #TLXEusers
drop table #HLSusers
drop table #HLCusers
drop table #totals


----

i gave up on trying to make this nice after realizing the log table i was working on had different types of values in the same column, based on a flag.

redo by counting like this:

select Grade, count() * 100.0 / sum(count()) over()
from MyTable
group by Grade

?