CODEWITHSUNDEEP
sql-server
Doonie Darkoo
Doonie Darkoo

Reputation: 1495

Get monthly attendance for an employee

I have a query that a guy helped here in developing which gives fine result. I want to use this query in a function which later I want to use in a procedure.

CREATE FUNCTION [dbo].[fnGetWorkedDays] (@StartDate datetime, @EndDate datetime)
RETURNS int
AS
BEGIN
     DECLARE @dateFrom datetime 
     DECLARE @dateTo datetime 
     SET @dateFrom = @StartDate 
     SET @dateTo = @EndDate


     DECLARE @DAYSWORKED INT   
     SELECT @DAYSWORKED = (
   SELECT EmpId,  COUNT(*) as DaysWorked 
    FROM
    (
      SELECT DISTINCT EmpId,CAST(TimeIn AS DATE) AS [Date]
      FROM myTable
      WHERE TimeIn IS NOT NULL
      AND CAST(TimeIn AS DATE) BETWEEN @StartDate AND @EndDate
    )T
    GROUP BY EmpId
    ORDER BY EmpId)
RETURN ISNULL(@DAYSWORKED,0)

END

The very first error I am getting is that 

Msg 1033, Level 15, State 1, Procedure fnGetWorkedDays, Line 24
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

I removed ORDER BY then I got another error says

Msg 116, Level 16, State 1, Procedure fnGetWorkedDays, Line 25
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Upvotes: 1

Views: 222

Answers (2)

Mahesh.K
Mahesh.K

Reputation: 901

Is this what you are looking for .?

CREATE FUNCTION [dbo].[fnGetWorkedDays] (@StartDate datetime, @EndDate datetime)
RETURNS @DaysWorked TABLE   
   (  
 Empid Int,   
    DaysWorked  Int   
   ) 
AS
BEGIN 


insert into @DaysWorked (Empid,DaysWorked)

  SELECT EmpId,  COUNT(*) as DaysWorked 
    FROM
    (
      SELECT DISTINCT EmpId,CAST(TimeIn AS DATE) AS [Date]
      FROM myTable
      WHERE TimeIn IS NOT NULL
      AND CAST(TimeIn AS DATE) BETWEEN @StartDate AND @EndDate
    )T
    GROUP BY EmpId
    ORDER BY EmpId

     RETURN 
END

select * from [fnGetWorkedDays]('2018-01-01','2018-01-31')

Upvotes: 0

PSK
PSK

Reputation: 17943

I suggest you to go though this to understand more on table valued functions vs scalar valued function Difference between scalar, table-valued, and aggregate functions in SQL server?

Regarding your scenario, you should be writing a table values function for this like following.

CREATE FUNCTION [dbo].[fnGetWorkedDays]
 (
  @StartDate AS DATETIME,
  @EndDate datetime
 )
RETURNS TABLE
AS
RETURN
   SELECT EmpId,  COUNT(*) as DaysWorked 
    FROM
    (
      SELECT DISTINCT EmpId,CAST(TimeIn AS DATE) AS [Date]
      FROM myTable
      WHERE TimeIn IS NOT NULL
      AND CAST(TimeIn AS DATE) BETWEEN @StartDate AND @EndDate
    )T
    GROUP BY EmpId

To use the function you can write your queries like following.

DECLARE @StartDate datetime
DECLARE @EndDate datetime
SET @StartDate = '2018-01-01'
SET @EndDate = '2018-01-31'
select * from  [dbo].[fnGetWorkedDays](@StartDate,@EndDate)

If you want this to work as a scalar value function, in that case you need to pass EmpId as parameter like following.

CREATE FUNCTION [dbo].[fnGetWorkedDaysEmpWise]
 (
  @StartDate AS DATETIME,
  @EndDate datetime,
  @EmpId INT
 )
RETURNS INT
AS
BEGIN
DECLARE @RetunCount INT

      SELECT @RetunCount = COUNT(DISTINCT CAST(TimeIn AS DATE))
      FROM myTable
      WHERE TimeIn IS NOT NULL
      AND CAST(TimeIn AS DATE) BETWEEN @StartDate AND @EndDate
      AND EmpID=@EmpId
      RETURN @RetunCount

  END

And you can use it directly in your select clause like following.

DECLARE @StartDate datetime
DECLARE @EndDate datetime
SET @StartDate = '2018-01-01'
SET @EndDate = '2018-01-31'
SELECT [dbo].[fnGetWorkedDaysEmpWise](@StartDate,@EndDate,1)

Upvotes: 3

Related Questions