I'm grumpy today, but something made me help you more. Usually, on a day like this, I would have asked you to post DDL and sample data in a consumable...

I would unpivot[/url] the source table, join it with the metadata table, use cross tabs[/url] to obtain the desired result and run away from that horrible design.

That destination table looks...

Have you tried nesting them?

SQLRNNR (4/1/2014)

---

Personally I...

You were really close to the solution.

WITH CTE1

as

(

SELECT r.GradeId,

r.DateofMfg,

ROW_NUMBER() OVER (order by r.DateofMfg)

- ROW_NUMBER() OVER (Partition by r.GradeId order by r.DateofMfg) as GroupID

FROM @t r...

I can't give more advice on this, unless you post more details. I don't know what kind of modifications you did or what are the change policies on your company.

the.roof (3/31/2014)

---

Luis Cazares (3/27/2014)

---

The subquery looks fine, you're filtering using JOINs. You could change them to IN or EXISTS and you might have a slight performance gain, but it...

Only if you cast them as...

You could use the syslanguages view along with the DelimitedSplit8K[/url] and Crosstabs[/url].

SELECT

MAX( CASE WHEN name = 'us_english' THEN s.Item END ) English

,MAX( CASE WHEN name = 'Deutsch' THEN s.Item...

Use a test environment.

You need to have the complete date value to order the months correctly or you could have another value to order by.

Examples:

DECLARE @Date date;

SET @Date = '20140405'

SELECT LEFT( DATENAME( MM,...

The code is still there. It's just located at the end instead of the beginning.

I'm glad that you're helping. I'm just trying to pay it forward. 😉

Eirikur Eiriksson (3/31/2014)

---

Luis Cazares (3/31/2014)

---

Here are 2 other options.

WITH Contacts AS(

SELECT INS.INSPNO, CNT.FIRSTNAME, CNT.LASTNAME, CNT.CAPACITY,

ROW_NUMBER() OVER( PARTITION BY INS.INSPNO ORDER BY CASE WHEN Capacity = 'Contractor' THEN -1 ELSE INS.INSPNO...

Here are 2 other options.

WITH Contacts AS(

SELECT INS.INSPNO, CNT.FIRSTNAME, CNT.LASTNAME, CNT.CAPACITY,

ROW_NUMBER() OVER( PARTITION BY INS.INSPNO ORDER BY CASE WHEN Capacity = 'Contractor' THEN -1 ELSE INS.INSPNO END DESC) rn

FROM @inspection...

It seems almost fine, but it's weird. You're not summing up the values. I added the SUM() and converted it to a iTVF. You need to test it as well...