Using functions for calculations
Posted by Jeff Rix on June 29, 2011 under DBA | 
Be the First to Comment
I need to calculate the detention attendance rate. In order to do this I need to get a count of detentions closed and a count of detentions attended.
–Determines Percentage of Students that have attended
SELECT A.DETENTION_DATE,
CAST(CAST(A.COUNT_CLOSED AS DECIMAL(4,2))/ CAST(B.COUNT_TOTAL AS DECIMAL(4,2)) AS DECIMAL(4,2)) as Percentage
FROM
–Gets Closed count by Detention Date
(
select DETENTION_DATE, STATUS, COUNT(*) as Count_Closed
from Discipline.dbo.DETENTION_DATE
where status = ‘Closed’
group by DETENTION_DATE, status
) as a
join
–Gets Total count by Detention Date
(
select DETENTION_DATE, COUNT(*) as Count_Total
from Discipline.dbo.DETENTION_DATE
group by DETENTION_DATE
) as b on a.DETENTION_DATE = b.DETENTION_DATE
SELECT A.DETENTION_DATE,
CAST(CAST(A.COUNT_CLOSED AS DECIMAL(4,2))/ CAST(B.COUNT_TOTAL AS DECIMAL(4,2)) AS DECIMAL(4,2)) as Percentage
FROM
–Gets Closed count by Detention Date
(
select DETENTION_DATE, STATUS, COUNT(*) as Count_Closed
from Discipline.dbo.DETENTION_DATE
where status = ‘Closed’
group by DETENTION_DATE, status
) as a
join
–Gets Total count by Detention Date
(
select DETENTION_DATE, COUNT(*) as Count_Total
from Discipline.dbo.DETENTION_DATE
group by DETENTION_DATE
) as b on a.DETENTION_DATE = b.DETENTION_DATE
If I put this into a function and then call the function I will get a lower cost way to calculate detention attendance rates.
Here is the code to put the select statement into a function.
CREATE FUNCTION [dbo].[CalcAttendancePercent]
(@DetentionDate Datetime) Returns DECIMAL(4,2)
AS
BEGIN
DECLARE @Percentage as DECIMAL(4,2)
SELECT @Percentage = CAST(A.COUNT_CLOSED AS DECIMAL(4,2))/ CAST(B.COUNT_TOTAL AS DECIMAL(4,2))
FROM
(
select DETENTION_DATE, STATUS, COUNT(*) as Count_Closed
from Discipline.dbo.DETENTION_DATE
where status = ‘Closed’ AND DETENTION_DATE = @DetentionDate
group by DETENTION_DATE, status
) as a
join
(
select DETENTION_DATE, COUNT(*) as Count_Total
from Discipline.dbo.DETENTION_DATE
where DETENTION_DATE = @DetentionDate
group by DETENTION_DATE
) as b on a.DETENTION_DATE = b.DETENTION_DATE
RETURN @Percentage
END
(@DetentionDate Datetime) Returns DECIMAL(4,2)
AS
BEGIN
DECLARE @Percentage as DECIMAL(4,2)
SELECT @Percentage = CAST(A.COUNT_CLOSED AS DECIMAL(4,2))/ CAST(B.COUNT_TOTAL AS DECIMAL(4,2))
FROM
(
select DETENTION_DATE, STATUS, COUNT(*) as Count_Closed
from Discipline.dbo.DETENTION_DATE
where status = ‘Closed’ AND DETENTION_DATE = @DetentionDate
group by DETENTION_DATE, status
) as a
join
(
select DETENTION_DATE, COUNT(*) as Count_Total
from Discipline.dbo.DETENTION_DATE
where DETENTION_DATE = @DetentionDate
group by DETENTION_DATE
) as b on a.DETENTION_DATE = b.DETENTION_DATE
RETURN @Percentage
END
Here is the code to call the function.
–using a function to calculate the percentage
SELECT DETENTION_DATE, dbo.CalcAttendancePercent(DETENTION_DATE) as Percentage
FROM Discipline.dbo.DETENTION_DATE
GROUP BY DETENTION_DATE
SELECT DETENTION_DATE, dbo.CalcAttendancePercent(DETENTION_DATE) as Percentage
FROM Discipline.dbo.DETENTION_DATE
GROUP BY DETENTION_DATE
The code that calls the function has a lower cost than using the original sql statement.
