I'm not sure I understand what you're saying. Did you have a look at the link I posted? Your query wasn't even close to a running state before,...

Bitbucket is hinting at the second way to do this, that I disregarded (possibly prematurely) because it's not dynamic. You need to know what every possible combination of columns...

Yeah, sorry, I have a bad habit of editing previous posts that people might have already read with more info. I really need to stop that.

Your syntax is off in many different ways. Take a look at this thread, I just fixed a very similar query for someone else last week.

http://www.sqlservercentral.com/Forums/Topic785369-338-1.aspx

Some simple Dynamic SQL + COUNT DISTINCT would get you there... but that's pretty messy.

Greg Snidow (9/16/2009)

---

Well I can see one, which is that mine is limited to only one year at a time. Other than that, yours...

Similar to what Greg posted, here is another method. Both have advantages and disadvantages, but together they should give you a good idea of how to use a tally...

Did you see my edit? You actually had 2 problems.

This code works perfectly fine:

--// lets create the source data

CREATE TABLE tblSource

(

...

Does it? Or did you just forget the

WHERE oldv.oldvalue newv.newvalue

😉

[Edit:] Also, in your test code, you're not generating new unique identifiers for the last two...

This is a pretty simple query if you're familiar with Tally/number tables. Take a look at the article in my signature for a full explanation of what a Tally...

That's not enough information for us to go off of. Are there any triggers on the table? Locking Contention possibly? We need a bit more information about...

lmu92 (9/15/2009)

---

I'd guess that the problem is within the definition of your view rather than the STARTDATE column in the SELECT clause.

Reason: The values provided for STARTDATE exceed the tinyint...

Try this.

SELECT * FROM CONTACTCALLDETv1 WHERE StartDate = 'Other'

I examined...

Jonathan

---

I logged in today because Steve wrote that he was (finally) making screen names unique (which we had discussed when he switched board software), and I wanted to see what...