Usually the first 10 rows of the file are used to decide which type the column is. So if you always use the first row as a dummy with an...

I agree, the cte should be more efficient.

There should be checks added though for main.JoinKey IS NULL and offset.JoinKey IS NOT NULL?

I think the easiest way is to google for something along the lines of 'sql server 2000 error 2576'.

SELECT COL1,COL2... convert(varchar,YOUR_DATE_COLUMN, 3) leads to dd/mm/yy, while you mentioned dd-mm-yyyy that should be

SELECT COL1,COL2... convert(varchar,YOUR_DATE_COLUMN, 105)

Of course I meant my code isn't guaranteed bugfree...

And Celko, many people who can't write sql very well reuse code many times and probably he's not the creator of...

Indeed, please post table definition, sample data and desired output next time. However, I think I might have found something what you're looking for. I also changed some of the...

What it looks like to me: You delete 9 random rows from the query, this is my testing script:create TABLE #t

(nDex INT IDENTITY(1,1)

,value VARCHAR(9)

,keey UNIQUEIDENTIFIER)

create table #count

(id...

how does this look to you?CREATE TABLE #Calendar (CalenderDate date)

INSERT INTO #Calendar

VALUES

('2011-01-01'),('2011-01-02'),('2011-01-03'),('2011-01-04'),('2011-01-05'),('2011-01-06'),('2011-01-07'),('2011-01-08'),('2011-01-09'),('2011-01-10'),

('2011-01-11'),('2011-01-12'),('2011-01-13'),('2011-01-14'),('2011-01-15'),('2011-01-16'),('2011-01-17'),('2011-01-18'),('2011-01-19'),('2011-01-20'),

('2011-01-21'),('2011-01-22'),('2011-01-23'),('2011-01-24'),('2011-01-25'),('2011-01-26'),('2011-01-27'),('2011-01-28'),('2011-01-29'),('2011-01-30')

INSERT INTO @Temptable(EmpCode,Name,Dept,Designation,Shift,ShiftDate,

InTime,OutTime,Remark,Late1,EarlyDepature1,EarlyCome1,DelayGo1)

--ELBalance,CLBalance,ReliveJoin)

SELECT

E.EmpCode,

E.FName,

D.DeptName,

De.Descriptions AS Designation,

'Shift'= ISNULL(S.Descriptions,'Default'),

'Date' = C.CalenderDate,

'InTime'= ISNULL(RIGHT(CONVERT(VARCHAR,SP.InTime,100),7),'00:00'),

'OutTime'=ISNULL(RIGHT(CONVERT(VARCHAR,SP.OutTime,100),7),'00:00'),

'Remarks' = CASE

WHEN CONVERT(DATE,E.DOJ) > CONVERT(DATE,C.CalenderDate) THEN ''

WHEN...

Then I think you simply have to add

OR ClientRoom.ClientRoomNo IS NULL

for rooms that have no reservations at all. You can use any other column in ClientRoom for that matter...

First I wouldn't use the old join type, but proper inner joins.

Second, I think it's rather strange that in the query itself as well in the group by, the summed...

Shamelessly advertising I'd say.

Is it something like that in the procedure you're using a transaction, and in that transaction you call the procedure again (or another transaction directly)?

I would expect something in the like of this:

UPDATE D

SET ProfileId = P.Id

FROM Designation D

INNER JOIN Profiles P ON P.Code = D.Codeand I guess is that you need to...

First, you should consider changing the ntext field to nvarchar(max). Second, I would create a query (maybe cast the field to nvarchar) and put it in an SSIS job, or...

Millions of rows with hundreds of columns sure can give a problem without your indexes well chosen.

First thing is: always create a primary key with a clustered index. Second I...