I have a function that returns a table-value.
What i need to do is account for the user not putting in any values and setting defaults.
There are three parameter values that are passed to the function.
Starting Date
Ending Date
Employee Last Name
If the starting date is not supplied I would like it to default to the beginning of the current day.
if Ending Date is not supplied it should default to the close of the current day.
If the employee name is not given it should default to all employees.
Here is the code for the function.
Thanks
John Fuhrman
What i need to do is account for the user not putting in any values and setting defaults.
There are three parameter values that are passed to the function.
Starting Date
Ending Date
Employee Last Name
If the starting date is not supplied I would like it to default to the beginning of the current day.
if Ending Date is not supplied it should default to the close of the current day.
If the employee name is not given it should default to all employees.
Here is the code for the function.
Code:
USE [MailroomTracking]
GO
/****** Object: UserDefinedFunction [dbo].[fn_QC_Employee_Report] Script Date: 06/23/2010 12:34:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: John Fuhrman
-- Create date: 2010/06/22
-- Description: QC Report
-- =============================================
ALTER FUNCTION [dbo].[fn_QC_Employee_Report]
(
-- Add the parameters for the function here
@Enter_Starting_Date DateTime,
@Enter_Ending_Date DateTime,
@Enter_Employee_Last_Name VarChar(50)
)
RETURNS TABLE
AS
RETURN
(
-- Add the SELECT statement with parameter references here
SELECT TOP (100) PERCENT
dbo.tblTrackingTable.Tracking_ID,
EmployeeFullName = Case
When EmployeeMI = '' Then EmployeeLN + ', ' + EmployeeFN
When EmployeeMI Is Null Then EmployeeLN + ', ' + EmployeeFN
When EmployeeMI <> '' Then EmployeeLN + ', ' + EmployeeFN + ' ' + EmployeeMI
End,
dbo.tblEmployee.EmployeeFN,
dbo.tblEmployee.EmployeeLN,
dbo.tblTrackingTable.EmployeeID,
dbo.tblTrackingTable.MachineName,
UPPER(dbo.tblTrackingTable.BoxNumber) AS BoxNumber,
UPPER(dbo.tblTrackingTable.FileNumber) AS FileNumber,
dbo.tblTrackingTable.TrackingDate
FROM dbo.tblTrackingTable FULL OUTER JOIN
dbo.tblEmployee ON dbo.tblTrackingTable.EmployeeID = dbo.tblEmployee.EmployeeID
WHERE (dbo.tblTrackingTable.TrackingDate
BETWEEN DATEADD(dd, DATEDIFF(dd, 0, @Enter_Starting_Date), 0)
AND DATEADD(ss, - 1, DATEADD(dd, DATEDIFF(dd, 0, @Enter_Ending_Date), 1)))
AND (dbo.tblEmployee.EmployeeLN Like '%' + @Enter_Employee_Last_Name + '%')
AND (dbo.tblTrackingTable.EmployeeID IS NOT NULL)
AND (dbo.tblTrackingTable.FileNumber <> '')
AND (dbo.tblTrackingTable.BoxNumber <> '')
AND (dbo.tblTrackingTable.FileNumber <> '.BOX.END.')
AND (dbo.tblTrackingTable.TrackingDate IS NOT NULL)
ORDER BY dbo.tblTrackingTable.TrackingDate, BoxNumber
)Thanks
John Fuhrman