MKz7172L

1Log function Parameters in Sql Server
2CREATE FUNCTION [dbo].[fn_CheckLeaveContinuation] (@emp_Id        INT,
3                                                   @leaveDate     DATETIME,
4                                                   @severityPoint INT)
5RETURNS BIT
6AS
7  BEGIN
8      --For the given  emp_id and the leave date, check if the given employee 
9      -- has already taken a leave on the previous day. Leaves in continuation 
10      -- should have severity point as Zero else return false
11      DECLARE @isLeaveContinued BIT;
12
13      --if it's a first leave of the employee then return true 
14      SELECT @isLeaveContinued= 
15            CASE
16               WHEN NOT EXISTS (SELECT *
17                                FROM   absenteeism
18                                WHERE  EMP_ID = @emp_ID)
19                    OR EXISTS (SELECT *
20                               FROM   absenteeism
21                               WHERE  DateDiff(DAY, @leaveDate, Absent_Date) = -1
22                                       AND EMP_ID = @emp_ID)
23                        AND @severityPoint = 0
24                    OR EXISTS (SELECT *
25                               FROM   Absenteeism
26                               WHERE  DateDiff(DAY, @leaveDate, Absent_Date) < -1
27                               )
28             THEN
29               'true'
30               ELSE 'false'
31             END
32
33      RETURN @isLeaveContinued
34  END
35	
36CREATE TABLE [dbo].[Absenteeism](
37    [EMP_ID] [int] NOT NULL,
38    [Absent_date] [datetime] NOT NULL,
39    [Reason_code] [char](40) NOT NULL,
40    [Severity_Points] [int] NOT NULL
41) ON [PRIMARY]
42
43GO
44SET ANSI_PADDING OFF
45GO
46ALTER TABLE [dbo].[Absenteeism]  WITH CHECK ADD  CONSTRAINT [chk_Leave_Continuation] CHECK  (([dbo].[fn_CheckLeaveContinuation]([Emp_ID],[Absent_date],[Severity_Points])='true'))
47GO
48ALTER TABLE [dbo].[Absenteeism] CHECK CONSTRAINT [chk_Leave_Continuation]
49GO
50ALTER TABLE [dbo].[Absenteeism]  WITH CHECK ADD CHECK  (([severity_points]>=(0) AND [severity_points]<=(4)))
51	
52NOT EXISTS (SELECT * FROM absenteeism WHERE  EMP_ID = @emp_ID)
53	
54EXISTS (
55  SELECT * 
56  FROM absenteeism2
57  WHERE  DateDiff(DAY, @leaveDate, Absent_Date) = -1
58    AND EMP_ID = @emp_ID)
59	
60EXISTS (
61  SELECT *
62  FROM Absenteeism2
63  WHERE DateDiff(DAY, @leaveDate, Absent_Date) < -1
64)
65	
66NOT EXISTS (
67  SELECT * 
68  FROM absenteeism 
69  WHERE  EMP_ID = @emp_ID 
70    AND Absent_Date <> @leaveDate)