The major issue I can see so far is using varchar(max).

If possible for your solution, change it to varchar(8000) or less.

It seems like the dowside of varchar(max) (being too long...

Please post table def and sample data as described in the first link in my signature.

lmu92 (1/4/2010)

---

select * from wce_history where recordedtime >= dateadd(mm,-2,getdate())

dateadd is used to calculate date offsets based on a given date. Getdate() is current date, current time. Please see BOL (Books...

Well, since we just have learned that you're looking for a dynamic solution, here it is:

Step 1: running the code from before excluding the PIVOT part, but storing the result...

You're very welcome!

Would you mind sharing the results for both versions when applied to your "real data" for both solutions? (including total number of rows in table and indexes on...

YES, there is: Try a different solution!

I have to ask again: What is your reason for refusing to...

Basically, to explain the concept of an index the easiest way I've seen so far is to compare an indexed table with a phone book, where the table index equals...

Here's another version using a CTE to check that both billing types exist.

Assuming the required indexes are available, it is slightly faster than your solution (about 20% as per execution...

You're very welcome and thank you for the feedback! 🙂

I still recommend reading the article I referred to. It's helpful to know how it works. Just in case... 😉

Something like this?

SELECT ol.orderid,ol.billingType,ol.BillingFlag,count(ol.billingType) as 'Num order Lines'

FROM orderlines ol

WHERE OrderLineStatus<>'HIS'

AND (

(ol.billingtype = 'INI'

and ol.BillingFlag = 'F'

)

OR

(

ol.billingType = 'SCH'

and ol.BillingFlag = 'T'

)

)

GROUP BY ol.orderid,ol.billingType,ol.BillingFlag

There are a couple issues with the way your query is written:

1) the query will not benefit from any index since you applied a function to the column you query...

If you can add a column to your table to store the running total in AND create a CLUSTERED index on startup_Time, then you could use the "quirky update" method...

If you want/need to do it without SSRS you might want to read the two articles I referenced below regarding CrossTab and DynamicCrossTab.

Additionally to the test data it would be nice to how you'd provide those multiple reportids (e.g. comma separated, table, xml aso).

Basically, being a little more specific what you mean...

Justin James (1/4/2010)

---

mister.magoo (12/30/2009)

---

For the sake of it, a solution which copes with new "ColumnName" values by using dynamic sql (go ahead - pork chop me or whatever you do...