I don't know which version of sqlserver you have. Try:

;with rn

as

(

select name, 2 grp, DENSE_RANK() OVER(ORDER BY name) rnk

...

Try this:

declare @test-2 table (Name varchar(30), userid char(50))

insert into @test-2

SELECT 'aaa', 'sjohn' UNION ALL

SELECT 'bbb', 'sjohn' UNION ALL

SELECT 'bbb', 'pkevin' UNION ALL

SELECT 'ccc', 'lsmith' UNION ALL

SELECT 'ccc', 'enobili' UNION ALL

SELECT...

Grant Fritchey (8/4/2010)

---

One of these must be returning zero:

SUM(TotalMaterialValue) / SUM(TotalQuantityMade) AS AverageMaterialCost,

SUM(TotalLabourValue) / SUM(TotalQuantityMade) AS AverageLabourCost,

SUM(TotalOverheadValue) / SUM(TotalQuantityMade) AS AverageOverheadCost,

SUM(TotalTotalValue) / SUM(TotalQuantityMade) AS AverageTotalCost,

SUM(TotalTimeBookedMinutes) / SUM(TotalQuantityMade) AS AverageTimeInMinutes,

SUM(TotalTimeBookedHours) /...

This is duplicate post to one posted on T-SQL(SS2K8).

The behavior of your select statements using "not in" is well expected due to "undeterministic" nature of null value comparison.

That is why is not good idea to use it...

CELKO (8/3/2010)

---

There is no such thing as a CASE statement in SQL. We have a CASE expression . Expressions return scalar values.

...

I hope you know better than to...

Try:

declare @ReqDate datetime

set @ReqDate = '20100806' --GETDATE()

select LF-(7*wn)-(6) BeginDate, LF-(7*wn) EndDate, LF-(7*wn) PeriodEndingDate

from

(SELECT dateadd(dd, (DATEDIFF(dd,'1900-01-01',@ReqDate - 4) % 7 ) * -1, @ReqDate )...

Are you using SSIS? It can help you to filter the problem rows out.

No, actually BOL doesn't tell anything about it in the "SET NOCOUNT" article. However you can find the list of SET statements of the session scope:

http://msdn.microsoft.com/en-us/library/ms190356.aspx

No you don't need to.

Test this:

declare @sql nvarchar(1000)

set @sql = 'select count(*) from sys.objects'

exec sp_executesql @sql

and then this:

set nocount on

declare @sql nvarchar(1000)

set @sql = 'select count(*) from sys.objects'

exec sp_executesql @sql

Scope...

UnionAll (8/3/2010)

---

Greetings,

I am looking for a way to build a calendar table on the fly for the previous 3 weeks.

This calendar table will be like a table variable and this...

That whould work for maximum of 100 days period.

The maximum recursion of CTE...

You could extend it by specifying the MAXRECURSION hint, however you would still have a limit of approx...

Yep, OP's wording was clear enough for me... 😀

After looking closer into expected results I believe I do understand what he wants:

declare @beginDate datetime, @endDate datetime, @Org int

set...

with corrected setup...

-- setup

/*

CREATE TABLE #timekeep

(

ID VARCHAR(10),

Org VARCHAR(4),

)

CREATE TABLE #timecard

(

Date DATETIME,

ID VARCHAR(10),

Hours DECIMAL(10,2)

)

INSERT INTO #timekeep

(ID,Org)

SELECT '040677', '100' UNION ALL

SELECT '056789', '100' UNION ALL

SELECT '006777', '200'

INSERT INTO #timecard

(Date,ID,Hours)

SELECT...

If you still loking for solution, posting table DDL and insert script for some test data would help...