If you copy your own code in an earlier post...

Declare @C Table....

Some inserts....

Down to...Declare @r table ... eg skip the while loop and try this instead...

Select c1.ConsultantID, c1.Level, c2.Level, c1.Title,...

Additonally, the results of a group by are skewed because a person can maintain a level for months at a time, thus the count would be false because it would...

Unfortuantely, this does not produce the desired result....

Mark,

Good code, I knew this could be done with a CTE, although I am just starting to learn/use them. I took the logic from your code and transformed the...

I was just showing an example of how to union column data with data...

Dump your union statements into temp tables. Then you can union a select *, which will drastically cut down on the size of your sql string.

select 1,2,3 union all

select...

Change the variable to nvarchar(max) and that will hold up to 2gb of string.

Alorenzini,

I tried to avoid using a type of cursor for this query but I could not avoid it. Maybe some of the others guys will have some better methods....

Create procedure spAttendanceWDA

@WDA smallint,

@Sheet NVARCHAR(25)

AS

BEGIN TRY

BEGIN TRANSACTION

DECLARE @sql NVARCHAR(500)

SET @sql = N'

INSERT INTO...

You have to use dynamic SQL

DECLARE @Sheet NVARCHAR(25),

@sql NVARCHAR(500)

SET @Sheet = '[Sheet1$]'

SET @sql = N'

INSERT INTO OPENROWSET(''Microsoft.Jet.OLEDB.4.0'',

''Excel 8.0;Database=c:\test.xls;'',

''SELECT * FROM ' + @Sheet + ''')

SELECT * FROM MyTable

'

EXEC...

I believe some of your tags were stripped. If you are trying to post XML use brackets instead.

e.g.

[tag]...

SELECT DISTINCT b.ConsultantID,

b.AchieveLevel,

b.AchieveTitle,

b.PeriodEndDate

FROM #Temp a

INNER JOIN #Temp b ON a.ConsultantID = b.ConsultantID

AND a.PaidLevel =b.AchieveLevel AND a.PeriodEndDate < b.PeriodEndDate

ORDER BY b.PeriodEndDate

This returns the entire recordset not the...

You have something wrong, take a look my test data:

DECLARE @C TABLE(

ConsultantID CHAR(7),

Level CHAR(2),

Title CHAR(25),

PeriodEndDate DATETIME

)

INSERT INTO @C

SELECT '0002617', '02', 'Consultant', '2001-08-31' UNION ALL

SELECT '0002617', '04', 'Team Leader', '2002-03-31' UNION...

You need quotes around the path

bcp.exe Testdatabase.dbo.test in "C:/1.txt" -S Anand -U kumar -P kumar -t "," -r "" -c'

This query will yeild the same results

SELECT DISTINCT b.ConsultantID, b.Level, b.Title, b.PeriodEndDate

FROM @C a INNER JOIN @C b ON a.ConsultantID = b.ConsultantID

AND a.Level = b.Level AND a.PeriodEndDate < b.PeriodEndDate

ORDER BY...