Here is how I would approach this.

WITH CTE AS (

SELECT DateValue, [Value], ROW_NUMBER() OVER( PARTITION BY DateValue, [Value] ORDER BY (SELECT 1) ) AS rn

FROM #tableOne

EXCEPT

SELECT DateValue, [Value], ROW_NUMBER() OVER(...

I agree with Lynn here. If you can't represent your problem with 50 or fewer records, then your problem is probably too complex to solve in a forum post.

Also,...

SSMS is a UI built to work with T-SQL. It is not part of T-SQL. Text mode and grid mode are functions of the UI itself, not native...

CHARINDEX() has an option parameter (start_location) that can be used to skip the first occurence of the specified string. If you want an example, you'll have to provide more...

Please don't hijack old threads. If you have a new question, start a new thread.

Drew

polkadot (4/20/2012)

---

actually, I thought he was taking the first date of one status (top Active) and the first date of the second status (because of reverse ordering) when I first...

The problem with Dwain's code...

polkadot (4/20/2012)

---

Drew,

please explain how 'Days' is introduced. Since you and Dwain are both using DATEDIFF to extract

day portion of the datetime, I would have expected your CASE statements...

There is a third option. Update the rows in Table2 so that the constraint will still be valid after the delete. You can either update that column to...

Check out Jeff Moden's article Cross Tabs and Pivots, Part 1 – Converting Rows to Columns[/url]

Drew

polkadot (4/20/2012)

---

Drew, I appreciate your help. Your query does almost everythinig but the numbers appear to represent the number of days a given ticket is in any status and...

abs1337 (4/20/2012)

---

I tried playing around with something like this:

and I get this error "The select list for the INSERT statement contains fewer items than the insert list. The number of...

Sorry about the error in my code. I forgot to copy the entire code.

Here is an update with changes to provide an average rather than a count. The...

You're looking for the HAVING clause.

SELECT SerialNum, MAX(UsedDate) AS LastUsedDate

FROM YourTable

GROUP BY SerialNum

HAVING MAX(UsedDate) < DATEADD(YEAR, -1, GETDATE())

Drew

I only quoted the relevant section of the code.

By definition, the partitioning fields are...