Database

GuyThiebaut wrote:

SQL Server will return a null date as 01/01/1900 if one castes a null string to a date.

jschell wrote:

the data is actually an empty string

GuyThiebaut wrote:

as I can see someone in the future doing an isnull comparison on an
empty string and getting a match against these dates.

mehdi.sabet wrote:

my where cluase change in each repeat.

ALTER PROCEDURE [dbo].[lfd__GetFullPersonnelDetails]
(
@PersonnelList nvarchar(MAX),
@WorkPeriodID int,
@WorkPeriodYear int
)  AS
Declare @startDate SMALLDATETIME
Declare @endDate SMALLDATETIME
Declare @PersonnelBaseID INT

WHILE(LEN(@PersonnelList) > 0)
BEGIN
	SET @PersonnelBaseID = SUBSTRING(@PersonnelList, 0, PATINDEX('%,%',@PersonnelList))
	IF(@PersonnelBaseID != 0)
		SET @PersonnelList = SUBSTRING(@PersonnelList, LEN(CAST (@PersonnelBaseID AS NVARCHAR) + ',') + 1, LEN(@PersonnelList))
	ELSE
		BEGIN
			SET @PersonnelBaseID=CAST(@PersonnelList AS INT)
			SET @PersonnelList = SUBSTRING(@PersonnelList, LEN(@PersonnelList + ',') + 1, LEN(@PersonnelList))
		END
	
	 set @startDate=(select top 1 [date] as startdate 
		from 
			lfd_DailyStatistics
		 where 
			PersonnelBaseID = @PersonnelBaseID and [Year] = @WorkPeriodYear and  WorkingPeriodID = @WorkPeriodID 
		order by date asc)

	 set @endDate=(select top 1 [date] as startdate 
		from 
			lfd_DailyStatistics
		 where 
			PersonnelBaseID = @PersonnelBaseID and [Year] = @WorkPeriodYear and  WorkingPeriodID = @WorkPeriodID 
		order by date desc)

	SELECT 
		dailyAbsences.*,
		CAST(CASE WHEN calculationQueue.[StartDate] IS NULL THEN 0 ELSE 1 END AS bit) AS [NeedsRecalculation] 
	FROM 
		lfd_dailyAbsence dailyAbsences LEFT JOIN
		lfd_CalculationQueue calculationQueue ON dailyAbsences.[PersonnelBaseID] = calculationQueue.[PersonnelBaseID] AND calculationQueue.[StartDate] <= @endDate 
	WHERE 
		dailyAbsences.[PersonnelBaseID] = @PersonnelBaseID AND
		dailyAbsences.[StartDate] >= @startDate AND 
		dailyAbsences.[EndDate] <= DATEADD(day, 1, @endDate)
	ORDER BY
		dailyAbsences.[StartDate]
	
END

CREATE FUNCTION dbo.fn_Split
(
    @Value        varchar(max),
    @Delimiter    varchar(20) = ','
)
Returns TABLE
As Return
(
    WITH cte (Idx, StartIndex, EndIndex) As
    (
        SELECT 
            1, 
            1, 
            CharIndex(@Delimiter, @Value)
        
        UNION ALL
        
        SELECT 
            1 + Idx, 
            CAST(1 + EndIndex As int), 
            CharIndex(@Delimiter, @Value, 1 + EndIndex)
        FROM 
            cte
        WHERE
            EndIndex > 0
    )
    SELECT
        Idx,
        SubString(@Value, StartIndex, CASE
            WHEN EndIndex > 0 THEN EndIndex - StartIndex
            ELSE LEN(@Value)
        END) As Value
    FROM
        cte
);
GO

WITH ctePersonnelList (PersonnelBaseID) As
(
    SELECT
        CASE
            WHEN Value Like '%[^0-9]%' THEN Null
            ELSE CAST(Value As int)
        END
    FROM
        dbo.fn_Split(@PersonnelList, ',')
    WHERE
        Value Not Like '%[^0-9]%'
),
cteDates (PersonnelBaseID, StartDate, EndDate) As
(
    SELECT
        L.PersonnelBaseID,
        Min([date]),
        Max([date])
    FROM
        ctePersonnelList As L
        INNER JOIN lfd_DailyStatistics As S
        ON L.PersonnelBaseID = S.PersonnelBaseID
    WHERE
        [Year] = @WorkPeriodYear
    And
        WorkingPeriodID = @WorkPeriodID
    GROUP BY
        L.PersonnelBaseID
)
SELECT
    A.*,
    CAST(CASE WHEN Q.StartDate IS NULL THEN 0 ELSE 1 END AS bit) As NeedsRecalculation
FROM
    cteDates As D
    INNER JOIN lfd_dailyAbsence As A
    ON A.PersonnelBaseID = D.PersonnelBaseID
    And A.StartDate >= D.StartDate
    And A.EndDate <= DateAdd(day, 1, D.EndDate)
    LEFT JOIN lfd_CalculationQueue As Q
    ON A.PersonnelBaseID = Q.PersonnelBaseID
    And Q.StartDate <= D.EndDate
ORDER BY
    A.PersonnelBaseID,
    A.StartDate
;