Need help for 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

General SQL Server Forums

New to SQL Server Programming

Need help for query

Author

Topic

rajnidas
Yak Posting Veteran

97 Posts

Posted - 2014-09-19 : 02:04:37

HI 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_NO AND AFTER SUBTRACTING SERVICED_AMT ONCE IT BECOMES ZERO UPDATE DSERVICED_FLAG TO 1

Row ACCOUNT_NO SERVICED_AMT TRANSACTION_VALUE_DATE DSERVICED_FLAG ACCOUNT_NO TRAN_AMT CSERVICED_FLAG TRANSACTION_VALUE_DATE
1 53105085752 136753.00 2014-08-30 00:00:00.000 0 53105085752 800000.00 0 2014-06-27 00:00:00.000
2 53105085752 136753.00 2014-08-30 00:00:00.000 0 53105085752 1000000.00 0 2014-08-27 00:00:00.000
3 53105085752 136753.00 2014-08-30 00:00:00.000 0 53105085752 0.00 0 2014-08-28 00:00:00.000
4 53105085752 136753.00 2014-08-30 00:00:00.000 0 53105085752 100000.00 0 2014-09-01 00:00:00.000
5 53105085752 136753.00 2014-08-30 00:00:00.000 0 53105085752 0.00 0 2014-09-03 00:00:00.000
6 53105085752 136753.00 2014-08-30 00:00:00.000 0 53105085752 100000000.00 0 2014-09-02 00:00:00.000

kindly need help

Thanks & regards

Rajnidas

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2014-09-19 : 04:01:27

Please show us what you have done so far and where you are stuck?

Harsh Athalye
http://www.letsgeek.net/

rajnidas
Yak Posting Veteran

97 Posts

Posted - 2014-09-19 : 04:41:01

SELECT ROW_NUMBER() OVER(ORDER BY CREDIT.ID ) AS Row,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

Actually this is the query

rajnidas
Yak Posting Veteran

97 Posts

Posted - 2014-09-19 : 04:44:17

Actually this is the query i wanted the result as per described

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_NO AND AFTER SUBTRACTING SERVICED_AMT ONCE IT BECOMES ZERO UPDATE DSERVICED_FLAG TO 1

but the major hurdle for me is how do i iterate on row basis so that i can subtract Serviced Amount with tran amount later on update the flags respectively

SELECT ROW_NUMBER() OVER(ORDER BY CREDIT.ID ) AS Row,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

thanks & regards

Rajnidas.

Arun Babu N
Starting Member

26 Posts

Posted - 2014-09-19 : 04:44:20

ok.you have given the o/p of this qry in prev post. what is your expected result..?

please show it as in table format.

arunbabu

rajnidas
Yak Posting Veteran

97 Posts

Posted - 2014-09-19 : 04:57:24

Row ACCOUNT_NO SERVICED_AMT TRANSACTION_VALUE_DATE DSERVICED_FLAG ACCOUNT_NO TRAN_AMT CSERVICED_FLAG TRANSACTION_VALUE_DATE
1 53105085752 1000.00 2014-08-30 00:00:00.000 0 53105085752 50.00 0 2014-06-27 00:00:00.000
2 53105085752 2000.00 2014-08-30 00:00:00.000 0 53105085752 50.00 0 2014-08-27 00:00:00.000
3 53105085753 3000.00 2014-08-30 00:00:00.000 0 53105085753 10.00 0 2014-08-28 00:00:00.000
4 53105085754 4000.00 2014-08-30 00:00:00.000 0 53105085754 100.00 0 2014-09-01 00:00:00.000
5 53105085755 500.00 2014-08-30 00:00:00.000 0 53105085755 20.00 0 2 014-09-03 00:00:00.000
6 53105085755 500.00 2014-08-30 00:00:00.000 0 53105085755 125.00 0 2014-09-02 00:00:00.000

need result
-------------
This is the expected output
first subtract TRAN_AMT to SERVICED_AMT after dat only the cserviced flag should b update to one
this should be followed in loop for particular account number once done go for next

SERVICED_AMT - TRAN_AMT

Row ACCOUNT_NO SERVICED_AMT TRANSACTION_VALUE_DATE DSERVICED_FLAG ACCOUNT_NO TRAN_AMT CSERVICED_FLAG TRANSACTION_VALUE_DATE
1 53105085752 1000.00 2014-08-30 00:00:00.000 0 53105085752 50.00 1 2014-06-27 00:00:00.000
2 53105085752 2000.00 2014-08-30 00:00:00.000 0 53105085752 50.00 1 2014-08-27 00:00:00.000
3 53105085753 3000.00 2014-08-30 00:00:00.000 0 53105085753 10.00 1 2014-08-28 00:00:00.000
4 53105085754 4000.00 2014-08-30 00:00:00.000 0 53105085754 100.00 1 2014-09-01 00:00:00.000
5 53105085755 500.00 2014-08-30 00:00:00.000 0 53105085755 20.00 1 2014-09-03 00:00:00.000
6 53105085755 500.00 2014-08-30 00:00:00.000 0 53105085755 125.00 1 2014-09-02 00:00:00.000

thanks

rajnidas

Arun Babu N
Starting Member

26 Posts

Posted - 2014-09-19 : 05:13:03

update CREDIT
Set CREDIT.SERVICED_FLAG = case when TRAN_AMT - SERVICED_AMT <0 then 1 else 0 end
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

arunbabu

Upendra Gupta
Starting Member

12 Posts

Posted - 2014-09-19 : 06:15:19

unspammed

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources