What is the best way to SELECT SUM while using PARTITION BY and WHERE and GROUP BY?

Question

What is the best way to SELECT SUM while using PARTITION BY and WHERE and GROUP BY?

Firewolf

SSC Veteran

Points: 270
More actions
August 19, 2016 at 10:36 am

#400598

Hello,
I have a small stored procedure that is doing what I believe is a fairly complex task. Maybe I'm overthinking it, but my SQL experience is rusty and limited.
I have a view that pulls the data rows I need and I need to sum one of the columns [Number of Employees] WHERE the [PYear] is either the current year or last year.
This is simple, you say. Yes, it would be. Except that the rows are single transactions and the [Number of Employees] is the total number of employees for a [Company]. The problem is that if a [Company] has 150 employees and there are 62 transactions, then if I SUM that column, I get 62x150 = 9300, which is obviously wrong.
You say this is still not that complex, there are ways around that. Here's another twist.
I need the total [Number of Employees] under a [Sales Rep] who is over multiple [Company]. 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).
Hopefully some of you are saying, "That's still easy" and are correct, because that's what I said when I was building my SSRS report, but I was wrong. This SQL provides data to an SSRS DataSet.
Here is what I am looking to achieve.
Example resultant table data: (Sorry, I can't figure out how to paste a table in neatly)
Sales Rep -- Number of Employees Previous Year -- Number of Employees Current Year
Allen -- 246 -- NULL
Rob -- 2585 -- 2105
Steve -- 1850 -- 1850
Mark -- 6935 -- 7894
Leah -- 44215 -- 46289
Jenny -- 200 -- 852
Jim -- 1451 -- 1398
Pat -- 9035 -- 10452
Well, here is the stored procedure and I'll let you ask questions or give recommendations on how to achieve this goal.
What I need this to return is the [Sales Rep], [Number of employees Previous Year], [Number of Employees Current Year] as it is listed above.
ALTER PROCEDURE [dbo].[StoredProcedureName]
-- Add the parameters for the stored procedure here
@Period_Year nvarchar(4),
@Period_Month nvarchar(2)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
WITH DEDUPE AS (
SELECT [Sales Rep]
,[Company]
,[Transaction Date]
,[PYear]
,[PMonth]
,[Number Of Employees]
,[Inception Date]
,[Term Date]
,ROW_NUMBER() OVER (PARTITION BY [Company] ORDER BY [Company],[Transaction Date] DESC) AS RowNum
FROM [vw_ViewName]
WHERE Cast([PYear] AS INT) = Cast(@Period_Year AS INT) AND [Inception Date] < DATEADD(MONTH, DATEDIFF(MONTH, -1, CONCAT(Right(@Period_Month,2), '/1/', @Period_Year))-1, -1) AND ([Term Date] > DATEADD(MONTH, DATEDIFF(MONTH, -1, CONCAT(Right(@Period_Month,2), '/1/', @Period_Year))-1, -1) OR [Term Date] IS NULL)
)
SELECT [Sales Rep], SUM([Number Of Employees]) AS NumEmpCurrYear
FROM DEDUPE
WHERE RowNum = 1
GROUP BY [Sales Rep]
END
Please note that I realize the above SP only returns the current year based on the code above.
The WHERE clause and SELECT after that would need to change:
WHERE Cast([PYear] AS INT) >= Cast(@Period_Year AS INT)-1 AND [Inception Date] < DATEADD(MONTH, DATEDIFF(MONTH, -1, CONCAT(Right(@Period_Month,2), '/1/', @Period_Year))-1, -1) AND ([Term Date] > DATEADD(MONTH, DATEDIFF(MONTH, -1, CONCAT(Right(@Period_Month,2), '/1/', @Period_Year))-1, -1) OR [Term Date] IS NULL)
)
SELECT [Sales Rep], SUM([Number Of Employees]) AS NumEmpCurrYear -- I would need to add some kind of CASE WHEN here for the SUM and generate two fields, one for current and one for previous year.
FROM DEDUPE
WHERE RowNum = 1
GROUP BY [Sales Rep]
What this stored procedure does is SUMs the number of employees for each sales rep. The PARTITION BY is used to group the data by [Company] and then SUM the RowNum = 1 (basically TOP 1) of each [Company] for each [Sales Rep]. Hopefully you are able to follow.
Here is a sample of a couple rows of data in raw form.
SALES REP -- COMPANY -- TRANSACTION DATE -- PYEAR -- PMONTH -- Number Of Employees
Allen -- Acme -- 2015-12-15 -- 2015 -- 12 -- 246
Allen -- Acme -- 2015-11-03 -- 2015 -- 11 -- 246
Mark -- Walmart -- 2016-04-01 -- 2016 -- 04 -- 7854
Mark -- Walmart -- 2015-09-04 -- 2015 -- 09 -- 6916
Mark -- Sql Server Central -- 2016-01-11 -- 2016 -- 01 -- 40
Steve -- Home Depot -- 2015-08-04 -- 2015 -- 08 -- 1850
Steve -- Home Depot -- 2016-02-25 -- 2016 -- 02 -- 1850
--EDIT-- In the sample data, I did not include the Inception Date or Term Date. Let's assume the sample data is only active companies. My query filters out data that doesn't meet the Inception Date AND Term Date criteria. If you need it for testing purposes, feel free to include more sample data with those columns. --END EDIT--
So the parent grouping needs to be [Sales Rep], the child grouping has to be [Company], and only one row per [Company] needs to be taken into consideration in order to get the [Number of Employees] for that [Company].
But if you look at the WHERE clause, you'll see that [Inception Date] and [Term Date] are considered. This is to determine if the [Company] is active or not. So if I ran this report on 7/2016, I would not want to include data from a [Company] for 2016 if they were terminated prior to 7/2016.
I know this is not the best method to do this and I think using temp tables and/or variables would be much more effective, but my experiences with these are minimal. Or at least that's what my memory tells me.
I hope this is enough info and that it makes sense.
Thank you.
Gavin

Viewing 15 posts - 1 through 15 (of 24 total)

You must be logged in to reply to this topic. Login to reply

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 1

gjoye (8/19/2016)
Here is a sample of a couple rows of data in raw form.
SALES REP -- COMPANY -- TRANSACTION DATE -- PYEAR -- PMONTH -- Number Of Employees
Allen -- Acme -- 2015-12-15 -- 2015 -- 12 -- 246
Allen -- Acme -- 2015-11-03 -- 2015 -- 11 -- 246
Mark -- Walmart -- 2016-04-01 -- 2016 -- 04 -- 7854
Mark -- Walmart -- 2015-09-04 -- 2015 -- 09 -- 6916
Mark -- Sql Server Central -- 2016-01-11 -- 2016 -- 01 -- 40
Steve -- Home Depot -- 2015-08-04 -- 2015 -- 08 -- 1850
Steve -- Home Depot -- 2016-02-25 -- 2016 -- 02 -- 1850
So the parent grouping needs to be [Sales Rep], the child grouping has to be [Company], and only one row per [Company] needs to be taken into consideration in order to get the [Number of Employees] for that [Company].
But if you look at the WHERE clause, you'll see that [Inception Date] and [Term Date] are considered. This is to determine if the [Company] is active or not. So if I ran this report on 7/2016, I would not want to include data from a [Company] for 2016 if they were terminated prior to 7/2016.
I know this is not the best method to do this and I think using temp tables and/or variables would be much more effective, but my experiences with these are minimal. Or at least that's what my memory tells me.
I hope this is enough info and that it makes sense.
Thank you.
Gavin

maybe I have missed it, but I dont see "Inception date" or "Term date" in your sample data?

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Firewolf SSC Veteran Points: 270 More actions · Answer 2

Sorry about that.

I was assuming that all sample data is for active companies and would satisfy the WHERE clause. I just forgot to mention that this is what I was assuming.

I also forgot to re-order the SELECT so the fields matched up with the sample data.

I EDITED my post to reflect these two items.

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 3

ok...I think I understand what you want, but your description of the problem comapred to the limited sample data and expected results doesnt clarify it for me.

suggest you post some more sample data amd the expected results of that sample data.

if you need help in doing that...please read this https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Firewolf SSC Veteran Points: 270 More actions · Answer 4

-- EDIT -- including the Inception Date and Term Date.

Sample data table:

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

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

| Allen | Acme | 12/15/2015 | 2015 | 12 | 246 | 1/1/2014 | 1/21/2016 |

| Allen | Acme | 11/3/2015 | 2015 | 11 | 246 | 1/1/2014 | 1/21/2016 |

| Allen | Acme | 6/22/2015 | 2015 | 6 | 246 | 1/1/2014 | 1/21/2016 |

| Allen | Acme | 6/21/2015 | 2015 | 6 | 246 | 1/1/2014 | 1/21/2016 |

| Allen | Acme | 4/23/2015 | 2015 | 4 | 246 | 1/1/2014 | 1/21/2016 |

| Allen | Acme | 11/19/2015 | 2015 | 11 | 246 | 1/1/2014 | 1/21/2016 |

| Allen | Acme | 2/2/2015 | 2015 | 2 | 246 | 1/1/2014 | 1/21/2016 |

| Allen | Acme | 9/1/2015 | 2015 | 9 | 246 | 1/1/2014 | 1/21/2016 |

| Jenny | Ozzie's | 4/2/2016 | 2016 | 4 | 132 | 6/25/2013 | NULL |

| Jenny | PC Repair | 11/25/2015 | 2015 | 11 | 200 | 10/10/2015 | NULL |

| Jenny | PC Repair | 2/16/2016 | 2016 | 2 | 720 | 10/10/2015 | NULL |

| Jenny | PC Repair | 1/17/2016 | 2016 | 1 | 720 | 10/10/2015 | NULL |

| Jenny | Ozzie's | 6/29/2016 | 2016 | 6 | 132 | 6/25/2013 | NULL |

| Jenny | PC Repair | 10/22/2015 | 2015 | 10 | 200 | 10/10/2015 | NULL |

| Jenny | PC Repair | 4/3/2016 | 2016 | 4 | 720 | 10/10/2015 | NULL |

| Jenny | PC Repair | 7/7/2016 | 2016 | 7 | 720 | 10/10/2015 | NULL |

| Leah | Apple | 2/1/2015 | 2015 | 2 | 24125 | 7/9/2010 | NULL |

| Leah | Apple | 6/25/2015 | 2015 | 6 | 24125 | 7/9/2010 | NULL |

| Leah | Apple | 3/11/2016 | 2016 | 3 | 25418 | 7/9/2010 | NULL |

| Leah | Google | 4/12/2015 | 2015 | 4 | 20090 | 10/16/2012 | NULL |

| Leah | Google | 5/14/2016 | 2016 | 5 | 20871 | 10/16/2012 | NULL |

| Leah | Google | 2/28/2016 | 2016 | 2 | 20871 | 10/16/2012 | NULL |

| Leah | Google | 8/4/2015 | 2015 | 8 | 20090 | 10/16/2012 | NULL |

| Leah | Google | 6/28/2015 | 2015 | 6 | 20090 | 10/16/2012 | NULL |

| Leah | Apple | 8/14/2015 | 2015 | 8 | 24125 | 7/9/2010 | NULL |

| Leah | Apple | 6/28/2015 | 2015 | 6 | 24125 | 7/9/2010 | NULL |

| Leah | Apple | 1/20/2016 | 2016 | 1 | 25418 | 7/9/2010 | NULL |

| Leah | Google | 11/4/2015 | 2015 | 11 | 20090 | 10/16/2012 | NULL |

| Leah | Google | 6/18/2016 | 2016 | 6 | 20871 | 10/16/2012 | NULL |

| Leah | Google | 8/2/2016 | 2016 | 8 | 20871 | 10/16/2012 | NULL |

| Leah | Google | 3/19/2015 | 2015 | 3 | 20090 | 10/16/2012 | NULL |

| Leah | Google | 4/25/2015 | 2015 | 4 | 20090 | 10/16/2012 | NULL |

| Mark | SQL Server Central | 5/2/2016 | 2016 | 5 | 40 | 5/6/2015 | NULL |

| Mark | SQL Server Central | 5/20/2015 | 2015 | 5 | 19 | 5/6/2015 | NULL |

| Mark | Walmart | 4/1/2016 | 2016 | 4 | 7854 | 2/10/2015 | NULL |

| Mark | Walmart | 9/4/2015 | 2015 | 9 | 6916 | 2/10/2015 | NULL |

| Mark | Walmart | 2/19/2016 | 2016 | 2 | 7854 | 2/10/2015 | NULL |

| Mark | SQL Server Central | 3/2/2016 | 2016 | 3 | 40 | 5/6/2015 | NULL |

| Mark | SQL Server Central | 12/5/2015 | 2015 | 12 | 19 | 5/6/2015 | NULL |

| Mark | Walmart | 7/6/2016 | 2016 | 7 | 7854 | 2/10/2015 | NULL |

| Mark | Walmart | 4/9/2015 | 2015 | 4 | 6916 | 2/10/2015 | NULL |

| Mark | Walmart | 9/1/2016 | 2016 | 9 | 7854 | 2/10/2015 | NULL |

| Pat | Oracle | 7/12/2015 | 2015 | 7 | 843 | 4/20/2015 | NULL |

| Pat | Microsoft | 6/12/2016 | 2016 | 6 | 9216 | 3/15/2009 | NULL |

| Pat | Microsoft | 5/5/2015 | 2015 | 5 | 8192 | 3/15/2009 | NULL |

| Pat | Oracle | 12/4/2015 | 2015 | 12 | 843 | 4/20/2015 | NULL |

| Pat | Microsoft | 5/22/2016 | 2016 | 5 | 9216 | 3/15/2009 | NULL |

| Pat | Microsoft | 2/5/2015 | 2015 | 2 | 8192 | 3/15/2009 | NULL |

| Pat | Oracle | 4/18/2016 | 2016 | 4 | 1236 | 4/20/2015 | NULL |

| Pat | Oracle | 2/2/2016 | 2016 | 2 | 1236 | 4/20/2015 | NULL |

| Rob | AT&T | 12/14/2015 | 2015 | 12 | 443 | 9/12/2013 | 2/12/2016 |

| Rob | Comcast | 1/1/2016 | 2016 | 1 | 548 | 12/22/2014 | NULL |

| Rob | Comcast | 1/1/2015 | 2015 | 1 | 658 | 12/22/2014 | NULL |

| Rob | Sprint | 8/8/2015 | 2015 | 8 | 682 | 11/30/2014 | NULL |

| Rob | Sprint | 9/8/2015 | 2015 | 9 | 682 | 11/30/2014 | NULL |

| Rob | Verizon | 5/31/2015 | 2015 | 5 | 802 | 2/16/2012 | NULL |

| Rob | Verizon | 8/10/2015 | 2015 | 8 | 802 | 2/16/2012 | NULL |

| Rob | AT&T | 10/12/2015 | 2015 | 10 | 443 | 9/12/2013 | 2/12/2016 |

| Rob | Comcast | 6/8/2016 | 2016 | 6 | 548 | 12/22/2014 | NULL |

| Rob | Comcast | 4/2/2015 | 2015 | 4 | 658 | 12/22/2014 | NULL |

| Rob | Sprint | 6/18/2016 | 2016 | 6 | 692 | 11/30/2014 | NULL |

| Rob | Sprint | 4/14/2015 | 2015 | 4 | 682 | 11/30/2014 | NULL |

| Rob | Verizon | 1/11/2015 | 2015 | 1 | 802 | 2/16/2012 | NULL |

| Rob | Verizon | 6/12/2016 | 2016 | 6 | 865 | 2/16/2012 | NULL |

| Steve | Home Depot | 8/4/2015 | 2015 | 8 | 1850 | 8/5/2013 | NULL |

| Steve | Home Depot | 1/26/2016 | 2016 | 1 | 1850 | 8/5/2013 | NULL |

| Steve | Home Depot | 6/6/2015 | 2015 | 6 | 1850 | 8/5/2013 | NULL |

| Steve | Home Depot | 10/8/2015 | 2015 | 10 | 1850 | 8/5/2013 | NULL |

| Steve | Home Depot | 4/8/2015 | 2015 | 4 | 1850 | 8/5/2013 | NULL |

| Steve | Home Depot | 4/13/2016 | 2016 | 4 | 1850 | 8/5/2013 | NULL |

| Steve | Home Depot | 7/19/2016 | 2016 | 7 | 1850 | 8/5/2013 | NULL |

| Steve | Home Depot | 2/17/2015 | 2015 | 2 | 1850 | 8/5/2013 | NULL |

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

Result table:

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

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

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

Firewolf SSC Veteran Points: 270 More actions · Answer 5

If there is a better way to do this, please let me know. And remember that this is going to an SSRS report. The tablix in the SSRS report is currently grouped by Sales Rep on the rows and by year on the columns (only for current and previous year).

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 6

shot in the dark......whilst you have posted sample data it is not easily reproducable in SSMS......did you read the link I provided ? (eg CREATE TABLE / INSERT DATA scripts)

try this and see how you go....if this doesnt deliver as required please provide the create/insert scripts.

here is one way I think .....

WITH cte AS (

SELECT DISTINCT SalesRep, PYear, NumberofEmployees FROM yourtable

)

SELECT SalesRep,

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

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

FROM cte

GROUP BY SalesRep

ORDER BY SalesRep

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

MMartin1 One Orange Chip Points: 27764 More actions · Answer 7

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.

----------------------------------------------------

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 8

Firewolf, it may seem frustrating to ask a question and get sent a link to an article about asking questions, but there is a reason for it. We don't need to READ your input data, we need a script that creates and populates the table(s) so that we can work with it and test with it. Like this:

CREATE TABLE #SampleData

(SalesRep varchar(20), Company varchar(20), TransactionDate date

, PYear smallint, Pmonth tinyint, NumberEmployees int

, InceptionDate date, TermDate date)

INSERT INTO #SampleData

VALUES

('Allen','Acme','12/15/2015','2015','12','246','1/1/2014','1/21/2016')

,('Allen','Acme','11/3/2015','2015','11','246','1/1/2014','1/21/2016')

etc

The only thing we need to read is what you would expect the results to be from the sample data you provide.

In the time it took me to write that script above, I could have been working on solving your problem. I promise that if you will take the time to do a script like that with all your sample data, more people will jump in to help you out and offer you TESTED SOLUTIONS. You get a proven answer faster. It's really worth your while to help us help you. Now if you will just finish out that values clause with the rest of your data, we'll all get to work. 😉

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Firewolf SSC Veteran Points: 270 More actions · Answer 9

Thanks for the responses. I did see the request to re-read how to post the script for the data. I had skimmed over the article the first time, but on 2nd read I understood what was being asked.

I apologize, I have not had the time to go back and do that. Been swamped. I will get to it as soon as I can.

Thank you.

Firewolf SSC Veteran Points: 270 More actions · Answer 10

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

MMartin1 One Orange Chip Points: 27764 More actions · Answer 11

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

I got a lot of errors when I tried running your script. Did you test it out before posting it? For instance... your term date column is defined as NOT NULL yet most of the rows have exactly that for the column's entry. Try this again and also show your expected output. It helps to see this for ourselves.

----------------------------------------------------

Firewolf SSC Veteran Points: 270 More actions · Answer 12

Ahhhhhh, I knew I'd forget to do something important when I had to retype the post.

I forgot to remove a few things from the code I got from the CSV to SQL converter site. Sorry about that.

CREATE TABLE mytable(

Sales_Rep NVARCHAR(5) NOT NULL

,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