select 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

select query

Author

Topic

esambath
Yak Posting Veteran

89 Posts

Posted - 2009-06-11 : 06:30:56

Hi experts

I have 2 table LP_Usermanagement,LP_Payment

LP_Payment

PaymentId , UserId , ExpertId , PaymentMethod price
1 4 6 Paypal 4
2 9 14 Paypal 4
3 2 1 Paypal 10
4 2 1 Paypal 10
5 2 1 Paypal 10
6 2 1 Paypal 10
7 2 1 Paypal 250

LP_Usermanagement

UserId Username
1 admin
2 sam
3 dhina
4 kotti
5 santhil
9 dhina

output

Username, PaymentMethod, DebitAmount, creditAmount ,balane
sam paypal 10 - 10
sam paypal 10 - 20
sam paypal 10 - 30
sam paypal 10 - 40
sam paypal 250 - 290

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

Select t.NickName as Username, u.PaymentMethod,
(Select sum(price) from LP_Payment where t.userid = userid and userid = u.userid
and ExpertId<>1) as debitamount,
(isnull((((select sum(price) from LP_Payment where UserId=2))-
(Select sum(price) from LP_Payment where t.userid = userid
and userid = u.userid and ExpertId<>1)),0)) as Balance,
isnull((Select sum(price) from LP_Payment
where ExpertId<>1 and t.userid = ExpertId ), '0')
as creditAmount,u.* from LP_UserManagement t inner join LP_Payment
as u on u.userid = t.userid where u.UserId =2

Thanks an Advance

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-11 : 06:38:27

what is your question ?

KH
[spoiler]Time is always against us[/spoiler]

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-06-11 : 06:40:45

[code]
select username, paymentmethod,price,
(select sum(price) from @LP_Payment where paymentid <= p.paymentid AND userid = 2)
from lp_payment p
inner join LP_Usermanagement u on u.userid= p.userid
where u.userid= 2
[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-11 : 06:42:02

The question is about running total.

E 12°55'05.63"
N 56°04'39.26"

esambath
Yak Posting Veteran

89 Posts

Posted - 2009-06-11 : 08:05:34

dear bklr,

Thanks for your meassage,

your query is working fine. we need debit,credit amount and balance

Please help this concern

output

Username, paymentmetho, Debit, credit, balane
sam Paypal 10 0 10
sam Paypal 10 0 20
sam Paypal 10 0 30
sam Paypal 10 0 40
sam Paypal 250 0 290

(Debit means incoming Amount , credit means outgoing Amount)

Thanks an advance

quote:

Originally posted by bklr


select username, paymentmethod,price,
(select sum(price) from @LP_Payment where paymentid <= p.paymentid AND  userid = 2)
from lp_payment p
inner join LP_Usermanagement u on u.userid= p.userid
where u.userid= 2

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-11 : 11:19:06

what field determines credit & debit ?

esambath
Yak Posting Veteran

89 Posts

Posted - 2009-06-12 : 00:44:50

Dear visakh16,

Thanks for your response

Price field are (credit & debit) amount.

Thanks an advance

quote:
Originally posted by visakh16

what field determines credit & debit ?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-12 : 00:54:53

quote:
Originally posted by esambath

Dear visakh16,

Thanks for your response

Price field are (credit & debit) amount.

Thanks an advance

quote:
Originally posted by visakh16

what field determines credit & debit ?

what visakh asking is do you have a field in your table to identify the price is it CREDIT or DEBIT ? Or you just hard code it ?

KH
[spoiler]Time is always against us[/spoiler]

esambath
Yak Posting Veteran

89 Posts

Posted - 2009-06-12 : 01:11:12

Dear KH,

Sorry for the confussion.

There is no seperate fields for CREDIT and DEBIT amount
we need to identify Credit amount and debit amount from expertid field

if expertid value is 1 the corresponding price is considered as debit amount.

for remaining all the values of expertid corresponding price values are considered as credit amount.

Thanks an advance

quote:
Originally posted by khtan

quote:
Originally posted by esambath

Dear visakh16,

Thanks for your response

Price field are (credit & debit) amount.

Thanks an advance

quote:
Originally posted by visakh16

what field determines credit & debit ?

what visakh asking is do you have a field in your table to identify the price is it CREDIT or DEBIT ? Or you just hard code it ?

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-12 : 01:25:30

just add to the select statement


select debitamount = case when expertid = 1 then price else 0 end,
       creditamount = case when expertid <> 1 then price else 0 end

KH
[spoiler]Time is always against us[/spoiler]

esambath
Yak Posting Veteran

89 Posts

Posted - 2009-06-12 : 05:25:08

Dear khtan,

Thanks for your response.

your query is working.

Thanks

quote:
Originally posted by khtan

just add to the select statement

select debitamount = case when expertid = 1 then price else 0 end,
creditamount = case when expertid <> 1 then price else 0 end

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources