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
;

GuyThiebaut wrote:

I restore the backup to a test system(so yes a duplicate database) then I test a series of .Net apps

GuyThiebaut wrote:

As a DBA you want to make sure that when the SHTF you can get the systems back to working within an agreed SLA

GuyThiebaut wrote:

I would strongly recommend an image backup every month with daily incremental image backups - disk space is cheap and rebuilding systems is expensive.

GuyThiebaut wrote:

Another tip - if you are not running this all on a virtual machine, ensure that the SQL Server install is on a different disk to the software accessing it(this will further ensure its integrity and speed up access).

Richard.Berry100 wrote:

the guy is a mate,

Richard.Berry100 wrote:

Not entirely sure what you mean here?

GuyThiebaut wrote:

I take it there there is one central instance of an SQL Server rather than an instance on each workstation.

If there is one central instance then try to get it off the OS drive(probably the C: drive) so that disk access is not competing between OS calls and SQL Server calls - does that make sense?