Firewolf (8/26/2016)

---

MMartin1 (8/23/2016)

---

Hi :

Since we dont have the information that is in your mind, we need a more structure approach so that we can effectively understand your issue without ambiguity. Here is another article that will help with that : http://www.sqlservercentral.com/articles/Best+Practices/61537/

Your explanation seems fragmented and goes back and forth it seems, not linear. For example , what do the following mean >

"Except that the rows are single transactions and the [Number of Employees] is the total number of employees for a [Company]. ",

It cant be the total number of employees from the data you provided. This number of employees does not belong in a transaction table if it is a mere current snapshot. How is it that Leah has 44215 and 46289 employees from the data in the transaction table? There is more missing here.

"And it has to be grouped by year and filtered down. And I need to do this based on input parameters (@Period_Year and @Period_Month)."

Filtered by year OR year/month? Please be clear. I mean what happens if there are no transactions in a month? Are there no employees in the company that month?

Just answering these questions is not a fix. Take the time to carefully read through the material and put forth the information (tables , data, what they represent at a granular level) asked for in the articles. Then we can get a better idea of what you have first and foremost , and where you want to go with it.

MMartin, well, I had typed up a response to this that tried to better explain everything and by the time I finished it and added the SQL INSERT table, the site timed out and I didn't copy it before clicking preview, so I lost the whole reply. I try to make it a habit of copying my text before I click submit / preview on submission forms in case it times out like this, but I forgot this time. So I just retyped (differently) the response below. Here is the SQL INSERT table:

CREATE TABLE mytable(

Sales_Rep NVARCHAR(5) NOT NULL PRIMARY KEY

,Company NVARCHAR(18) NOT NULL

,Transaction_Date DATE NOT NULL

,PYear INTEGER NOT NULL

,PMonth INTEGER NOT NULL

,Number_of_Employees INTEGER NOT NULL

,Inception_Date DATE NOT NULL

,Term_Date DATE NOT NULL

);

INSERT INTO mytable(Sales_Rep,Company,Transaction_Date,PYear,PMonth,Number_of_Employees,Inception_Date,Term_Date) VALUES (N'Allen',N'Acme','12/15/2015',2015,12,246,'1/1/2014','1/21/2016');

INSERT INTO mytable(Sales_Rep,Company,Transaction_Date,PYear,PMonth,Number_of_Employees,Inception_Date,Term_Date) VALUES (N'Allen',N'Acme','11/3/2015',2015,11,246,'1/1/2014','1/21/2016');

INSERT INTO mytable(Sales_Rep,Company,Transaction_Date,PYear,PMonth,Number_of_Employees,Inception_Date,Term_Date) VALUES (N'Allen',N'Acme','6/22/2015',2015,6,246,'1/1/2014','1/21/2016');

INSERT INTO mytable(Sales_Rep,Company,Transaction_Date,PYear,PMonth,Number_of_Employees,Inception_Date,Term_Date) VALUES (N'Allen',N'Acme','6/21/2015',2015,6,246,'1/1/2014','1/21/2016');

INSERT INTO mytable(Sales_Rep,Company,Transaction_Date,PYear,PMonth,Number_of_Employees,Inception_Date,Term_Date) VALUES (N'Allen',N'Acme','4/23/2015',2015,4,246,'1/1/2014','1/21/2016');

INSERT INTO mytable(Sales_Rep,Company,Transaction_Date,PYear,PMonth,Number_of_Employees,Inception_Date,Term_Date) VALUES (N'Allen',N'Acme','11/19/2015',2015,11,246,'1/1/2014','1/21/2016');

INSERT INTO mytable(Sales_Rep,Company,Transaction_Date,PYear,PMonth,Number_of_Employees,Inception_Date,Term_Date) VALUES (N'Allen',N'Acme','2/2/2015',2015,2,246,'1/1/2014','1/21/2016');

INSERT INTO mytable(Sales_Rep,Company,Transaction_Date,PYear,PMonth,Number_of_Employees,Inception_Date,Term_Date) VALUES (N'Allen',N'Acme','9/1/2015',2015,9,246,'1/1/2014','1/21/2016');

INSERT INTO mytable(Sales_Rep,Company,Transaction_Date,PYear,PMonth,Number_of_Employees,Inception_Date,Term_Date) VALUES (N'Jenny',N'Ozzie''s','4/2/2016',2016,4,132,'6/25/2013',NULL);

INSERT INTO mytable(Sales_Rep,Company,Transaction_Date,PYear,PMonth,Number_of_Employees,Inception_Date,Term_Date) VALUES (N'Jenny',N'PC Repair','11/25/2015',2015,11,200,'10/10/2015',NULL);

INSERT INTO mytable(Sales_Rep,Company,Transaction_Date,PYear,PMonth,Number_of_Employees,Inception_Date,Term_Date) VALUES (N'Jenny',N'PC Repair','2/16/2016',2016,2,720,'10/10/2015',NULL);

INSERT INTO mytable(Sales_Rep,Company,Transaction_Date,PYear,PMonth,Number_of_Employees,Inception_Date,Term_Date) VALUES (N'Jenny',N'PC Repair','1/17/2016',2016,1,720,'10/10/2015',NULL);

INSERT INTO mytable(Sales_Rep,Company,Transaction_Date,PYear,PMonth,Number_of_Employees,Inception_Date,Term_Date) VALUES (N'Jenny',N'Ozzie''s','6/29/2016',2016,6,132,'6/25/2013',NULL);

INSERT INTO mytable(Sales_Rep,Company,Transaction_Date,PYear,PMonth,Number_of_Employees,Inception_Date,Term_Date) VALUES (N'Jenny',N'PC Repair','10/22/2015',2015,10,200,'10/10/2015',NULL);

INSERT INTO mytable(Sales_Rep,Company,Transaction_Date,PYear,PMonth,Number_of_Employees,Inception_Date,Term_Date) VALUES (N'Jenny',N'PC Repair','4/3/2016',2016,4,720,'10/10/2015',NULL);

INSERT INTO mytable(Sales_Rep,Company,Transaction_Date,PYear,PMonth,Number_of_Employees,Inception_Date,Term_Date) VALUES (N'Jenny',N'PC Repair','7/7/2016',2016,7,720,'10/10/2015',NULL);

INSERT INTO mytable(Sales_Rep,Company,Transaction_Date,PYear,PMonth,Number_of_Employees,Inception_Date,Term_Date) VALUES (N'Leah',N'Apple','2/1/2015',2015,2,24125,'7/9/2010',NULL);

INSERT INTO mytable(Sales_Rep,Company,Transaction_Date,PYear,PMonth,Number_of_Employees,Inception_Date,Term_Date) VALUES (N'Leah',N'Apple','6/25/2015',2015,6,24125,'7/9/2010',NULL);

INSERT INTO mytable(Sales_Rep,Company,Transaction_Date,PYear,PMonth,Number_of_Employees,Inception_Date,Term_Date) VALUES (N'Leah',N'Apple','3/11/2016',2016,3,25418,'7/9/2010',NULL);

INSERT INTO mytable(Sales_Rep,Company,Transaction_Date,PYear,PMonth,Number_of_Employees,Inception_Date,Term_Date) VALUES (N'Leah',N'Google','4/12/2015',2015,4,20090,'10/16/2012',NULL);

INSERT INTO mytable(Sales_Rep,Company,Transaction_Date,PYear,PMonth,Number_of_Employees,Inception_Date,Term_Date) VALUES (N'Leah',N'Google','5/14/2016',2016,5,20871,'10/16/2012',NULL);

INSERT INTO mytable(Sales_Rep,Company,Transaction_Date,PYear,PMonth,Number_of_Employees,Inception_Date,Term_Date) VALUES (N'Leah',N'Google','2/28/2016',2016,2,20871,'10/16/2012',NULL);

INSERT INTO mytable(Sales_Rep,Company,Transaction_Date,PYear,PMonth,Number_of_Employees,Inception_Date,Term_Date) VALUES (N'Leah',N'Google','8/4/2015',2015,8,20090,'10/16/2012',NULL);

