You still haven't provided expected results.

Drew

Although, if you are eventually going to "pivot" the data, it's probably better to use the ROW_NUMBER and skip the LAG/LEAD.

J Livingston SQL (5/15/2016)

---

maybe....use following and then do a crosstab

WITH cteprices as (

SELECT itmId,

price,

ROW_NUMBER() OVER(PARTITION BY...

leon_clf (5/16/2016)

---

drew.allen (5/16/2016)

---

You haven't given your desired output, but it looks like you can get what you want by simply using the nodes() function on the subtrees received from the...

This is usually done with a CTE with ROW_NUMBER(). I'm showing the equivalent derived table version, since your original query used derived tables rather than CTEs.

I've also assigned your...

sqlnaive (5/16/2016)

---

I've one table with one specific integer column containing date as YYYYMMDD format and apart from date it also may contain value 99999999. I'm using this table in a...

You haven't given your desired output, but it looks like you can get what you want by simply using the nodes() function on the subtrees received from the first nodes()...

For performance problems, please post the execution plan (actual if possible, estimated if not), and the stored proc definition. Given that your error handling consists of a single simple...

copling (5/13/2016)

---

Actually, that's not going to work because if the TotalSalesAmt contains a NULL it's not going to be counted; and I need it to be counted. In the DENSE_RANK...

ChrisM@Work (5/13/2016)

---

Have you tried this?

;WITH

CTE1 AS (

SELECT [Emp_no.], Org_Code, Org_Num FROM #Employee

EXCEPT

SELECT [Emp_no.], Org_Code, Org_Num FROM #Class

), CTE2 AS (

SELECT [Emp_no.], Org_Code, Org_Num FROM #Class

EXCEPT

SELECT [Emp_no.],...

You can't directly convert an INT to a DATE. You could either convert an INT to DATETIME and then to DATE or you could use DATEADD(DAY, <your int field>,...

b.grove (5/12/2016)

---

Hi, the table that holds the document information has a RevisionNO column.

The range of revisions are from 1 to 27. Some people have 1. Some have 5....

Eric M Russell (5/12/2016)

---

There are certain keywords like "testosterone", "distressed furniture", and "butt cream" that seem to be a recurring theme for the spammers, but are highly unlikely to naturally...

TheSQLGuru (5/11/2016)

---

Drew.Allen: if you want to better test server performance on a given query that does lots of rows SELECT, just put the column(s) into variable(s). Voila - no more...

Alan.B (5/11/2016)

---

drew.allen (5/11/2016)

---

Actually, an inline table-valued function based on a table value constructor (like in Luis' script above) is blazing fast. Much faster than any physical table, because it...