October 4, 2011 at 5:11 am
Table1
ItemID___ItemName___ItemPrice
-----------------------------
-----------------------------
101 _____McDonald's___ 90
Table2
UID______ItemID_____UserId______ContributedAmount
---------------------------------------------------
---------------------------------------------------
1 _______101________U001_________50
2 _______101________U002_________40
3 _______101________U003_________ 0
4 _______101________U004_________ 0
I have two tables shown as above , 4 Persons go to a restaurant and the total bill was 90$
Person1 pays 50$ and Person2 pays 40$ and other 2 persons didnt pay at all.
The paid amount should be shared equally among 4, Now i need a query to find out who needs to pay
to whom of how many amount ($'s) ? Help would be appreciated
Regards
Prasanna
October 4, 2011 at 6:50 am
It's a fun question!
This is the best I could come up with, but I'm sure there's a better/simpler way.
DECLARE @Table1 TABLE (
ItemID int,
ItemName nvarchar(100),
ItemPrice money
);
INSERT INTO @Table1 VALUES (101, 'McDonald''s', 90);
DECLARE @Table2 TABLE (
UID int,
ItemID int,
UserId char(4),
ContributedAmount money
);
INSERT INTO @Table2 VALUES (1 ,101,'U001',50);
INSERT INTO @Table2 VALUES (2 ,101,'U002',40);
INSERT INTO @Table2 VALUES (3 ,101,'U003', 0);
INSERT INTO @Table2 VALUES (4 ,101,'U004', 0);
WITH BaseData AS (
SELECT *,
Diff = TotalDue - ContributedAmount,
DebtCount = SUM(CASE WHEN TotalDue - ContributedAmount > 0 THEN 1 ELSE 0 END) OVER (),
CreditCount = SUM(CASE WHEN TotalDue - ContributedAmount <= 0 THEN 1 ELSE 0 END) OVER ()
FROM (
SELECT *,
TotalDue = (SELECT ItemPrice FROM @Table1 WHERE ItemId = T2.ItemId) / Participants
FROM (
SELECT *, Participants = COUNT(*) OVER()
FROM @Table2
) AS T2
) AS T3
)
SELECT A.UID, A.ItemID, A.ContributedAmount, A.UserId, A.Diff,
B.UserId, B.ContributedAmount, AmountDue = B.Diff / A.CreditCount
FROM BaseData AS A
INNER JOIN BaseData AS B
ON B.Diff < 0
WHERE A.Diff > 0;
Hope this helps
Gianluca
-- Gianluca Sartori
October 4, 2011 at 1:11 pm
It sounds like homework or test.
If not, can you please explain the business case?
October 4, 2011 at 1:35 pm
LutzM (10/4/2011)
It sounds like homework or test.If not, can you please explain the business case?
Nevertheless it was fun to code!
-- Gianluca Sartori
October 5, 2011 at 12:55 am
Haaa Haa , It was not a homework , I was just trying to implement a business scenario for sharing bills between room mates. The above query works like a charm when there are more than 1 contributors, but its not working as expected when only one person contibutes the full amount and later that amount needs to be distributed equally.
Consider Total Amount is 200$ , Person1 pays $200 , and Person 2 ,3,4 do not pay , when the above query is ran it shows $-150 to be paid by person 2,3,4 to Person 1.
Regards
October 5, 2011 at 3:00 am
I'm sorry. Here's the revised version:
SETUP:
DECLARE @Table1 TABLE (
ItemID int,
ItemName nvarchar(100),
ItemPrice money
);
INSERT INTO @Table1 VALUES (101, 'McDonald''s', 90);
DECLARE @Table2 TABLE (
UID int,
ItemID int,
UserId char(4),
ContributedAmount money
);
INSERT INTO @Table2 VALUES (1 ,101,'U001',60);
INSERT INTO @Table2 VALUES (2 ,101,'U002', 5);
INSERT INTO @Table2 VALUES (3 ,101,'U003', 5);
INSERT INTO @Table2 VALUES (4 ,101,'U004',20);
INSERT INTO @Table2 VALUES (5 ,101,'U005', 0);
INSERT INTO @Table2 VALUES (6 ,101,'U006', 0);
SOLUTION:
WITH BaseData AS (
SELECT *,
Diff = TotalDue - ContributedAmount,
DebtCount = SUM(CASE WHEN TotalDue - ContributedAmount > 0 THEN 1 ELSE 0 END) OVER (),
CreditCount = SUM(CASE WHEN TotalDue - ContributedAmount <= 0 THEN 1 ELSE 0 END) OVER (),
TotalCredit = SUM(CASE WHEN TotalDue - ContributedAmount <= 0 THEN TotalDue - ContributedAmount ELSE 0 END) OVER ()
FROM (
SELECT *,
TotalDue = (SELECT ItemPrice FROM @Table1 WHERE ItemId = T2.ItemId) / Participants
FROM (
SELECT *, Participants = COUNT(*) OVER()
FROM @Table2
) AS T2
) AS T3
)
SELECT Debt.TotalDue, Debt.UID, Debt.ItemID, Debt.ContributedAmount, Debt.UserId, Debt.Diff,
Debt.UserId, Debt.ContributedAmount, AmountDue = Debt.Diff * (Credit.Diff / Credit.TotalCredit) , Credit.UID
FROM BaseData AS Debt
INNER JOIN BaseData AS Credit
ON Credit.Diff < 0
WHERE Debt.Diff > 0;
-- Gianluca Sartori
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply