IF (@user_leave_type = 3)
BEGIN
IF EXISTS( SELECT 1 FROM [dbo].[User_Leave] WHERE CONVERT(DATETIME,[start_date],101) BETWEEN CONVERT(DATETIME,@start_date,101) AND CONVERT(DATETIME,@end_date,101) AND
CONVERT(DATETIME,[end_date],101) BETWEEN CONVERT(DATETIME,@start_date,101) AND CONVERT(DATETIME,@end_date,101) AND [user_id] = @user_id)
BEGIN
select @leave_status=leave_status from user_leave where user_id=@user_id and CONVERT(DATETIME,[start_date],101)= CONVERT(DATETIME,@start_date,101) AND CONVERT(DATETIME,[end_date],101) = CONVERT(DATETIME,@end_date,101)
If(@leave_status=1)
begin
SET @active_rec_in = 0
print '@active_rec_in 3'
INSERT INTO User_Leave
(
[ref_cat_id],
[user_id],
[leave_hour],
[start_date],
[end_date],
[leave_desc],
[active_rec_in],
[user_leave_type],
[shift_during_leave],
[shift_after_leave],
[worked_days_in_shift],
[task_delegated_to]
)
SELECT
@ref_cat_id,
@user_id,
@leave_hour,
@start_date,
@end_date,
@leave_desc,
@active_rec_in,
@user_leave_type,
@shift_during_leave,
@shift_after_leave,
@worked_days_in_shift,
@task_delegated_to
end
else
SELECT @message=[ref_name] FROM [dbo].[Reference_Categories] WITH (NOLOCK) WHERE [ref_type] = 'MESSAGE' AND [ref_cat_code]=1024
end
ELSE
IF NOT EXISTS( SELECT 1 FROM [dbo].[User_Leave] WHERE CONVERT(DATETIME,[start_date],101) BETWEEN CONVERT(DATETIME,@start_date,101) AND CONVERT(DATETIME,@end_date,101) AND
CONVERT(DATETIME,[end_date],101) BETWEEN CONVERT(DATETIME,@start_date,101) AND CONVERT(DATETIME,@end_date,101) AND [user_id] = @user_id )
BEGIN
SET @active_rec_in = 0
print '@active_rec_in 3'
INSERT INTO User_Leave
(
[ref_cat_id],
[user_id],
[leave_hour],
[start_date],
[end_date],
[leave_desc],
[active_rec_in],
[user_leave_type],
[shift_during_leave],
[shift_after_leave],
[worked_days_in_shift],
[task_delegated_to]
)
SELECT
@ref_cat_id,
@user_id,
@leave_hour,
@start_date,
@end_date,
@leave_desc,
@active_rec_in,
@user_leave_type,
@shift_during_leave,
@shift_after_leave,
@worked_days_in_shift,
@task_delegated_to
END
ELSE
IF NOT EXISTS( SELECT 1 FROM [dbo].[User_Leave] WHERE CONVERT(DATETIME,[start_date],101) = CONVERT(DATETIME,@start_date,101) AND [user_id] = @user_id OR CONVERT(DATETIME,[end_date],101) = CONVERT(DATETIME,@end_date,101))
BEGIN
SET @active_rec_in = 0
INSERT INTO User_Leave
(
[ref_cat_id],
[user_id],
[leave_hour],
[start_date],
[end_date],
[leave_desc],
[active_rec_in],
[user_leave_type],
[shift_during_leave],
[shift_after_leave],
[worked_days_in_shift],
[task_delegated_to]
)
SELECT
@ref_cat_id,
@user_id,
@leave_hour,
@start_date,
@end_date,
@leave_desc,
@active_rec_in,
@user_leave_type,
@shift_during_leave,
@shift_after_leave,
@worked_days_in_shift,
@task_delegated_to
END
END