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/
Go to Top of Page

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
Go to Top of Page

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.
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

Upendra Gupta
Starting Member

12 Posts

Posted - 2014-09-19 : 06:15:19
unspammed
Go to Top of Page
   

- Advertisement -