Subtotalling by Employee ID

Question

Hi All

I have a SQL table that has the following:

EmployeeID | Account Code | Value

EMP1 1234 50.00
EMP1 3456 75
EMP2 2345 100.30
EMP2 5675 54.99

I need to get this in to a journal to import so need to output something like the below, but have no idea how to do it (or if it can):

EmployeeID | Account Code | Value |

EMP1 1234 50.00
EMP1 3456 75
EMP1 9999 -125.00

EMP2 2345 100.30
EMP2 5675 54.99
EMP2 9999 -155.29

So what I need is some sort of subtotal by employee ID with a negative sum of the values added together (contra entry in to a journal).

I will be outputting this to a .csv file. I have done a lot of work to get it to this point, and the total bit is the last bit.

Thank you in advance.

Answer 1

Hi @neilsja ,

Welcome to Microsoft Q&A!

Please also refer below:

CREATE TABLE mytable(EmployeeID VARCHAR(10), AccountCode INT, Value decimal(5,2));  
  
 INSERT INTO mytable  VALUES  
 ('EMP1', '1234', 50.00 ),  
 ('EMP1', '3456', 75),  
 ('EMP2', '2345', 100.30),  
 ('EMP2', '5675', 54.99 );  
  
select * from mytable  
union   
select EmployeeID,9999,-1* SUM(VALUE) from mytable  
group by EmployeeID  

Output:

EmployeeID AccountCode Value  
EMP1 1234 50.00  
EMP1 3456 75.00  
EMP1 9999 -125.00  
EMP2 2345 100.30  
EMP2 5675 54.99  
EMP2 9999 -155.29  

Best regards,
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hi @neilsja ,

Please try the following solution.
The combination of the GROUPING() function and the GROUP BY ROLLUP is for your scenario.

SQL

-- DDL and data population, start  
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, EmployeeID VARCHAR(10), AccountCode VARCHAR(10), DollarValue MONEY);  
INSERT INTO @tbl (EmployeeID, AccountCode, DollarValue) VALUES  
('EMP1', '1234', 50.00 ),  
('EMP1', '3456', 75	   ),  
('EMP2', '2345', 100.30),  
('EMP2', '5675', 54.99 );  
-- DDL and data population, end  
  
-- just to see what GROUPING() function does. The real use of it below  
SELECT  EmployeeID, AccountCode, SUM(DollarValue) AS DollarValue  
, GROUPING(EmployeeID) AS EmployeeIDGrp  
, GROUPING(AccountCode) AS AccountCodeGrp  
FROM @tbl   
GROUP BY ROLLUP(EmployeeID, AccountCode);  
  
-- real deal  
;WITH rs AS  
(  
	SELECT EmployeeID = IIF(GROUPING(EmployeeID)=0, EmployeeID, 'Grand Total')  
		, AccountCode = IIF(GROUPING(AccountCode)=1 AND GROUPING(EmployeeID)=0, '9999', AccountCode)  
		, DollarValue = IIF(GROUPING(AccountCode)=1, (-1) * SUM(DollarValue), SUM(DollarValue))  
	FROM @tbl  
	GROUP BY ROLLUP(EmployeeID, AccountCode)  
)  
SELECT * FROM rs  
WHERE AccountCode IS NOT NULL;  

Output

+------------+-------------+-------------+  
| EmployeeID | AccountCode | DollarValue |  
+------------+-------------+-------------+  
| EMP1       |        1234 |       50.00 |  
| EMP1       |        3456 |       75.00 |  
| EMP1       |        9999 |     -125.00 |  
| EMP2       |        2345 |      100.30 |  
| EMP2       |        5675 |       54.99 |  
| EMP2       |        9999 |     -155.29 |  
+------------+-------------+-------------+  

Answer 3

Hi All

Your help is so very much appreciated. What I thought would be something easy that SSIS could handle, seems to be more of a SQL job.

I am getting closer with the above, but still have an issue.

The SQL I have written is:

SELECT
Company,
EmployeeID,
JournalAccountCode = IIF(GROUPING(JournalAccountCode)=1 AND GROUPING(EmployeeID)=0, '9999', JournalAccountCode),
JournalNetofTotalAdjustedReclaimTax = IIF(GROUPING(JournalAccountCode)=1, (-1) * SUM(JournalNetofTotalAdjustedReclaimTax),
SUM(JournalNetofTotalAdjustedReclaimTax))
, GROUPING(Company) AS CompanyGrp
, GROUPING(EmployeeID) AS EmployeeIDGrp
, GROUPING(JournalAccountCode) AS AccountCodeGrp

FROM dbo.Data
GROUP BY ROLLUP(Company, EmployeeID, JournalAccountCode);

This then displays the following:

Company EmployeeID JournalAccountCode JournalNetofTotalAdjustedReclaimTax CompanyGrp EmployeeIDGrp AccountCodeGrp
COMP001 lhow 3024900600 1284.00 0 0 0
COMP001 lhow 3212900000 355.43 0 0 0
COMP001 lhow 3804000000 175.43 0 0 0
COMP001 lhow 3806000000 246.45 0 0 0
COMP001 lhow 3808000000 667.05 0 0 0
COMP001 lhow 3850900900 52.75 0 0 0
COMP001 lhow 3884900900 102.95 0 0 0
COMP001 lhow 9999 -3916.05 0 0 1
COMP001 NULL NULL -5587.29 0 1 1

The problem I have is the totals. The -3916.05 is correct, but the -5587.29 is a sum of all employees within COMP001. I just need -3916 to show, which is the debit value per employee (in this case lhow).

Answer 4

Hi @neilsja ,

Did you have a try with my solution? Is it very necessary to use GROUPING() and the GROUP BY ROLLUP for your scenario?

You could refer below:

CREATE TABLE Data(Company VARCHAR(10),EmployeeID VARCHAR(10), JournalAccountCode varchar(100), JournalNetofTotalAdjustedReclaimTax decimal(8,2));  
      
INSERT INTO Data  VALUES  
('COMP001', 'lhow', '3024900600', 1284.00),  
('COMP001', 'lhow', '3212900000', 355.43),  
('COMP001', 'lhow', '3804000000', 175.43),  
('COMP001', 'lhow', '3806000000', 246.45),  
('COMP001', 'lhow', '3808000000', 667.05),   
('COMP001', 'lhow', '3850900900', 52.75),  
('COMP001', 'lhow', '3884900900', 102.95)    
  
select * from Data  
union   
select Company,EmployeeID,'9999',-1* SUM(JournalNetofTotalAdjustedReclaimTax)   
from Data  
group by Company,EmployeeID  

Output:

Company	EmployeeID	JournalAccountCode	JournalNetofTotalAdjustedReclaimTax  
COMP001	lhow	3024900600	1284.00  
COMP001	lhow	3212900000	355.43  
COMP001	lhow	3804000000	175.43  
COMP001	lhow	3806000000	246.45  
COMP001	lhow	3808000000	667.05  
COMP001	lhow	3850900900	52.75  
COMP001	lhow	3884900900	102.95  
COMP001	lhow	9999	-2884.06  

If above is not working, please provide your actual data of Data table and expected output. Thanks.

Best regards,
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 5

Hi Melissa

Yes, it will need to be grouped.

I need it to be grouped by company, employeedID and JournalAccountCode.

So if you swapped one of the account codes to duplicate another, then I would then those same account codes grouped together to give me one line.