Step one: Define a column to be used as a row/column identifer (either column Parameter or a new column using ROW_NUMBER())

Step two: UNPIVOT the data to get a table with...

I would precalculate the data per user and day using a CTE or subquery.

; WITH cte AS

(

SELECT ServiceID ,

COUNT(DISTINCT DATE) AS cnt,

SUM(numHours) AS subtotal

FROM @ServiceDates

GROUP BY ServiceID, UserName

)

SELECT L.ClientID, S.LocationID,...

Paul White NZ (7/2/2010)

---

Interesting. Even though I am credited on that post with a...

Would the following code give you the expected result?

SELECT

CASE WHEN (Grouping(Store) = 1) THEN 'Report Total' ELSE Store END AS report_total,

Product,

Store,

CASE WHEN (Grouping(Store) = 1) THEN...

Here's an alternative to Waynes second query.

Depending on the data structure of the xml file it might perform better.

-- to get all KeyTypes for the user, different approach:

SELECT...

Assuming you're using SQL2000 then the following approach might help:

SELECT

User_ID as Field1,

'Monday' as Weekday,

Prg_Hrs_Monday=Prj_Hrs

FROM YourTable

UNION ALL

SELECT

User_ID as Field1,

'Tuesday' as Weekday,

Prg_Hrs_Tuesday=Prj_Hrs

FROM...

Any chance to be a little more specific?

E.g. by providing table def and sample data of the table involved together with a few scenarios to cover?

This would actually help us...

Triple post.

So far, most replies here.

GilaMonster (7/1/2010)

---

I should bloody hope that a test with 1000 rows returns in under a second. It requires a really bad query to process 1000 rows in more than 1...

Some posters sure have a strange perspective regarding performance...

chris-736523 (7/1/2010)

---

...

lmu92,

I tested both versions in question above. Both return more than 1000 values in less than 1 second. If you want to split hairs, can you...

chris-736523 (6/30/2010)

---

I do this frequently.

I'll declare the input parameter as

@ParameterName VARCHAR(4000)

Then, whatever is calling the procedure needs to pass a comma delimited string of values. Example "1,2,3,4,5,6".

In...

What exactly is your question? :unsure:

Based on your sample data, wouldn't it simply be

SELECT cust_num, SUM(dayslate) AS res

FROM mytable

WHERE [TYPE]='P'

GROUP BY cust_num

If you're using SQL Server 2008 you could pass the values as a table variable parameter.

An example is given here.

Regarding "ALL records": it depends how you pass the data...