September 19, 2014 at 4:29 am
HI ALL , I HAVE FOLLOWING QUERY
I WANT MY SERVICED_AMT SHOULD BE SUBTRACTED FROM TRAN_AMT FOR EACH ROW ONCE ITS DONE CSERVICED_FLAG SHOULD BE UPDATE TO 1 WHERE
ACCOUNT_NO = ACCOUNT_NOAND AFTER SUBTRACTING SERVICED_AMT ONCE IT BECOMES ZERO UPDATE DSERVICED_FLAG TO 1
-----query --
SELECT DEBIT.ACCOUNT_NO, DEBIT.SERVICED_AMT,DEBIT.TRANSACTION_VALUE_DATE,DEBIT.SERVICED_FLAG,
CREDIT.ACCOUNT_NO,CREDIT.TRAN_AMT,CREDIT.SERVICED_FLAG,CREDIT.[TRANSACTION_VALUE_DATE]
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
----
33105126375286533.002014-08-30 00:00:00.000033105126375865800.0002014-06-27 00:00:00.000
33105126375286533.002014-08-30 00:00:00.000033105126375130000.0002014-06-27 00:00:00.000
4560510798019522.002014-08-30 00:00:00.0000456051079801767.2902014-06-27 00:00:00.000
4560510798019522.002014-08-30 00:00:00.0000456051079809801.1402014-06-27 00:00:00.000
4560510798019522.002014-08-30 00:00:00.00004560510798085452.9602014-06-27 00:00:00.000
455050006611013088.002014-08-30 00:00:00.00004550500066110706.2702014-06-27 00:00:00.000
4560510798019522.002014-08-30 00:00:00.00004560510798015442.8102014-06-27 00:00:00.000
i need help pls send me the query
Thanks & regards
Rajnidas
September 19, 2014 at 8:30 am
Please don't use all caps. It is considered yelling and is really difficult to read.
Now let's turn that unformatted mess into something legible.
SELECT DEBIT.ACCOUNT_NO
,DEBIT.SERVICED_AMT
,DEBIT.TRANSACTION_VALUE_DATE
,DEBIT.SERVICED_FLAG
,CREDIT.ACCOUNT_NO
,CREDIT.TRAN_AMT
,CREDIT.SERVICED_FLAG
,CREDIT.[TRANSACTION_VALUE_DATE]
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
What we really are missing is a clear picture of what you want to do. It sounds like maybe you are trying to do a running total? Here is one way to do that. http://www.sqlservercentral.com/articles/T-SQL/68467/[/url]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 21, 2014 at 12:11 am
Quick suggestion for a solution, if I got the description right;-)
😎
;WITH BASE_DATA(
DEP_ACCOUNT_NO
,DEP_SERVICED_AMT
,DEP_TRANSACTION_VALUE_DATE
,DEP_SERVICED_FLAG
,CRED_ACCOUNT_NO
,CRED_TRAN_AMT
,CRED_SERVICED_FLAG
,CRED_TRANSACTION_VALUE_DATE)
AS (
SELECT * FROM (VALUES
('33105126375',286533.00,'2014-08-30 00:00:00.000',0,'33105126375',865800.00,0,'2014-06-27 00:00:00.000')
,('33105126375',286533.00,'2014-08-30 00:00:00.000',0,'33105126375',130000.00,0,'2014-06-27 00:00:00.000')
,('45605107980',19522.00,'2014-08-30 00:00:00.000',0,'45605107980',1767.29, 0,'2014-06-27 00:00:00.000')
,('45605107980',19522.00,'2014-08-30 00:00:00.000',0,'45605107980',9801.14, 0,'2014-06-27 00:00:00.000')
,('45605107980',19522.00,'2014-08-30 00:00:00.000',0,'45605107980',85452.96,0,'2014-06-27 00:00:00.000')
,('45505000661',1013088.00,'2014-08-30 00:00:00.000',0,'45505000661',10706.27,0,'2014-06-27 00:00:00.000')
,('45605107980',19522.00,'2014-08-30 00:00:00.000',0,'45605107980',15442.81,0,'2014-06-27 00:00:00.000')
) AS X(
DEP_ACCOUNT_NO
,DEP_SERVICED_AMT
,DEP_TRANSACTION_VALUE_DATE
,DEP_SERVICED_FLAG
,CRED_ACCOUNT_NO
,CRED_TRAN_AMT
,CRED_SERVICED_FLAG
,CRED_TRANSACTION_VALUE_DATE)
)
SELECT
BD.DEP_ACCOUNT_NO
,BD.DEP_SERVICED_AMT
,BD.DEP_TRANSACTION_VALUE_DATE
,CASE WHEN BD.DEP_SERVICED_AMT - SUM(BD.CRED_TRAN_AMT) OVER
(
PARTITION BY BD.CRED_ACCOUNT_NO
ORDER BY BD.CRED_TRANSACTION_VALUE_DATE
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW
) > 0 THEN 0 ELSE 1 END AS DEP_SERVICED_FLAG
,BD.CRED_ACCOUNT_NO
,BD.CRED_TRAN_AMT
,CASE WHEN BD.DEP_SERVICED_AMT - SUM(BD.CRED_TRAN_AMT) OVER
(
PARTITION BY BD.CRED_ACCOUNT_NO
ORDER BY BD.CRED_TRANSACTION_VALUE_DATE
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW
) > 0 THEN 0 ELSE 1 END AS CRED_SERVICED_FLAG
,BD.CRED_TRANSACTION_VALUE_DATE
FROM BASE_DATA BD;
Results
DEP_ACCOUNT_NO DEP_SERVICED_AMT DEP_TRANSACTION_VALUE_DATE DEP_SERVICED_FLAG CRED_ACCOUNT_NO CRED_TRAN_AMT CRED_SERVICED_FLAG CRED_TRANSACTION_VALUE_DATE
-------------- ----------------- -------------------------- ----------------- --------------- -------------- ------------------ ---------------------------
33105126375 286533.00 2014-08-30 00:00:00.000 0 33105126375 130000.00 0 2014-06-27 00:00:00.000
33105126375 286533.00 2014-08-30 00:00:00.000 1 33105126375 865800.00 1 2014-06-27 00:00:00.000
45505000661 1013088.00 2014-08-30 00:00:00.000 0 45505000661 10706.27 0 2014-06-27 00:00:00.000
45605107980 19522.00 2014-08-30 00:00:00.000 1 45605107980 85452.96 1 2014-06-27 00:00:00.000
45605107980 19522.00 2014-08-30 00:00:00.000 1 45605107980 9801.14 1 2014-06-27 00:00:00.000
45605107980 19522.00 2014-08-30 00:00:00.000 1 45605107980 15442.81 1 2014-06-27 00:00:00.000
45605107980 19522.00 2014-08-30 00:00:00.000 1 45605107980 1767.29 1 2014-06-27 00:00:00.000
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply