May 3, 2017 at 10:02 am
I have a case where the total amount is divided by number of employees.( EMployees belongs to US and Non US countries)
Eg - total amount - 116 when I divide by 6 employees = it will get 19.33
but when 19.33 is multiplied with 6 it will return 115.98 ( 2 cents are missed here)
So how to rounding to whole cents since we are taking total amount in US dollars / # of employees; By default these extra 2 cents should go to first US employee.
Another Example -
112.19 / 5 = 22.438.
Round to two decimal places - we can round down and make it 22.43 in which case 4 of them get $22.43 and ideally the first US person $22.47
SELECT CASE WHEN EmpCode = 'USA' THEN TotAmount / @Employees
Else 0 END
from EMP
May 3, 2017 at 10:10 am
mcfarlandparkway - Wednesday, May 3, 2017 10:02 AMI have a case where the total amount is divided by number of employees.( EMployees belongs to US and Non US countries)Eg - total amount - 116 when I divide by 6 employees = it will get 19.33
but when 19.33 is multiplied with 6 it will return 115.98 ( 2 cents are missed here)So how to rounding to whole cents since we are taking total amount in US dollars / # of employees; By default these extra 2 cents should go to first US employee.
Another Example -
112.19 / 5 = 22.438.
Round to two decimal places - we can round down and make it 22.43 in which case 4 of them get $22.43 and ideally the first US person $22.47
SELECT CASE WHEN EmpCode = 'USA' THEN TotAmount / @EmployeesElse 0 END
from EMP
That is, indeed, a rounding issue.
Now, how about providing sample data, DDL and expected results? Also, take care to define exactly what 'first US employee' means. First in age, speed, height, weight, class?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
May 3, 2017 at 10:29 am
table -1 - Expenses
ID total Amount (decimal(26,9),null) EmpID
123 112.19 84746788
table - 2 - EmployeeExpenses
ID EMpID TotalEmployees
123 84746788 22.47
123 89246532 22.43
123 88456788 22.43
123 89346788 22.43
123 87536788 22.43
First US person is the one who entered expense first.
May 3, 2017 at 10:37 am
mcfarlandparkway - Wednesday, May 3, 2017 10:29 AMFirst US person is the one who entered expense first.
How do you know who entered the expense first from your example data? Is it the one that has their EmpID next to the the value in Table1, or the one that is at the top of the table (which also happens to be the one in table1).
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
May 3, 2017 at 11:09 am
Hazarding a guess here (sorry for formatting, SSC still hates my paste and I can't be bothered to fix it). I'll admit,this does it in two passes, not great, but others feel free to improve:USE TestDB;
GO
CREATE TABLE Expense
(ID int,
Expense decimal(26,9), --Does this need to be to 9 decimal places? Will you really be paying someone .00000001 of a dollar?
EmpID int);
GO
CREATE TABLE EmployeeExpense
(ID int,
EmpID int,
Expense decimal(12,2)); --More reasonable value for a monatary figure
GO
INSERT INTO Expense
VALUES
(123, 112.19, 84746788);
GO
INSERT INTO EmployeeExpense
VALUES
(123,84746788,NULL),
(123,89246532,NULL),
(123,88456788,NULL),
(123,89346788,NULL),
(123,87536788,NULL);
GO
UPDATE EmployeeExpense
SET Expense = (SELECT (FLOOR((E.Expense / COUNT(EE.ID)) * 100)) / 100
FROM Expense E
JOIN EmployeeExpense EE ON E.ID = EE.ID
WHERE E.ID = EmployeeExpense.ID
GROUP BY E.ID, E.Expense)
WHERE EmployeeExpense.ID = 123;
GO
--As it currently stands
SELECT *
FROM EmployeeExpense;
GO
UPDATE EE
Set Expense = EE.Expense + (SELECT sqE.Expense - SUM(sqEE.Expense)
FROM Expense sqE
JOIN EmployeeExpense sqEE ON sqE.ID = sqEE.ID
WHERE sqE.ID = E.ID
GROUP BY sqE.ID, sqE.Expense)
FROM EmployeeExpense EE
JOIN Expense E ON EE.ID = E.ID AND EE.EmpID = E.EmpID
WHERE E.ID = 123;
GO
--Final product
SELECT *
FROM EmployeeExpense;
GO
DROP TABLE Expense;
DROP TABLE EmployeeExpense;
GO
Edit: With one parse. Again, I still have no idea if this is right, as Phil rightfully points out below, we really need more detail.DECLARE @ID int = 123;
WITH EvenSplit AS (
SELECT EE.ID, EE.EmpID, E.EmpID AS FirstEmpID, E.Expense,
CAST((FLOOR((E.Expense / COUNT(EE.ID) OVER (PARTITION BY E.ID)) * 100)) / 100 AS decimal(12,2)) AS EmployeeExpense
FROM Expense E
JOIN EmployeeExpense EE ON E.ID = EE.ID
WHERE E.ID = @ID)
, WithRemainder AS (
SELECT ES.ID, ES.EmpID,
CASE WHEN ES.EmpID != ES.FirstEmpID THEN ES.EmployeeExpense
ELSE ES.EmployeeExpense + (ES.Expense - SUM(ES.EmployeeExpense) OVER (PARTITION BY ES.ID)) END AS EmployeeExpense
FROM EvenSplit ES
)
UPDATE EmployeeExpense
SET Expense = WR.EmployeeExpense
FROM WithRemainder WR
WHERE EmployeeExpense.ID = WR.ID
AND EmployeeExpense.EmpID = WR.EmpID;
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
May 3, 2017 at 12:42 pm
mcfarlandparkway - Wednesday, May 3, 2017 10:29 AMtable -1 - Expenses
ID total Amount (decimal(26,9),null) EmpID
123 112.19 84746788
table - 2 - EmployeeExpenses
ID EMpID TotalEmployees
123 84746788 22.47
123 89246532 22.43
123 88456788 22.43
123 89346788 22.43
123 87536788 22.43First US person is the one who entered expense first.
No DDL = no answer from me. Why should others do your work for you? There is no time in your data, therefore what crystal ball do you need to invoke in order to determine who did anything first? You've been here long enough to know better than provide such low-quality information.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply