July 22, 2010 at 1:27 am
stored procedure
SELECT TOP (100) PERCENT a.Date as Date, emp.Employee_Name AS Employeename, a.Start_Time AS StartTime, a.End_Time AS EndTime, a.Remark,
CONVERT(decimal(6, 3), DATEDIFF(minute, CONVERT(varchar(100), a.Start_Time, 100), CONVERT(varchar(100), a.End_Time, 100)) / 60.0) AS diffhour,
SUM(t.Duration) AS totalhour,DATENAME (dw,convert(varchar(200),a.start_time,100))as days--, a.islate AS late
FROM dbo.Employee AS emp INNER JOIN
dbo.Attendance AS a ON a.Employee_ID = emp.Employee_ID LEFT OUTER JOIN
dbo.TaskSheet AS t ON a.Employee_ID = t.Emp_id AND a.Date = t.DateTime
WHERE (emp.Employee_Name = @name) AND (CONVERT(varchar(200), a.Date, 101) BETWEEN @startdate AND @enddate )
GROUP BY a.Date, emp.Employee_Name, a.islate, a.Start_Time, a.End_Time, a.Remark, CONVERT(decimal(6, 3),
DATEDIFF(minute, CONVERT(varchar(100),
a.Start_Time, 100), CONVERT(varchar(100), a.End_Time, 100)) / 60.0)
ORDER BY StartTime
Function:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER Function [dbo].[fnDateTable]
(
@StartDate datetime,
@EndDate datetime,
@DayPart char(5) -- support 'day','month','year','hour', default 'day'
)
Returns @Result Table
(
[Date] datetime
)
As
Begin
Declare @CurrentDate datetime
Set @CurrentDate=@StartDate
While @CurrentDate<=@EndDate
Begin
Insert Into @Result Values (@CurrentDate)
Select @CurrentDate=
Case
When @DayPart='year' Then DateAdd(yy,1,@CurrentDate)
When @DayPart='month' Then DateAdd(mm,1,@CurrentDate)
When @DayPart='hour' Then DateAdd(hh,1,@CurrentDate)
Else
DateAdd(dd,1,@CurrentDate)
End
End
Return
End
=========================================
i want to call this function in my stored procedure
July 22, 2010 at 1:38 am
The Function is of Table return type....
So U should join Function with your select Query...
Example:-
Select a.Col1,a.col2,a.col6,fn.Col1,fn.col2
from Tbl a join (Select * from fn_func(par1,par2) )fn on fn.fn_col1=a.col1
In your qurey need to join on Datetime....
July 22, 2010 at 10:07 am
I'm not sure how big your tables are, but the following will perform a table scan instead of using an index which will cause huge performance issues on larger tables
WHERE (emp.Employee_Name = @name)
AND (CONVERT(varchar(200), a.Date, 101) BETWEEN @startdate AND @enddate )
Also, what is the purpose of the function? What are you going to use it to do?
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply