Better solution to the Tricky SQL problem
time to read 4 min | 614 words

Moran has pointed me to the CROSS APPLY syntax in T-SQL 2005, which does allows joining against a table valued function. Using this, the query goes down to this:

SELECT

        EmpName,

        CONVERT(NVARCHAR, CurrentDate,103) Date,

        HasWorked = CASE SUBSTRING(Roster,

                dbo.IndexInRoster(StartDate,CurrentDate,LEN(Roster)), 1)

                        WHEN '_' THEN 0

                        WHEN '*' THEN 1

                        ELSE NULL

            END

FROM Schedules CROSS APPLY DateRange(StartDate,EndDate)

This is much nicer way to deal with it. Considerring that I am using similar techniques all over the place, this is a really good thing to know.

Tweet Share Share 0 comments
Tags: