Here is code that gives the correct result for your sample data. From there, you should be able to figure out a way to optimize it.

;WITH PlannedWork AS (

SELECT...

I'm going to throw a wrench in the works. All of your data has had roughly equivalent start and end times for the planned and actual data. Suppose...

TDSapp (10/6/2011)

---

CELKO (10/5/2011)

---

What I would really like to do is return a table that has a customer column, and then one column for each status. The status column would...

I think it IS that simple. Your expected results...

XML is case sensitive, so that should be

select...

kramaswamy (10/5/2011)

---

Row 1: From 8:00AM to 9:55 AM. Was supposed to work from 8:00AM to 9:45 AM -- 10 minutes

Row 2: From 10:11 AM to 11:28 AM. Was supposed to...

You don't need to join on Table_1 for this particular query, but that could just be an artifact of the simplified data.

In any case, you might want to group your...

Actually, if you want to return anything other than the amount--which you probably do, since you asked for details--you should look at the Row_Number() function.

Drew

I did look at using an APPLY rather than a subquery, but the execution plans for both were essentially the same. The only difference I found was that the...

The standard way is something like the following:

SELECT DISTINCT [State]

, Stuff(

(SELECT ', ' + City

FROM Table1 AS t2

WHERE t1.[State] = t2.[State]

ORDER BY City

FOR XML PATH('')

)

, 1 -- start position

,...

For performance issues, it really helps to have the actual and/or estimated execution plans.

One thing you can try on your own is turning on XML statistics before the rest of...

Sean Lange (10/4/2011)

---

Yes like mentioned you cannot perform multiple aggregations on a single column. You can however split this into two pieces in a subquery and perform your calculation outside....

Using the distributive properties of multiplication over addition, your formula

sum(cast(l.labor as decimal(18,2)) * count(distinct d.sn) / 1000 * 23.37) as appLabor

is equivalent to the following formula

sum(cast(l.labor as decimal(18,2))) * count(distinct...

Kenneth Fisher-475792 (10/3/2011)

---

Actually no, in this case a reporting tool won't work. We get the occasional spreadsheet sent to us by upper management with the task of "filling in...

The problem with this is that it forces you to violate the first normal form...