INSERT INTO mytable(Sales_Rep,Company,Transaction_Date,PYear,PMonth,Number_of_Employees,Inception_Date,Term_Date) VALUES (N'Leah',N'Google','6/28/2015',2015,6,20090,'10/16/2012',NULL);

INSERT INTO mytable(Sales_Rep,Company,Transaction_Date,PYear,PMonth,Number_of_Employees,Inception_Date,Term_Date) VALUES (N'Leah',N'Apple','8/14/2015',2015,8,24125,'7/9/2010',NULL);

INSERT INTO mytable(Sales_Rep,Company,Transaction_Date,PYear,PMonth,Number_of_Employees,Inception_Date,Term_Date) VALUES (N'Leah',N'Apple','6/28/2015',2015,6,24125,'7/9/2010',NULL);

INSERT INTO mytable(Sales_Rep,Company,Transaction_Date,PYear,PMonth,Number_of_Employees,Inception_Date,Term_Date) VALUES (N'Leah',N'Apple','1/20/2016',2016,1,25418,'7/9/2010',NULL);

INSERT INTO mytable(Sales_Rep,Company,Transaction_Date,PYear,PMonth,Number_of_Employees,Inception_Date,Term_Date) VALUES (N'Leah',N'Google','11/4/2015',2015,11,20090,'10/16/2012',NULL);

INSERT INTO mytable(Sales_Rep,Company,Transaction_Date,PYear,PMonth,Number_of_Employees,Inception_Date,Term_Date) VALUES (N'Leah',N'Google','6/18/2016',2016,6,20871,'10/16/2012',NULL);

INSERT INTO mytable(Sales_Rep,Company,Transaction_Date,PYear,PMonth,Number_of_Employees,Inception_Date,Term_Date) VALUES (N'Leah',N'Google','8/2/2016',2016,8,20871,'10/16/2012',NULL);

INSERT INTO mytable(Sales_Rep,Company,Transaction_Date,PYear,PMonth,Number_of_Employees,Inception_Date,Term_Date) VALUES (N'Leah',N'Google','3/19/2015',2015,3,20090,'10/16/2012',NULL);

INSERT INTO mytable(Sales_Rep,Company,Transaction_Date,PYear,PMonth,Number_of_Employees,Inception_Date,Term_Date) VALUES (N'Leah',N'Google','4/25/2015',2015,4,20090,'10/16/2012',NULL);

INSERT INTO mytable(Sales_Rep,Company,Transaction_Date,PYear,PMonth,Number_of_Employees,Inception_Date,Term_Date) VALUES (N'Mark',N'SQL Server Central','5/2/2016',2016,5,40,'5/6/2015',NULL);

INSERT INTO mytable(Sales_Rep,Company,Transaction_Date,PYear,PMonth,Number_of_Employees,Inception_Date,Term_Date) VALUES (N'Mark',N'SQL Server Central','5/20/2015',2015,5,19,'5/6/2015',NULL);

INSERT INTO mytable(Sales_Rep,Company,Transaction_Date,PYear,PMonth,Number_of_Employees,Inception_Date,Term_Date) VALUES (N'Mark',N'Walmart','4/1/2016',2016,4,7854,'2/10/2015',NULL);

INSERT INTO mytable(Sales_Rep,Company,Transaction_Date,PYear,PMonth,Number_of_Employees,Inception_Date,Term_Date) VALUES (N'Mark',N'Walmart','9/4/2015',2015,9,6916,'2/10/2015',NULL);

INSERT INTO mytable(Sales_Rep,Company,Transaction_Date,PYear,PMonth,Number_of_Employees,Inception_Date,Term_Date) VALUES (N'Mark',N'Walmart','2/19/2016',2016,2,7854,'2/10/2015',NULL);

INSERT INTO mytable(Sales_Rep,Company,Transaction_Date,PYear,PMonth,Number_of_Employees,Inception_Date,Term_Date) VALUES (N'Mark',N'SQL Server Central','3/2/2016',2016,3,40,'5/6/2015',NULL);

INSERT INTO mytable(Sales_Rep,Company,Transaction_Date,PYear,PMonth,Number_of_Employees,Inception_Date,Term_Date) VALUES (N'Mark',N'SQL Server Central','12/5/2015',2015,12,19,'5/6/2015',NULL);

INSERT INTO mytable(Sales_Rep,Company,Transaction_Date,PYear,PMonth,Number_of_Employees,Inception_Date,Term_Date) VALUES (N'Mark',N'Walmart','7/6/2016',2016,7,7854,'2/10/2015',NULL);

INSERT INTO mytable(Sales_Rep,Company,Transaction_Date,PYear,PMonth,Number_of_Employees,Inception_Date,Term_Date) VALUES (N'Mark',N'Walmart','4/9/2015',2015,4,6916,'2/10/2015',NULL);

INSERT INTO mytable(Sales_Rep,Company,Transaction_Date,PYear,PMonth,Number_of_Employees,Inception_Date,Term_Date) VALUES (N'Mark',N'Walmart','9/1/2016',2016,9,7854,'2/10/2015',NULL);

INSERT INTO mytable(Sales_Rep,Company,Transaction_Date,PYear,PMonth,Number_of_Employees,Inception_Date,Term_Date) VALUES (N'Pat',N'Oracle','7/12/2015',2015,7,843,'4/20/2015',NULL);

INSERT INTO mytable(Sales_Rep,Company,Transaction_Date,PYear,PMonth,Number_of_Employees,Inception_Date,Term_Date) VALUES (N'Pat',N'Microsoft','6/12/2016',2016,6,9216,'3/15/2009',NULL);

INSERT INTO mytable(Sales_Rep,Company,Transaction_Date,PYear,PMonth,Number_of_Employees,Inception_Date,Term_Date) VALUES (N'Pat',N'Microsoft','5/5/2015',2015,5,8192,'3/15/2009',NULL);

INSERT INTO mytable(Sales_Rep,Company,Transaction_Date,PYear,PMonth,Number_of_Employees,Inception_Date,Term_Date) VALUES (N'Pat',N'Oracle','12/4/2015',2015,12,843,'4/20/2015',NULL);

INSERT INTO mytable(Sales_Rep,Company,Transaction_Date,PYear,PMonth,Number_of_Employees,Inception_Date,Term_Date) VALUES (N'Pat',N'Microsoft','5/22/2016',2016,5,9216,'3/15/2009',NULL);

INSERT INTO mytable(Sales_Rep,Company,Transaction_Date,PYear,PMonth,Number_of_Employees,Inception_Date,Term_Date) VALUES (N'Pat',N'Microsoft','2/5/2015',2015,2,8192,'3/15/2009',NULL);

INSERT INTO mytable(Sales_Rep,Company,Transaction_Date,PYear,PMonth,Number_of_Employees,Inception_Date,Term_Date) VALUES (N'Pat',N'Oracle','4/18/2016',2016,4,1236,'4/20/2015',NULL);

INSERT INTO mytable(Sales_Rep,Company,Transaction_Date,PYear,PMonth,Number_of_Employees,Inception_Date,Term_Date) VALUES (N'Pat',N'Oracle','2/2/2016',2016,2,1236,'4/20/2015',NULL);

INSERT INTO mytable(Sales_Rep,Company,Transaction_Date,PYear,PMonth,Number_of_Employees,Inception_Date,Term_Date) VALUES (N'Rob',N'AT&T','12/14/2015',2015,12,443,'9/12/2013','2/12/2016');

INSERT INTO mytable(Sales_Rep,Company,Transaction_Date,PYear,PMonth,Number_of_Employees,Inception_Date,Term_Date) VALUES (N'Rob',N'Comcast','1/1/2016',2016,1,548,'12/22/2014',NULL);

INSERT INTO mytable(Sales_Rep,Company,Transaction_Date,PYear,PMonth,Number_of_Employees,Inception_Date,Term_Date) VALUES (N'Rob',N'Comcast','1/1/2015',2015,1,658,'12/22/2014',NULL);

INSERT INTO mytable(Sales_Rep,Company,Transaction_Date,PYear,PMonth,Number_of_Employees,Inception_Date,Term_Date) VALUES (N'Rob',N'Sprint','8/8/2015',2015,8,682,'11/30/2014',NULL);

INSERT INTO mytable(Sales_Rep,Company,Transaction_Date,PYear,PMonth,Number_of_Employees,Inception_Date,Term_Date) VALUES (N'Rob',N'Sprint','9/8/2015',2015,9,682,'11/30/2014',NULL);

INSERT INTO mytable(Sales_Rep,Company,Transaction_Date,PYear,PMonth,Number_of_Employees,Inception_Date,Term_Date) VALUES (N'Rob',N'Verizon','5/31/2015',2015,5,802,'2/16/2012',NULL);

INSERT INTO mytable(Sales_Rep,Company,Transaction_Date,PYear,PMonth,Number_of_Employees,Inception_Date,Term_Date) VALUES (N'Rob',N'Verizon','8/10/2015',2015,8,802,'2/16/2012',NULL);

INSERT INTO mytable(Sales_Rep,Company,Transaction_Date,PYear,PMonth,Number_of_Employees,Inception_Date,Term_Date) VALUES (N'Rob',N'AT&T','10/12/2015',2015,10,443,'9/12/2013','2/12/2016');

INSERT INTO mytable(Sales_Rep,Company,Transaction_Date,PYear,PMonth,Number_of_Employees,Inception_Date,Term_Date) VALUES (N'Rob',N'Comcast','6/8/2016',2016,6,548,'12/22/2014',NULL);

INSERT INTO mytable(Sales_Rep,Company,Transaction_Date,PYear,PMonth,Number_of_Employees,Inception_Date,Term_Date) VALUES (N'Rob',N'Comcast','4/2/2015',2015,4,658,'12/22/2014',NULL);

INSERT INTO mytable(Sales_Rep,Company,Transaction_Date,PYear,PMonth,Number_of_Employees,Inception_Date,Term_Date) VALUES (N'Rob',N'Sprint','6/18/2016',2016,6,692,'11/30/2014',NULL);

INSERT INTO mytable(Sales_Rep,Company,Transaction_Date,PYear,PMonth,Number_of_Employees,Inception_Date,Term_Date) VALUES (N'Rob',N'Sprint','4/14/2015',2015,4,682,'11/30/2014',NULL);

INSERT INTO mytable(Sales_Rep,Company,Transaction_Date,PYear,PMonth,Number_of_Employees,Inception_Date,Term_Date) VALUES (N'Rob',N'Verizon','1/11/2015',2015,1,802,'2/16/2012',NULL);

INSERT INTO mytable(Sales_Rep,Company,Transaction_Date,PYear,PMonth,Number_of_Employees,Inception_Date,Term_Date) VALUES (N'Rob',N'Verizon','6/12/2016',2016,6,865,'2/16/2012',NULL);

INSERT INTO mytable(Sales_Rep,Company,Transaction_Date,PYear,PMonth,Number_of_Employees,Inception_Date,Term_Date) VALUES (N'Steve',N'Home Depot','8/4/2015',2015,8,1850,'8/5/2013',NULL);

INSERT INTO mytable(Sales_Rep,Company,Transaction_Date,PYear,PMonth,Number_of_Employees,Inception_Date,Term_Date) VALUES (N'Steve',N'Home Depot','1/26/2016',2016,1,1850,'8/5/2013',NULL);

INSERT INTO mytable(Sales_Rep,Company,Transaction_Date,PYear,PMonth,Number_of_Employees,Inception_Date,Term_Date) VALUES (N'Steve',N'Home Depot','6/6/2015',2015,6,1850,'8/5/2013',NULL);

INSERT INTO mytable(Sales_Rep,Company,Transaction_Date,PYear,PMonth,Number_of_Employees,Inception_Date,Term_Date) VALUES (N'Steve',N'Home Depot','10/8/2015',2015,10,1850,'8/5/2013',NULL);

INSERT INTO mytable(Sales_Rep,Company,Transaction_Date,PYear,PMonth,Number_of_Employees,Inception_Date,Term_Date) VALUES (N'Steve',N'Home Depot','4/8/2015',2015,4,1850,'8/5/2013',NULL);

INSERT INTO mytable(Sales_Rep,Company,Transaction_Date,PYear,PMonth,Number_of_Employees,Inception_Date,Term_Date) VALUES (N'Steve',N'Home Depot','4/13/2016',2016,4,1850,'8/5/2013',NULL);

INSERT INTO mytable(Sales_Rep,Company,Transaction_Date,PYear,PMonth,Number_of_Employees,Inception_Date,Term_Date) VALUES (N'Steve',N'Home Depot','7/19/2016',2016,7,1850,'8/5/2013',NULL);

INSERT INTO mytable(Sales_Rep,Company,Transaction_Date,PYear,PMonth,Number_of_Employees,Inception_Date,Term_Date) VALUES (N'Steve',N'Home Depot','2/17/2015',2015,2,1850,'8/5/2013',NULL);

I'll try to answer your questions here, as they were good questions:

Your explanation seems fragmented and goes back and forth it seems, not linear. For example , what do the following mean >

"Except that the rows are single transactions and the [Number of Employees] is the total number of employees for a [Company]. ",

It cant be the total number of employees from the data you provided. This number of employees does not belong in a transaction table if it is a mere current snapshot. How is it that Leah has 44215 and 46289 employees from the data in the transaction table? There is more missing here.

I totally agree - The Number of Employees does NOT belong with the transaction table. When I was first given the dataset (SQL view), I thought, "What is this? This is going to be complicated to calculate, if at all possible." This is how the data was provided to me and what I chose to work with rather than relying on my limited SQL experience to try to recapture the data in a different way. My role is a system admin so I don't have as much time to sit at my desk and plug away, unfortunately. (as much as I'd like to do that)

- To bring more clarity (hopefully) to this request: There are multiple tables being joined here in a view. The Accounts table has Sales Rep, Company, Inception Date, Term Date and Number of Employees (for the Company). The SSRS report is a sales report and has a tablix that processes row by row and calculates the sales for each Sales Rep the SUM of sales (transactions) - I don't have to tell you that this is a very simple process to get the sales data. The Number of Employees is an anomaly.

So the Accounts table is joined with one or two other tables that contain the transaction data ON Sales Rep.

In SSRS, I have a column grouping that groups the data by active Companies by the year of the transaction date IIF(Inception Date < Period Date [derived from PYear and PMonth] AND (Term Date > Period Date OR Term Date = NULL)). The row group is simply by Sales Rep.

- To answer the 2nd part, in the transaction table I provided where Leah has 44215 and 46289 employees, that number is indeed the total Number of Employees (snapshot) in the given period where the transaction took place. (in regards to the code I've posted, we are only considering period year, not month - I can handle expanding this once the numbers are correct for period year) What I've done is ignored the fact that it doesn't make sense to me to obtain the Number of Employees and just provide the data they want. If I may say this, throw out what you think is logical surrounding this field (Number of Employees) and what it represents and just think "is there a way to achieve this?" It hurts the brain, I know. 🙂 There are a number of things I could say about this field and I'm not going to.

"And it has to be grouped by year and filtered down. And I need to do this based on input parameters (@Period_Year and @Period_Month)."

Filtered by year OR year/month? Please be clear. I mean what happens if there are no transactions in a month? Are there no employees in the company that month?

Yes.

So, as stated earlier, it is currently grouped by year. The next step is to modify that by period (month and year), but it was easier to get and validate the data by year first. By period is an easy change.

What happens if there are no transactions in a month? You, my friend, are asking the questions I've asked. The idea is that it will be a YTD report. So if I selected my year=2016 and month=06 parameters, then I would get any records where a transaction fell between 1/1/2016 - 6/30/2016 AND the Company was active on 6/30/2016, plus the 2015 versions of this as well.

We realize that this would exclude active Companies who had no transactions in 2016 (or 2015) through June and skew numbers. It is something we need to clarify with the higher ups. I believe we are basing the report off of sales numbers and what sales occurred during the time period, not necessarily the number of active companies with their respective # of employees and sales numbers.

Hopefully this clears things up enough to get an idea going. Remember, I'm just the javelin thrower who was given a discus in a pole vaulting competition, so don't shoot the messenger (without reason).