August 29, 2016 at 7:32 am
Firewolf (8/29/2016)
J Livingston SQL (8/29/2016)
please can you reconfirm the results you are expecting from the latest sample dataI verified that this table matches the results from the SQL table code I gave you, it just needs to be filtered, e.g. remove duplicates on (Sales Rep, PYear and Number of Employees) and filter by PYear, then SUM the results per Sales Rep and you get the numbers I have below.
+-----------+-----------------+-----------------+
| Sales Rep | CurrYear NumEmp | PrevYear 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 |
+-----------+-----------------+-----------------+
did you try the code I originally posted?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
August 29, 2016 at 7:49 am
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.
August 29, 2016 at 7:57 am
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
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
August 29, 2016 at 8:08 am
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 |
+-----------+-----------------+-----------------+
August 29, 2016 at 8:26 am
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 |
+-----------+-----------------+-----------------+
ok...so those are results my code gives....well apart from "Jim"....not sure where he comes from cos he aint in your sample data :ermm:
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
August 29, 2016 at 12:04 pm
ok...so those are results my code gives....well apart from "Jim"....not sure where he comes from cos he aint in your sample data :ermm:
Yeah, let us pretend Jim doesn't exist.:-D
August 29, 2016 at 12:09 pm
Firewolf (8/29/2016)
ok...so those are results my code gives....well apart from "Jim"....not sure where he comes from cos he aint in your sample data :ermm:
Yeah, let us pretend Jim doesn't exist.:-D
so ... are you happy with the solution?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
August 29, 2016 at 12:23 pm
So that you don't have to worry about hard coding years in your script, you could do :
WITH cte AS (
SELECT DISTINCT Sales_Rep, PYear, Number_of_Employees FROM mytable
)
SELECTcoalesce(py.sales_rep, cy.sales_rep) as salesRep,
py.numberOfEmployees as priorYear,
cy.numberOfEmployees as currentYear
FROM
(
selectsales_rep, pyear ,
sum(Number_of_Employees) as [numberOfEmployees]
fromcte
wherepyear = (select max(pyear) from myTable)
groupby sales_Rep,pyear
) as cy
Full outer join
(
selectsales_rep, pyear,
sum(Number_of_Employees) as [numberOfEmployees]
fromcte
wherepyear = (select max(pyear) from myTable where pyear<>(select max(pyear) from myTable))
groupby sales_Rep,pyear
) as py
ON py.sales_rep = cy.sales_rep
However I like more the style of just having the years as your column headers , if you later wanted to look back many years. In this latter case, the solution would be better off as a pivot report (sales reps on row header and years as the column headers).
----------------------------------------------------
September 6, 2016 at 3:13 pm
Happy with the solution? Sure.
Did it work? Not in this scenario. We are scratching this plan for now and splitting it into two reports.
The data is just not playing nicely and for time's sake I'm just going to get the data into a report that will be exported to Excel and joined via vlookups.
I'll revisit this when I have time and take another stab at it with a clear mind (hopefully).
Thanks for the input/help.
September 6, 2016 at 3:24 pm
Thanks for the feedback. And I wont bog you down with questions, just that if you want to let us know the exact issues you encountered then we can certainly take a look at those. Hope things go well with your alternate solution.
----------------------------------------------------
Viewing 10 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply