ALTER PROCEDURE [dbo].[sp_GetIndividualHolidayReport]
(
@Username varchar(50)
)
AS
DECLARE @StartingHolidays decimal(4,2)
DECLARE @StartingDate date
SELECT @StartingHolidays = (Select Top 1 Days FROM dbo.StartingHolidays WHERE Username = @Username)
SELECT @StartingDate = (SELECT [StartDate] FROM [Timesheets].[dbo].[Periods] WHERe IsCurrent = 1)
SELECT
TIMESHEET.DATE,
1 AS Days,
@StartingHolidays AS StartingHolidays,
ApprovedHolidays.TimeSheetID,
ApprovedHolidays.DateAproved,
ApprovedHolidays.ApprovedBy,
ApprovedHolidays.Notes,
ApprovedHolidays.UserName
FROM TIMESHEET Full Outer JOIN
ApprovedHolidays ON TIMESHEET.RefId = ApprovedHolidays.TimeSheetID
WHERE (TIMESHEET.IdVolNow = @Username) AND (TIMESHEET.ContractId = 136)
AND @StartingDate >= @StartingDate
Holiday.PNG
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
From novice to tech pro — start learning today.
Open in new window
Results...
HolidayStart HolidayEnd HolidayLength
2013-07-17 00:00:00.000 2013-07-19 00:00:00.000 3
2013-07-22 00:00:00.000 2013-07-26 00:00:00.000 5
2013-09-19 00:00:00.000 2013-09-20 00:00:00.000 2
2013-09-23 00:00:00.000 2013-09-23 00:00:00.000 1