September 11, 2014 at 6:23 am
hi all,
SELECT debit.ACCOUNT_NO, debit.Serviced_Amt,credit.Tran_Amt,credit.Serviced_Flag
FROM tbl_Interest_Debit as debit
inner join
tbl_Credit as credit
on debit.ACCOUNT_NO=credit.Account_No
order by credit.TRANSACTION_VALUE_DATE
getting result
-------------------
ACCOUNT_NO Serviced_Amt Tran_Amt Serviced_Flag
------------ --------------- ------------ ------------
455050006611013088.0019157467.000
455050006611013088.0018988.960
33105126375286533.00171084.000
33105126375286533.00500000.000
33105126375286533.001000000.000
33105126375286533.00800000.000
33105126375286533.001000000.000
33105126375286533.001000000.000
I want that service_amount should be subtracted from tran_amt until service_amount become zero
Once service_amount becomes zero service_flag should be changed to 1. i need help.
thanks
Rajnidas
September 11, 2014 at 6:29 am
Please take a look at the following article on how to post questions in order to get the best help:
Forum Etiquette: How to post data/code on a forum to get the best help[/url]
Basically we need table DDL, some sample data and some desired output.
With that, people can better help to get you the query you need.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
September 11, 2014 at 8:42 am
And are you actually using SQL Server 2012? I ask because LEAD/LAG wasn't available until then and LEAD/LAG is the supported method for doing this. If not, then we'll have to use some unsupported "Black Arts" code to do this if you want some good performance out of this.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 11, 2014 at 1:17 pm
I started answering your question; but then realised the wording of your question has a nifty little logic bomb in it. Sorry you will have to clarify:
"I want that service_amount should be subtracted from tran_amt until service_amount become zero
Once service_amount becomes zero service_flag should be changed to 1. i need help."
How will the service_amount ever change?
Regardless, here is the sample date I've created based on your question, but if you want an answer you will need to provide quite a bit more information.
USE [TempDB]
GO
CREATE TABLE tbl_Interest_Debit(Account_No NVARCHAR(11), Serviced_Amt MONEY)
GO
CREATE TABLE tbl_Credit(Account_No NVARCHAR(11), TRANSACTION_VALUE_DATE DATETIME, Tran_Amt MONEY, Serviced_Flag BIT)
GO
INSERT INTO tbl_Interest_Debit
(Account_No, Serviced_Amt)
VALUES
('45505000661', 1013088.00)
, ('33105126375', 286533.00)
GO
INSERT INTO tbl_Credit
(Account_No, TRANSACTION_VALUE_DATE, Tran_Amt, Serviced_Flag)
VALUES
('45505000661', DATEADD(DAY, -9, GETDATE()), 19157467.00, 0)
, ('45505000661', DATEADD(DAY, -8, GETDATE()),18988.96, 0)
, ('33105126375', DATEADD(DAY, -7, GETDATE()),171084.00, 0)
, ('33105126375', DATEADD(DAY, -6, GETDATE()),500000.00, 0)
, ('33105126375', DATEADD(DAY, -5, GETDATE()),1000000.00, 0)
, ('33105126375', DATEADD(DAY, -4, GETDATE()),800000.00, 0)
, ('33105126375', DATEADD(DAY, -3, GETDATE()),1000000.00, 0)
, ('33105126375', DATEADD(DAY, -2, GETDATE()),1000000.00, 0)
, ('33105126375', DATEADD(DAY, -1, GETDATE()),18988.96, 0)
GO
The answer is likely going to begin with something like this:
SELECT
d.ACCOUNT_NO
, d.Serviced_Amt
, c.Tran_Amt
, LAG(d.Serviced_Amt, 1, 0) OVER (PARTITION BY d.ACCOUNT_NO ORDER BY c.TRANSACTION_VALUE_DATE) AS Last_Serviced_Amt
, c.Serviced_Flag
FROM
tbl_Interest_Debit d
INNER JOIN
tbl_Credit c ON d.ACCOUNT_NO = c.Account_No
;
September 13, 2014 at 9:16 am
rajnidas.y (9/11/2014)
hi all,SELECT debit.ACCOUNT_NO, debit.Serviced_Amt,credit.Tran_Amt,credit.Serviced_Flag
FROM tbl_Interest_Debit as debit
inner join
tbl_Credit as credit
on debit.ACCOUNT_NO=credit.Account_No
order by credit.TRANSACTION_VALUE_DATE
getting result
-------------------
ACCOUNT_NO Serviced_Amt Tran_Amt Serviced_Flag
------------ --------------- ------------ ------------
455050006611013088.0019157467.000
455050006611013088.0018988.960
33105126375286533.00171084.000
33105126375286533.00500000.000
33105126375286533.001000000.000
33105126375286533.00800000.000
33105126375286533.001000000.000
33105126375286533.001000000.000
I want that service_amount should be subtracted from tran_amt until service_amount become zero
Once service_amount becomes zero service_flag should be changed to 1. i need help.
thanks
Rajnidas
Quick solution using running total window function and a reversed engineered schema/data set, should be enough to get you passed this hurdle. Creating the schema and data took 98% of the time providing the answer;-)
😎
USE tempdb;
GO
;WITH tbl_Credit(TRANSACTION_VALUE_DATE,Account_No,Tran_Amt,Serviced_Flag) AS
( SELECT * FROM
(VALUES
('2014-06-01',45505000661,19157467.00,0)
,('2014-06-02',45505000661,18988.96 ,0)
,('2014-06-01',33105126375,171084.00 ,0)
,('2014-06-02',33105126375,500000.00 ,0)
,('2014-06-03',33105126375,1000000.00 ,0)
,('2014-06-04',33105126375,800000.00 ,0)
,('2014-06-05',33105126375,1000000.00 ,0)
,('2014-06-06',33105126375,1000000.00 ,0)
) AS X(TRANSACTION_VALUE_DATE,Account_No,Tran_Amt,Serviced_Flag)
)
,tbl_Interest_Debit(ACCOUNT_NO,Serviced_Amt) AS
( SELECT * FROM
(VALUES
(45505000661,1013088.00)
,(33105126375,286533.00 )
) AS X(ACCOUNT_NO,Serviced_Amt)
)
SELECT
debit.ACCOUNT_NO
,credit.TRANSACTION_VALUE_DATE
,debit.Serviced_Amt
,credit.Tran_Amt
,SUM(credit.Tran_Amt) OVER
(
PARTITION BY debit.ACCOUNT_NO
ORDER BY TRANSACTION_VALUE_DATE ASC
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW
) AS RT_Tran_Amt
,CASE
WHEN (debit.Serviced_Amt - SUM(credit.Tran_Amt) OVER
(
PARTITION BY debit.ACCOUNT_NO
ORDER BY TRANSACTION_VALUE_DATE ASC
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW
)) > 0 THEN 0
ELSE 1
END AS Serviced_Flag
FROM tbl_Interest_Debit as debit
inner join
tbl_Credit as credit
on debit.ACCOUNT_NO=credit.Account_No
Results
ACCOUNT_NO TRANSACTION_VALUE_DATE Serviced_Amt Tran_Amt RT_Tran_Amt Serviced_Flag
------------ ---------------------- ------------- ------------- ------------ -------------
33105126375 2014-06-01 286533.00 171084.00 171084.00 0
33105126375 2014-06-02 286533.00 500000.00 671084.00 1
33105126375 2014-06-03 286533.00 1000000.00 1671084.00 1
33105126375 2014-06-04 286533.00 800000.00 2471084.00 1
33105126375 2014-06-05 286533.00 1000000.00 3471084.00 1
33105126375 2014-06-06 286533.00 1000000.00 4471084.00 1
45505000661 2014-06-01 1013088.00 19157467.00 19157467.00 1
45505000661 2014-06-02 1013088.00 18988.96 19176455.96 1
September 13, 2014 at 10:02 am
CELKO (9/13/2014)
...blah, blah, blah, blah
...
You did read the earlier posts where the OP was asked for the DDL, sample data, and hopefully expected results (that helps a lot).
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply