Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Query

Author

Topic

rocknpop
Posting Yak Master

201 Posts

Posted - 2014-01-31 : 08:04:05

DECLARE @TBL TABLE(ID INT IDENTITY(1,1), Amount DECIMAL(18,2), Flag INT)

INSERT INTO @TBL
VALUES (5.00, 1),
(8.00, 1),
(7.00, 1),
--(50,1) if this amount is used then there will be a left-over balance; left-over balance needs to be inserted in amountPaid
(10,3),
(15,3),
(15,2),
(25,2),
(1.50,2)

SELECT * FROM @TBL

DECLARE @amountPaid TABLE(ID INT IDENTITY(1,1), tbl_ID INT, tbl_PayID INT, Amount DECIMAL(18,2))
INSERT INTO @amountPaid VALUES(6,1,5),(6,3,7) -- 2 amounts(5 and 7) are exahusted fully against expense of 15 (1st expense; Flag=2 are expenses)

SELECT * FROM @amountPaid

We need to apply amount received against the expenses done. These are captured in table @amountPaid.
In the table @Tbl, Amount received is denoted by Flag=1 and expenses by Flag=2. This table has another set, Flag=3. These are same as amount received but ones with Flag=1 take priority over ones with Flag=3.
So in case all amount received are exahusted with Flag=1 and we still have expenses left then we start exhausting the amounts from Flag=3.
In the above example as you can see the amountPaid table has 2 amounts that were used up to exahaust against the expenses.

This is the way isnerts take place in amountPaid table: tbl_ID=the expense (ID from @TBL where Flag=2) we are exhausting against and tbl_PayID (ID from @TBL where Flag=1) is the amount received we used to pay from,
and Amount column will have the actual amount we are exahusting.

Any pending amount received gets applied to the next expense and so on. When all ones with Flag=1 are exahusted, apply from Flag=3.Also, in case amounts from Flag=1 exceed or there are any left-over amounts then
they should get inserted into amountPaid table with tbl_PayID as 0 and left-over amount in the Amount column.

--This is a sample insert that I created based on the above conditions, all amounts are fully exhausted after the amounts 5 & 7
-- INSERT INTO @amountPaid VALUES(6,2,3),(7,2,6),(7,4,10),(7,5,9),(8,5,1.5)

--------------------
Rock n Roll with SQL

rocknpop
Posting Yak Master

201 Posts

Posted - 2014-01-31 : 21:40:29

Anyone? Is the requirement not too clear?

It is the table @amountPaid that needs to be filled-in with amount received(Flag=1) and this amount needs to be applied against expenses(Flag=2).

Thanks

--------------------
Rock n Roll with SQL

rocknpop
Posting Yak Master

201 Posts

Posted - 2014-02-02 : 00:39:15

Ok here is some more clarity, run the following query and the final output is the one I need to manipulate to adjust payments to expenses:

DECLARE @TBL TABLE(ID INT IDENTITY(1,1), Amount DECIMAL(18,2), Flag INT)
DECLARE @amountPaid TABLE(ID INT IDENTITY(1,1), tbl_ID INT, tbl_PayID INT, Amount DECIMAL(18,2), TOTAL MONEY)

INSERT INTO @TBL
VALUES (5.00, 1),
(8.00, 1),
(7.00, 1),
--(50,1) if this amount is used then there will be a left-over balance
(10,3),
(15,3),
(15,2),
(25,2),
(1.50,2)

;with
cte as (--running total of payments
select
p.ID as PaymentId, p.Amount as PaymentAmount,
total_payments = TAB.Amount
from @TBL p
CROSS APPLY(SELECT SUM(AMOUNT)AS Amount FROM @TBL WHERE Id<= P.Id and Flag in (1,3) )TAB
WHERE Flag in (1,3)

),
cte2 as(--running total of expenses
select
p.ID as ExpenseId, p.Amount as ExpenseAmount,
total_expenses = TAB.Amount
from @TBL p
CROSS APPLY(SELECT SUM(AMOUNT)AS Amount FROM @TBL WHERE Id<= P.Id and Flag =2 )TAB
WHERE Flag = 2
)

SELECT
*,
CASE WHEN ExpenseAmount>=PaymentAmount THEN ExpenseAmount-total_payments ELSE total_payments-ExpenseAmount end AS Balance
FROM
(
SELECT * FROM cte2
cross apply cte
)TAB
--WHERE ExpenseAmount>=total_payments

Thanks

--------------------
Rock n Roll with SQL

