Click here to Skip to main content
15,745,432 members
Home / Discussions / Database
   

Database

 
GeneralRe: SQL Server null date 01/01/1900 possible issue - a question Pin
Richard Deeming1-May-13 8:43
mveRichard Deeming1-May-13 8:43 
GeneralRe: SQL Server null date 01/01/1900 possible issue - a question Pin
GuyThiebaut1-May-13 9:19
professionalGuyThiebaut1-May-13 9:19 
GeneralRe: SQL Server null date 01/01/1900 possible issue - a question Pin
jschell2-May-13 7:59
jschell2-May-13 7:59 
Questionhow to union some table in a while loop to a table Pin
mhd.sbt1-May-13 3:49
mhd.sbt1-May-13 3:49 
AnswerRe: how to union some table in a while loop to a table Pin
Richard Deeming1-May-13 3:57
mveRichard Deeming1-May-13 3:57 
GeneralRe: how to union some table in a while loop to a table Pin
mhd.sbt1-May-13 4:10
mhd.sbt1-May-13 4:10 
GeneralRe: how to union some table in a while loop to a table Pin
GuyThiebaut1-May-13 4:31
professionalGuyThiebaut1-May-13 4:31 
GeneralRe: how to union some table in a while loop to a table Pin
Richard Deeming1-May-13 4:33
mveRichard Deeming1-May-13 4:33 
OK, the first thing you need is a split function:
SQL
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


Then you can build a single query:
SQL
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
;




"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer


GeneralRe: how to union some table in a while loop to a table Pin
mhd.sbt1-May-13 5:04
mhd.sbt1-May-13 5:04 
AnswerRe: how to union some table in a while loop to a table Pin
Corporal Agarn1-May-13 4:20
professionalCorporal Agarn1-May-13 4:20 
GeneralRe: how to union some table in a while loop to a table Pin
mhd.sbt1-May-13 4:26
mhd.sbt1-May-13 4:26 
GeneralRe: how to union some table in a while loop to a table Pin
Eddy Vluggen1-May-13 4:59
professionalEddy Vluggen1-May-13 4:59 
QuestionSSE 2008 R2 Backup Strategy Pin
Richard.Berry10030-Apr-13 19:47
Richard.Berry10030-Apr-13 19:47 
AnswerRe: SSE 2008 R2 Backup Strategy Pin
GuyThiebaut30-Apr-13 21:56
professionalGuyThiebaut30-Apr-13 21:56 
GeneralRe: SSE 2008 R2 Backup Strategy Pin
Richard.Berry10030-Apr-13 22:16
Richard.Berry10030-Apr-13 22:16 
GeneralRe: SSE 2008 R2 Backup Strategy Pin
GuyThiebaut30-Apr-13 22:43
professionalGuyThiebaut30-Apr-13 22:43 
GeneralRe: SSE 2008 R2 Backup Strategy Pin
Richard.Berry10030-Apr-13 23:18
Richard.Berry10030-Apr-13 23:18 
GeneralRe: SSE 2008 R2 Backup Strategy Pin
GuyThiebaut30-Apr-13 23:34
professionalGuyThiebaut30-Apr-13 23:34 
GeneralRe: SSE 2008 R2 Backup Strategy Pin
Richard.Berry1001-May-13 0:10
Richard.Berry1001-May-13 0:10 
QuestionGeneric column mapping Pin
Ravikiran72p30-Apr-13 1:57
Ravikiran72p30-Apr-13 1:57 
AnswerRe: Generic column mapping Pin
Eddy Vluggen30-Apr-13 9:49
professionalEddy Vluggen30-Apr-13 9:49 
AnswerRe: Generic column mapping Pin
Mycroft Holmes30-Apr-13 13:56
professionalMycroft Holmes30-Apr-13 13:56 
AnswerRe: Generic column mapping Pin
GuyThiebaut30-Apr-13 21:49
professionalGuyThiebaut30-Apr-13 21:49 
AnswerRe: Generic column mapping Pin
jschell1-May-13 8:34
jschell1-May-13 8:34 
QuestionMariaDB on Linux Pin
mbadi27-Apr-13 10:57
mbadi27-Apr-13 10:57 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.