Firewolf (8/29/2016)

---

J Livingston SQL (8/29/2016)

---

Firewolf (8/29/2016)

---

J Livingston SQL (8/29/2016)

did you try the code I originally posted?

I just did now - it works on the sample data, but it hadn't for the true data. I'll tweak it again and see where it went wrong.

Thanks.

can you clarify what Pyear is "Currentyr" in your results.

this code assumes 2016 as CurrentYr....which does not give your results, although I think you have your results columns the wrong way around??

WITH cte AS (

SELECT DISTINCT Sales_Rep, PYear, Number_of_Employees FROM mytable

)

SELECT Sales_Rep,

SUM(CASE WHEN Pyear = 2016 THEN Number_of_Employees END) AS CurrentYr,

SUM(CASE WHEN Pyear = 2015 THEN Number_of_Employees END) AS PriorYr

FROM cte

GROUP BY Sales_Rep

ORDER BY Sales_Rep

Bah, I don't think I've ever been this sloppy. :angry: smh.

You are correct, I had the column headers backwards in my table. THIS one is correct. Sigh.

+-----------+-----------------+-----------------+

| Sales Rep | PrevYear NumEmp | CurrYear NumEmp |

+-----------+-----------------+-----------------+

| Allen | 246 | NULL |

| Rob | 2585 | 2105 |

| Steve | 1850 | 1850 |

| Mark | 6935 | 7894 |

| Leah | 44215 | 46289 |

| Jenny | 200 | 852 |

| Jim | 1451 | 1398 |

| Pat | 9035 | 10452 |

+-----------+-----------------+-----------------+