Why don't you rewrite algorithm to use recursive cte instead?

This is all documented in Books Online, of course.

DECLARE@tblTopics TABLE

(

TopicID INT,

ReplyID INT,

ParentID INT

)

INSERT@tblTopics

SELECT700, NULL, NULL UNION ALL

SELECT701, 700, 700 UNION ALL

SELECT900, NULL, NULL UNION ALL

SELECT702, 701, ...

Also asked and answered here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=116495

Here http://msdn.microsoft.com/en-us/library/ms191175(SQL.90).aspx

What you also could do, is to make a two-step import routine.

First try to import file using six columns. If that fails, execute the import again using five columns.

DECLARE...

use a format file.

there are examples in books online how to deal with this situation.

SELECT *

FROM #MyTest

ORDER BY LEN(TestData), TestData

The basic idea is to map future precalculated timeslots against an appointment table and get first (oldest) timeslot that doesn't have a related appointment.

ak (12/18/2008)

---

Could you please help me understand the query you have written, I am tring to work on 'finding the next available time slot' in a scheduling application and can...

If you are going to have a maximum of 62 blocks, you now can switch back to your bit-hack 😀

Some times, you can force an index seek without having to write any query hints.

In some cases you can add

AND Col1 > 0

if Col1 has positive values only.

It depends on what you are indexing, of course.

A primary key can have 100% since all records are unique.

An IDENTITY can also have 100% since the values are sequential.

An index...

Try this 🙂

EDIT: Damn editor to remove code parts.... :angry:

See attached file for sample data and working code.

CalendarFromTimeCalendarToTime010203040506

00:00:0001:00:00000001

01:00:0002:00:00000001

02:00:0003:00:00000001

03:00:0004:00:00000001

04:00:0005:00:00000001

05:00:0006:00:00000001

06:00:0007:00:00000001

07:00:0008:00:00000001

08:00:0009:00:00101011

09:00:0010:00:00100011

10:00:0011:00:00200011

11:00:0012:00:00110011

12:00:0013:00:00200011

13:00:0014:00:00200011

14:00:0015:00:00100011

15:00:0016:00:00100011

16:00:0017:00:00110011

17:00:0018:00:00010011

18:00:0019:00:00000011

19:00:0020:00:00000011

20:00:0021:00:00000011

21:00:0022:00:00000011

22:00:0023:00:00000011

23:00:0024:00:00000011

3) Change

SUM(CASE WHEN DATEPART(DAY, s.FromTime) = 17 THEN 1 ELSE 0 END) AS [17],

to

COUNT(DISTINCT CASE WHEN DATEPART(DAY, s.FromTime) = 17 THEN s.empID ELSE NULL END) AS [17],

As you can see, it is very easy to change the @SlotSize value to either "dive into" or "pull out of" the calendar.

If you want 15 minute slots, just set...

EDITS:

Second DATEPART(DAY, s.FromTime) = 4 should be DATEPART(DAY, s.FromTime) = 14

AND Number <= 1440 / @SlotSize AND Number * @SlotSize < 1440 can be shortened down to AND Number *...