This gives the expected results with the data supplied, but you haven't said what the expected behavior should be when 2012 and 2014 have data, but 2013 and 2015 don't....

Shadab Shah (6/11/2015)

---

Alvin Ramard (6/10/2015)

---

If #Temp2 had a RowNumber column, with values that reset to 1 for each grouping, then in your update statement, you can add the join on...

karen.blake (6/10/2015)

---

When I added with rollup to the end of the group by piece, it listed some columns as having null values, some had a month in the month column,...

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 >...

KGJ-Dev (6/9/2015)

---

Hi Drew/Sumngson,

thanks for your response. once condition is missed on your query. If the offer is not available at company level then we need to check on client level...

There are three main issues here:

1) You have no join clause in your query, so you are essentially doing a CROSS JOIN.

2) Neither table has a primary key, so you...

I added some functionality that you didn't ask for. I assumed that your table may have multiple offers for each company including future and multiple expired offers. I set it...

You've probably set...

I greatly simplified your query by doing the aggregates as soon as I had all the necessary information. It turns out that the tblProjectRevenue contained enough information to do...

I don't see the sale date field and I'm assuming that you want to show the sale information in the fiscal period where the sale occurred. I'm only including...

In some languages, CASE statements can return a BOOLEAN, but not in SQL. You're probably trying to return a BOOLEAN value from the CASE statement. Try the following instead:

/*

...

This solution works for the supplied data, but I don't guarantee that it will work for all cases.

I've assumed that you will create a table containing relationship words that will...

You can actually do this without the self join. Since you haven't provided sample data, I can't readily do a performance comparison.

;WITH SalesOrderHeader As

(

SELECT H.SALESPERSONID,

MONTH(H.SHIPDATE) AS SALES_MONTH,

YEAR(H.SHIPDATE)...

dbodell (6/5/2015)

---

My real question is whether using EXCEPT adds significant overhead to the process. Everything we do in SQL is a balancing act between performance/efficiency and getting the job done....

Generally when you are doing these kinds...