rocknpop
Posting Yak Master

201 Posts

Posted - 2014-02-03 : 23:00:52

Anyone?

--------------------
Rock n Roll with SQL

VeeranjaneyuluAnnapureddy
Posting Yak Master

169 Posts

Posted - 2014-02-03 : 23:42:15

can u show the expected result .......

Veera

rocknpop
Posting Yak Master

201 Posts

Posted - 2014-02-04 : 01:26:05

Ok here is the link to the expected output:

http://s0.uploads.im/kplR6.gif

Please let me know if you have any questions.

Thanks

--------------------
Rock n Roll with SQL

rocknpop
Posting Yak Master

201 Posts

Posted - 2014-02-04 : 11:01:15

Anyone,is the requirement not clear?

Thanks

--------------------
Rock n Roll with SQL

rocknpop
Posting Yak Master

201 Posts

Posted - 2014-02-05 : 04:00:06

Not a single reply but so many reads...

--------------------
Rock n Roll with SQL

rocknpop
Posting Yak Master

201 Posts

Posted - 2014-02-06 : 06:46:46

Still no reply, what am I missing, can someone please let me know.

Thanks

--------------------
Rock n Roll with SQL

rocknpop
Posting Yak Master

201 Posts

Posted - 2014-02-07 : 04:57:16

Can someone at least let me know if I missed anything with the requirement or if I did not post correctly?

Thanks

--------------------
Rock n Roll with SQL

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-02-07 : 09:57:36

Can't think of any set-based solution for your requirement . . looks like need to resort to the while loop to get the job done

KH
[spoiler]Time is always against us[/spoiler]

rocknpop
Posting Yak Master

201 Posts

Posted - 2014-02-07 : 11:32:10

Thanks a lot for the reply, appreciate this.

--------------------
Rock n Roll with SQL

rocknpop
Posting Yak Master

201 Posts

Posted - 2014-02-10 : 07:56:34

Hi,written a loop to achieve this, posting the same in case anyone has a similar requirement or if anyone wants to improve upon this.

DECLARE @TBL TABLE(ID INT IDENTITY(1,1), Amount MONEY, Flag INT)

--FLAG=1 ARE PAYMENTS RECEIVED
--FLAG=2 ARE EXPENSES
--FLAG=3 ARE PAYMENTS BUT THE ONES WITH FLAG=1 ARE EXHAUSTED FIRST, FOLLOWED BY FLAG=3 PAYMENTS

INSERT INTO @TBL
VALUES (-5.00,1),
(-20.00,1),
(-25.00,1),
(-5.00,1),
(10,3),
(10,3),
(15.00,2),
(25.00,2),
(1.50, 2),
(10.00,2),
(5.00,2),
(5.00,2)

--SELECT * FROM @TBL

DECLARE @Paytbl TABLE(expenseID INT, payID INT, amountPaid MONEY)
DECLARE @payID INT, @payAmt MONEY
DECLARE @ExpenseID INT, @ExpenseAmt MONEY
DECLARE @balance MONEY

--FETCH ALL EXPENSES
DECLARE CUR CURSOR
FOR SELECT ID,AMOUNT FROM @TBL WHERE FLAG=2 ORDER BY ID

OPEN CUR
FETCH NEXT FROM CUR INTO @ExpenseID, @ExpenseAmt

DECLARE @Flag TINYINT = 1

SELECT @payID = MIN(ID) FROM @TBL WHERE flag=1

--IF PAY AMOUNT IS AVAILABLE
IF @payID IS NOT NULL
SELECT @payAmt=ABS(Amount) FROM @TBL WHERE flag=1 AND ID = @payID
ELSE --NO PAYMENTS; PAY FROM PAYMENTS WITH FLAG=3
BEGIN
SELECT @payID = MIN(ID) FROM @TBL WHERE flag=3
IF @payID IS NOT NULL
SELECT @payAmt=ABS(Amount) FROM @TBL WHERE flag=3 AND ID = @payID
SET @Flag = 3
END

--LOOP THROUGH ALL EXPENSES
WHILE @@FETCH_STATUS = 0
BEGIN

--IF EXPENSE AMOUNT IS GREATER THAN PAY AMOUNT
IF @ExpenseAmt>=@payAmt
BEGIN
INSERT INTO @Paytbl VALUES(@ExpenseID, @payID, @payAmt)
--THIS IS BALANCE EXPENSE
SET @balance = @ExpenseAmt - @payAmt

--IF BALANCE IS 0, FETCH NEXT PAY AMOUNT TO PROCESS
IF @balance = 0 SET @payAmt = 0

--IF BALANCE STILL EXISTS
WHILE @balance > 0
BEGIN

--FETCH NEXT PAY AMOUT
--SELECT TOP 1 @payID=ID, @payAmt=ABS(Amount) FROM @TBL WHERE flag=1 AND ID > @payID
SELECT @payID = MIN(ID) FROM @TBL WHERE flag=@Flag AND ID > @payID

--IF PAY AMOUNT IS AVAILABLE
IF @payID IS NOT NULL
SELECT @payAmt=ABS(Amount) FROM @TBL WHERE flag=@Flag AND ID = @payID
ELSE IF @payID IS NULL --NO PAYMENTS; PAY FROM PAYMENTS WITH FLAG=3
BEGIN
IF @Flag = 1 SELECT @payID = MIN(ID) FROM @TBL WHERE flag=3
IF @payID IS NOT NULL
SELECT @payAmt=ABS(Amount) FROM @TBL WHERE flag=3 AND ID = @payID
SET @Flag = 3
END

--CHECK IF BALANCE IS MORE THAN NEXT PAY AMOUNT
IF @balance>=@payAmt AND @payID IS NOT NULL
BEGIN
INSERT INTO @Paytbl VALUES(@ExpenseID, @payID, @payAmt)
SET @balance = @balance - @payAmt

--IF BALANCE AND PAY AMOUNT ARE SAME, FETCH NEXT PAY AMOUNT TO PROCESS
IF @balance = 0 SET @payAmt = 0
END
ELSE--BALANCE IS LESS THAN NEXT PAY AMOUNT
BEGIN
INSERT INTO @Paytbl VALUES(@ExpenseID, @payID, @balance)
SET @payAmt = @payAmt - @balance
SET @balance = 0 -- EXIT LOOP AS COMPLETE (EXPENSE) BALANCE AS HAS BEEN MAPPED TO PAYMENT
END

END--END OF WHILE

END--END OF IF @ExpenseAmt>=@payAmt
ELSE --EXPENSE AMOUNT IS LESS THAN PAY AMOUNT
BEGIN
INSERT INTO @Paytbl VALUES(@ExpenseID, @payID, @ExpenseAmt)
SET @payAmt = @payAmt - @ExpenseAmt
END

--IF @payAmt = 0, FETCH NEXT PAY AMOUNT TO PROCESS
IF @payAmt = 0
BEGIN
SELECT @payID = MIN(ID) FROM @TBL WHERE flag=@Flag AND ID > @payID

--IF PAY AMOUNT IS AVAILABLE
IF @payID IS NOT NULL
SELECT @payAmt=ABS(Amount) FROM @TBL WHERE flag=@Flag AND ID = @payID
ELSE IF @payID IS NULL --NO PAYMENTS; PAY FROM PAYMENTS WITH FLAG=3
BEGIN
IF @Flag = 1 SELECT @payID = MIN(ID) FROM @TBL WHERE flag=3
IF @payID IS NOT NULL
SELECT @payAmt=ABS(Amount) FROM @TBL WHERE flag=3 AND ID = @payID
SET @Flag = 3
END
END

--FETCH NEXT EXPENSE AMOUNT
FETCH NEXT FROM CUR INTO @ExpenseID, @ExpenseAmt

END

CLOSE CUR
DEALLOCATE CUR

--THIS IS PART OF THE PAYMENT LEFT AFTER EXHAUSTING MOST OF THE PAYMENTS
SELECT * FROM
(
SELECT @payID AS PayID, @payamt AS BalancePayAmount
)T
WHERE payID IS NOT NULL

--THESE ARE THE PAYMENTS MADE
SELECT * FROM @Paytbl WHERE payID IS NOT NULL

--THIS IS JUST A CHECK TO HOW MUCH EXPENSE HAS BEEN PAID OFF
--SELECT expenseID, SUM(amountPaid) AS expenseAmount
--FROM @Paytbl
--WHERE payID IS NOT NULL
--GROUP BY expenseID

--THESE ARE THE PAYMENTS STILL LEFT AFTER PAYING OFF ALL EXPENSES
--SELECT *
--FROM @Tbl T LEFT JOIN @Paytbl P ON T.ID = P.payID
--WHERE P.payID IS NULL
-- AND T.Flag IN (1,3)

Edit: added a condition-> IF @balance = 0 SET @payAmt = 0

Thanks

--------------------
Rock n Roll with SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources