CREATE TABLE #NewsEntities (NewsEntity INT NOT NULL identity(1,1), NewsId INT, EntityId INT)

INSERT INTO #NewsEntities (NewsId, EntityId)

VALUES

(1,1),(1,2),(1,3),

(2,1),(2,5),(2,3),

(3,5),(3,2),(3,3)

SELECT n.NewsId

FROM #NewsEntities n

WHERE EntityId = 1

AND NOT EXISTS (

SELECT 1

FROM #NewsEntities...

drew.allen (6/10/2015)

---

ChrisM@Work (6/10/2015)

---

Your syntax is a little out:

;WITH CTE AS(

SELECT ROW_NUMBER() OVER(PARTITION BY Id ORDER BY ID ASC) AS RowNum ,*

FROM #TEMP

)

UPDATE #TEMP2 CTE

SET SearchedBy =CASEWHEN RowNum=1 THEN CREATEDBY

WHEN RowNum...

jay-125866 (6/10/2015)

---

Thank you, Chris. Your solution works about right.

The issue remaining is that the Product Pencil is also shown in the result. It's not supposed to be there since...

Your syntax is a little out:

;WITH CTE AS(

SELECT ROW_NUMBER() OVER(PARTITION BY Id ORDER BY ID ASC) AS RowNum ,*

FROM #TEMP

)

UPDATE #TEMP2 CTE

SET SearchedBy =CASEWHEN RowNum=1 THEN CREATEDBY

WHEN RowNum > 1...

Try this:

;WITH PaidProducts AS (

SELECT *,

rn1 = ROW_NUMBER() OVER (PARTITION BY productId ORDER BY logDate DESC),

rn2 = CASE ProductStatus WHEN 'paid' THEN ROW_NUMBER() OVER (PARTITION BY productId, ProductStatus ORDER...

jay-125866 (6/9/2015)

---

...select only the products that had been paid for, but later their status has been changed.

I need to report the original paid date, the most recent status, and most...

gissah (6/10/2015)

---

Can someone please look at this code and tell me what I am doing wrong. the middle 2 conditions are not calculating correctly. I am getting zero as the...

jeff.born (6/9/2015)

---

ChrisM@Work (6/9/2015)

---

@jeff, please can you amend your sample data set so that it contains each of the conditions you describe? Thanks. Then run this and have a look at...

Zohaib Anwar (6/10/2015)

---

ChrisM@Work (6/10/2015)

---

You should be expecting a final query something like this:

;

WITH PreAgg AS (

SELECT

p.id,

HourBucket = DATEDIFF(hour,ISNULL(p.DueDate,GETDATE()),CAST(GETDATE() AS DATE))/24,

paidAmount = ISNULL(SUM(CASE WHEN x.Paid = 1 THEN tdPaid.DebitAmount -...

You should be expecting a final query something like this:

;

WITH PreAgg AS (

SELECT

p.id,

HourBucket = DATEDIFF(hour,ISNULL(p.DueDate,GETDATE()),CAST(GETDATE() AS DATE))/24,

paidAmount = ISNULL(SUM(CASE WHEN x.Paid = 1 THEN tdPaid.DebitAmount - tdPaid.CreditAmount ELSE 0...

Zohaib Anwar (6/10/2015)

---

ChrisM@Work (6/10/2015)

---

Zohaib Anwar (6/10/2015)

---

I am not sure that case statements are problem may be you can see other area of query improvement also if we dont put no...

Zohaib Anwar (6/10/2015)

---

If you filter a column of an outer-joined table in the WHERE clause of a query, you turn the join into an INNER JOIN:

...

Zohaib Anwar (6/10/2015)

---

ChrisM@Work (6/10/2015)

---

Zohaib Anwar (6/10/2015)

---

I am not sure that case statements are problem may be you can see other area of query improvement also if we dont put no...

Zohaib Anwar (6/10/2015)

---

I am not sure that case statements are problem may be you can see other area of query improvement also if we dont put no lock then we...

Good lord Grant! You...