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
 
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
 
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
 
The code that calls the function has a lower cost than using the original sql statement.

image

Tags: ,

Using scalar functions to concatenate strings

Posted by Jeff Rix on June 27, 2011 under T-SQL | Be the First to Comment

Create Function- Create function to accept Detention ID and return all comments concatenated for the Detention ID.

 

  SET NOCOUNT ON;

  USE Discipline;

 

–DROP FUNCTION

  IF OBJECT_ID(‘dbo.ConcatDetentionComments’, ‘FN’) IS NOT NULL

  DROP FUNCTION dbo.ConcatDetentionComments;

GO

 

–CREATE FUNCTION

  –CONCATENATE COMMENTS

CREATE FUNCTION dbo.ConcatDetentionComments

  (@detentionID INT) RETURNS VARCHAR(MAX)

AS

  BEGIN

  declare @COMMENTS AS VARCHAR(MAX)

  SET @COMMENTS = ”;

 

  SELECT @COMMENTS = @COMMENTS + COMMENT + ‘; ‘

  FROM Discipline.DBO.DETENTION_COMMENTS

  WHERE DETENTION_ID = @detentionID;

 

  RETURN @COMMENTS;

  END

GO

Test Results

–BEFORE DETENTION ID COMMENTS ARE CONCATENATED
SELECT DETENTION_ID, COMMENT
FROM Discipline.dbo.DETENTION_COMMENTS

–CONCATENATES COMMENTS WHERE MORE THAN ONE COMMENT EXISTS PER DETENTION ID
SELECT DISTINCT DETENTION_ID, dbo.ConcatDetentionComments(DETENTION_ID) AS COMMENTS
FROM Discipline.dbo.DETENTION_COMMENTS

image

Tags: , ,

home top