David F (11/5/2012)

---

I do appreciate the help...

But I 100% disagree that I "should" explicitly define the columns. I understand the performance benefit but I "should" do whatever I...

I'm glad that I could help.

Now, you should be careful when handling data. Noone will assure you that you won't find a value that's not supported by bigint.

Try to control...

To test what the others have said, you can use this code:

CREATE TABLE TestOne(

col1int,

col2varchar(20),

col3varchar(20),

col4datetime)

CREATE TABLE TestTwo(

col1int,

col2varchar(20),

col3varchar(20))

INSERT INTO TestOne VALUES

(1,'A', 'XYZ', '20121101'),

(2,'B', 'TWa', '20121102'),

(3,'C', 'ASD', '20121103'),

(4,'D', 'POI', '20121104')

INSERT INTO TestTwo

SELECT col1,

col3...

There are many problems with your query:

1. You didn't include start_time in your CTE definition. This should show an error.

2. You're comparing strings against ints. This is useless and it...

Why are you trying to convert a datetime into datetime? isn't that redundant?

It wasn't completely clear what you needed but the sample data and expected results helped a lot.

I guess there's an error with the second group on id 2, but this...

The query seems fine, but you need to know what is causing the problem.

Remember that if you're comparing strings, you have to be careful with leading spaces or zeros.

'123' is...

The error is occurring on your WHERE clause because is trying to convert BBL into int to compare against 1015180039. If BBL is a varchar, you should compare against varchar...

That sounds complicated, but is hard to give an advice without knowing the whole situation.

It would be a shot in the dark and might not be the most suitable advice.

What...

To get only one row for every manid, you were really close. You just needed one more aggregate.

If this doesn't solve your problem, I would need a better explanation on...

Similar to capnhector last option presented, here's another one that might have disadvantages but works for the example.

;with cte as

(

select 'Volvo' as CARS, 2001 as Year union all

select 'Volvo',...

You should take a look at Gail's article with suggestions to post performance issues.

http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

It might not affect performance, but you should beware of the dangers of using NOLOCK.

vicki.k.noble (11/2/2012)

---

The problem is 0 0 shows as 0 complete but 4 4 also shows as 0 complete. The latter is correct but if 0 0 could just return...

What's the difference between line 2 (Total Items: 3) and line 4 (Total Items: 4)? You say line 2 is incorrect and line 4 is correct.

If, as I suppose,...

Do you need to create an UDF?

Why can't you use DATEDIFF?