regarding payment querys - 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

regarding payment querys

Author

Topic

esambath
Yak Posting Veteran

89 Posts

Posted - 2009-04-29 : 06:09:55

Hi guys,

Please help this query

I have two table LP_UserManagement and LP_Payment

LP_UserManagement

UserId
Email
Username
Password
UserType(0=Admin,1=User,2=Expert)

LP_Payment

PaymentId,UserId,ExpertId,TxnId,PaymentMethod,Email,Price

sample datas

UserId Email Username Password UserType

1 sam@yahoo.com sam s 1
2 nesk@yahoo.com nesk s 2
3 kavi@yahoo.com kavi s 2
4 sim@yahoo.com sim s 1

PaymentId UserId ExpertId TxnId PaymentMethod Email Price
1 1 2 11 paypal a@a.com 7
2 4 3 12 paypal b@a.com 5
3 2 3 34 paypal c@a.com 8

output

Username PaymentMethod DebitAmount creditAmount

sam paypal 0 7
sim paypal 0 5
kavi paypal 7 8

(Debit Amount means incoming, credit Amount means outgoing)
This is my test query

select (Select NickName from LP_UserManagement where UserId=P.UserId) as Username,P.CreationDateTime,
PaymentMethod,TxnId,(select Sum(Price) from LP_Payment PP inner join LP_UserManagement U
ON PP.UserId=U.UserId where PP.UserId=P.UserId) as DebitAmount,P.* from LP_Payment P inner join LP_UserManagement U ON P.UserId=U.UserId where P.Deleted=0 order by P.creationdatetime desc

Thanks and regards
E.sambath

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-04-29 : 07:05:52

select t.Username, u.PaymentMethod, (select sum(price) from LP_Payment where t.userid = userid and userid = u.userid) as debitamount, u.price as creditAmount
from LP_UserManagement t
inner join LP_Payment as u on u.userid = t.userid

esambath
Yak Posting Veteran

89 Posts

Posted - 2009-04-29 : 07:25:51

dear bklr

Thank you for Message

Regards
sambath kumar

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-04-29 : 07:48:10

welcome

did u get the required output.......

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources