-- Update the dates with the value for the day
UPDATE @TableDates
SET 
	qt = @qtDay + qt,
	ids = ids + ',' + CAST(@id AS NVARCHAR(15))
WHERE pac = @pac AND 
		dt IN ( SELECT DATEADD(DAY,N-1,@st)
				FROM dbo.Numbers
				WHERE N <= @diff AND dbo.CheckDayType(DATEADD(DAY,N-1,@st)) = 0)

CREATE FUNCTION [dbo].[CheckDayType]
	(@Data SMALLDATETIME)
RETURNS TINYINT
AS
BEGIN
	DECLARE 
			@Type TINYINT,
			@Holiday BIT,
			@WeekDay BIT

	SELECT 
		@Holiday = Feriado,
		@WeekDay = DiaSemana 
	FROM dbo.Calendar 
	WHERE Data = @Data 
	
	
	IF @Holiday = 0 AND @WeekDay = 1 
		SET @Type = 0 -- Normal WorkDay
	ELSE IF @Holiday = 0 AND @WeekDay = 0
		SET @Type = 1 -- Weekend
	ELSE IF @Holiday = 1 AND @WeekDay = 1
		SET @Type = 2 -- Holiday
	ELSE IF @Holiday = 1 AND @WeekDay = 0
		SET @Type = 3 -- Holiday in the WeekEnd
		
	RETURN @Type 
END

CREATE FUNCTION [dbo].[CheckDayType_ITVF]
	(@Data SMALLDATETIME)
RETURNS TABLE 
AS
RETURN
SELECT 
  CASE
    WHEN Feriado = 0 AND DiaSemana = 1
    THEN 0
    WHEN Feriado = 0 AND DiaSemana = 0
    THEN 1
    WHEN Feriado = 1 AND DiaSemana = 1
    THEN 2
    WHEN Feriado = 1 AND DiaSemana = 0
    THEN 3
  END AS DayType
  FROM dbo.Calendar 
	WHERE Data = @Data 
GO

DECLARE curD CURSOR FOR
SELECT pac,st,ed,qt,idpac	
FROM @TabelaPACS 
ORDER BY pac

OPEN curD
FETCH NEXT FROM curD INTO @PAC,@st,@ed,@Qt,@id
WHILE @@FETCH_STATUS = 0
BEGIN

	-- Retrieve workingdays
	SELECT @Diff = dbo.workingDays(@st,@ed,0,0)
	SET @qtDay  = @Qt/(CASE WHEN @qtDay = 0 THEN 1 ELSE @qtDay END)

	UPDATE @TabelaDates 
	SET 
		ids = ids + ',' + CAST(@id AS NVARCHAR(10)),
		qt = @qtDay + qt
	WHERE pac = @pac AND data IN (
								SELECT DATEADD(DAY,N-1,@st)
								FROM dbo.Numbers
								WHERE N <= @Diff AND dbo.VerificaTipoDia(DATEADD(DAY,N-1,@st)) = 0)
	
		
	FETCH NEXT FROM curD INTO @PAC,@st,@ed,@Qt,@id
END
CLOSE curD
DEALLOCATE curD

rootfixxxer (1/17/2013)

---

Never used ITVF before, so how do i replace my function by the ITVF?

UPDATE MyTable
SET 
	MyTable.qt = @qtDay + qt,
	MyTable.ids = ids + ',' + CAST(@id AS NVARCHAR(15))
FROM @TableDates MyTable
CROSS APPLY dbo.CheckDayType_ITVF(DATEADD(DAY,N-1,dt)) myfn	
WHERE pac = @pac
AND myfn = 0