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-10-27 : 06:34:54

Hi Experts,

I have two table LP_Usermanagement and LP_Payment

CREATE TABLE [LP_Usermanagement] (
[UserId] [bigint] NULL ,
[NickName] [varchar] (50) NULL ,
[Email] [varchar] (50) NULL ,
[UserType] [bigint] NULL
)
insert into LP_Usermanagement(UserId,NickName,Email,UserType)values(1,'admin','admin@gt.com',0)
insert into LP_Usermanagement(UserId,NickName,Email,UserType)values(2,'sam','sambath.ekambaram@gt.com',1)
insert into LP_Usermanagement(UserId,NickName,Email,UserType)values(4,'kotti','kottiprasad.ramakrishnan@gt.com',2)
insert into LP_Usermanagement(UserId,NickName,Email,UserType)values(107,'subram','subramanian.mahalingam@gt.com',2)
insert into LP_Usermanagement(UserId,NickName,Email,UserType)values(141,'01user','01user@yahoo.com',1)

select * from LP_Usermanagement

CREATE TABLE [LP_Payment] (
[PaymentId] [bigint] NULL ,
[UserId] [bigint] NULL ,
[ExpertId] [bigint] NULL ,
[PaymentMethod] [varchar] (50) NULL ,
[Price] [float] NULL ,
[Reference] [varchar] (50) NULL
)

insert into LP_Payment(PaymentId,UserId,ExpertId,PaymentMethod,Price,Reference)values(262,141,1,'paypal',1.0,'SAAdvertisement')
insert into LP_Payment(PaymentId,UserId,ExpertId,PaymentMethod,Price,Reference)values(266,141,1,'paypal',1.0,'SAAdvertisement')
insert into LP_Payment(PaymentId,UserId,ExpertId,PaymentMethod,Price,Reference)values(274,141,1,'paypal',1.0,'SAMembership')
insert into LP_Payment(PaymentId,UserId,ExpertId,PaymentMethod,Price,Reference)values(279,141,107,'paypal',1.2,'Escrow')
insert into LP_Payment(PaymentId,UserId,ExpertId,PaymentMethod,Price,Reference)values(364,141,107,'paypal',3.0,'Expert')
insert into LP_Payment(PaymentId,UserId,ExpertId,PaymentMethod,Price,Reference)values(367,141,107,'paypal',3.0,'Expert')
insert into LP_Payment(PaymentId,UserId,ExpertId,PaymentMethod,Price,Reference)values(370,141,1,'paypal',63.0,'Membership')
insert into LP_Payment(PaymentId,UserId,ExpertId,PaymentMethod,Price,Reference)values(373,141,1,'paypal',1.0,'Advertisement')
insert into LP_Payment(PaymentId,UserId,ExpertId,PaymentMethod,Price,Reference)values(377,141,107,'paypal',1.0,'Expert')
insert into LP_Payment(PaymentId,UserId,ExpertId,PaymentMethod,Price,Reference)values(391,4,107,'paypal',1.0,'Expert')
insert into LP_Payment(PaymentId,UserId,ExpertId,PaymentMethod,Price,Reference)values(265,141,1,'Paypal',150,'Admin')
insert into LP_Payment(PaymentId,UserId,ExpertId,PaymentMethod,Price,Reference)values(266,141,107,'Paypal',1,'Expert' )
insert into LP_Payment(PaymentId,UserId,ExpertId,PaymentMethod,Price,Reference)values(267,141,1,'Paypal',0.8,'Site Owner')
insert into LP_Payment(PaymentId,UserId,ExpertId,PaymentMethod,Price,Reference)values(268,141,5,'Paypal',0.2,'Affiliate')
insert into LP_Payment(PaymentId,UserId,ExpertId,PaymentMethod,Price,Reference)values(281,141,107,'Paypal',3,'Expert')
insert into LP_Payment(PaymentId,UserId,ExpertId,PaymentMethod,Price,Reference)values(370,141,107,'Paypal',1.2,'Escrow')
insert into LP_Payment(PaymentId,UserId,ExpertId,PaymentMethod,Price,Reference)values(371,141,1,'Paypal',0.6,'EscrowSite Owner')
insert into LP_Payment(PaymentId,UserId,ExpertId,PaymentMethod,Price,Reference)values(372,141,5,'Paypal',0.2,'EscrowAffiliate')
insert into LP_Payment(PaymentId,UserId,ExpertId,PaymentMethod,Price,Reference)values(401,141,107,'Paypal',0,'Negotiate')
insert into LP_Payment(PaymentId,UserId,ExpertId,PaymentMethod,Price,Reference)values(405,2,141,'Paypal',1.2,'Expert')
insert into LP_Payment(PaymentId,UserId,ExpertId,PaymentMethod,Price,Reference)values(406,2,1,'Paypal',0.6,'Site Owner')
insert into LP_Payment(PaymentId,UserId,ExpertId,PaymentMethod,Price,Reference)values(407,2,1,'Paypal',0.2,'Affiliate')
insert into LP_Payment(PaymentId,UserId,ExpertId,PaymentMethod,Price,Reference)values(408,2,141,'Paypal',1.2,'Escrow')

select * from LP_Payment

Condition

---When UserId=141 and ExpertId=1 and Reference=('Admin') means to admin the Balance Amount
---When UserId=141 and ExpertId=[anoter UserId] and Reference=Escrow means to subtract the Balance Amount
---When UserId=[anoter UserId] and ExpertId=141 and Reference=Escrow means to not add & subtract the Balance Amount
---When UserId=141 and ExpertId=[anoter UserId] and Reference=Expert means to subtract the Balance Amount
---When UserId=[anoter UserId] and ExpertId=141 and Reference=Expert means to add the Balance Amount
---When UserId=141 and ExpertId=1 and Reference=('SAAdvertisement','SAMembership') means to subtract the Balance Amount
---When UserId=141 and ExpertId=1 and Reference=('Advertisement','Membership') means to not add & subtract the Balance Amount
---When UserId=141 and ExpertId=1 and Reference=Site Owner means to subtract the Balance Amount
---When UserId=141 and ExpertId=1 and Reference=Affiliate means to subtract the Balance Amount
---When UserId=141 and ExpertId=1 and Reference=('Site Owner','Affiliate','EscrowSite Owner','EscrowAffiliate') means to subtract the Balance Amount
---When UserId=141 and ExpertId=[anoter UserId] and Reference=('Negotiate') means to not add & subtract the Balance Amount

This is my test query

declare @table table
(
ReferenceText nvarchar(255),
Username nvarchar(255),
Expertname nvarchar(255),
creditAmount money,
Debitamount money,
Balance money
)
insert into @table
Select (case when Reference='Expert' then 'User released the fund' else Reference end) as ReferenceText,
(select NickName from LP_Usermanagement where UserId=P.UserId) as Username,
(select NickName from LP_Usermanagement where UserId=P.ExpertId) as Expertname,
creditAmount = case when expertid = 141 then price else 0 end,
Debitamount = case when expertid = 1 then price else 0 end,
0
from lp_payment p where (UserId=141 OR expertid=141)

declare @currentBlance money
set @currentBlance = 0
update @table set @currentBlance = Balance = @currentBlance +
case
when ReferenceText in ('Escrow' , 'Membership','Advertisement') then 0
else creditAmount-Debitamount
end
select * from @table

OUTPUT
--------------------

Reference, User, Expert, CreditAmount, DebitAmount, Balance

SAAdvertisement 01user admin 0 1 -1
SAAdvertisement 01user admin 0 1 -2
SAMembership 01user admin 0 1 -3
Escrow 01user subram 1.2 0 -4.2
Expert 01user subram 0 3 -7.2
Expert 01user subram 0 3 -10.2
Membership 01user admin 0 63 -10.2
Advertisement 01user admin 0 1 -10.2
Expert 01user subram 1 0 -11.2
Admin 01user admin 0 150 138.8
Expert 01user subram 0 1 137.8
Site Owner 01user admin 0 0.8 137
Affiliate 01user admin 0 0 137
Expert 01user subram 0 3 135
Escrow 01user subram 1.2 0 133.8
EscrowSite Owner01user admin 0 0.6 133.2
EscrowAffiliate 01user admin 0 0 133.2
Negotiate 01user subram 0 0 133.2
Expert sam 01user 1.2 0 134.4
Site Owner sam admin 0 0.6 133.8
Affiliate sam admin 0 0.2 133.6
Escrow sam 01user 1.2 0 132.4

if you need more clarification i will explain you

kindly help this one

thanks an advance

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-10-27 : 06:36:10

what is your question ?

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

esambath
Yak Posting Veteran

89 Posts

Posted - 2009-10-27 : 08:45:21

quote:
Originally posted by khtan

what is your question ?

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

Dear khtan,

Thanks for your replay

i have provide the sample datas in (LP_Usermanagement and LP_Payment)

This is my test query

-------------------------------------------
declare @table table
(
ReferenceText nvarchar(255),
Username nvarchar(255),
Expertname nvarchar(255),
creditAmount money,
Debitamount money,
Balance money
)
insert into @table
Select (case when Reference='Expert' then 'User released the fund' else Reference end) as ReferenceText,
(select NickName from LP_Usermanagement where UserId=P.UserId) as Username,
(select NickName from LP_Usermanagement where UserId=P.ExpertId) as Expertname,
creditAmount = case when expertid = 141 then price else 0 end,
Debitamount = case when expertid = 1 then price else 0 end,
0
from lp_payment p where (UserId=141 OR expertid=141)

declare @currentBlance money
set @currentBlance = 0
update @table set @currentBlance = Balance = @currentBlance +
case
when ReferenceText in ('Escrow' , 'Membership','Advertisement') then 0
else creditAmount-Debitamount
end
select * from @table

--------------------------

I am calculating the details of the UserId 141 in payment table

I need to split the Price into Three fields as DebitAmount ,CreditAmount and Balance

When the userId =141 and ExpertId = 1 and Reference='Admin' means i need to show Debitamount as
Price and Creditamount as 0 and Balance as Price .
when the UserId=141 and ExpertId=(Any Id) and Reference ='Escrow' means I need to show Debitamount as
0 and Creditamount as Price and Balance as (CurrentBalance - Price)
when the userId=(Any Id) and ExpertId=141 and Reference ='Escrow' means I need to show Debitamount as 0 and Creditamount as price and Balance as maintain same Balance
When UserId=141 and ExpertId=[Any Id] and Reference='Expert' means i need to show Debitamount as Price and Creditamount as 0 and Balance as (Debitamount - Price)
When UserId=[Any Id] and ExpertId=141 and Reference='Expert' means i need to show Debitamount as 0 and Creditamount as Price and Balance as (Creditamount + Price)
When UserId=141 and ExpertId=1 and Reference=('SAAdvertisement','SAMembership') means i need to show Debitamount as Price and Creditamount as 0 and Balance as (Debitamount - Price)
When UserId=141 and ExpertId=1 and Reference=('Advertisement','Membership') means i need to show Debitamount as Price and Creditamount as 0 and Balance as maintain same Balance
When UserId=141 and ExpertId=1 and Reference='Site Owner' means i need to show Debitamount as Price and Creditamount as 0 and Balance as (Debitamount - Price)
When UserId=141 and ExpertId=1 and Reference='Affiliate' means i need to show Debitamount as Price and Creditamount as 0 and Balance as (Debitamount - Price)
When UserId=141 and ExpertId=1 and Reference=('Site Owner','Affiliate','EscrowSite Owner','EscrowAffiliate') means i need to show Debitamount as Price and Creditamount as 0 and Balance as (Debitamount - Price)
When UserId=141 and ExpertId=[anoter UserId] and Reference=('Negotiate') means i need to show Debitamount as Price and Creditamount as 0 and Balance as maintain same Balance

if you need more clarification i will explain

please help this one

Thanks an Advance

Hi

Anyone please help this concern if you need more clarification i will explain

please help this one

Thanks an Advance

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-10-28 : 07:36:49

Hi esambath

Yes we need more clarification for your question

-------------------------
R...

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-10-28 : 07:57:28

quote:
Originally posted by esambath
I am calculating the details of the UserId 141 in payment table

I need to split the Price into Three fields as DebitAmount ,CreditAmount and Balance

Ok, can you show us EXPECTED output?

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

esambath
Yak Posting Veteran

89 Posts

Posted - 2009-10-29 : 00:54:09

quote:
Originally posted by DonAtWork

quote:
Originally posted by esambath
I am calculating the details of the UserId 141 in payment table

I need to split the Price into Three fields as DebitAmount ,CreditAmount and Balance

Ok, can you show us EXPECTED output?

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Hi

Thanks for your replay

This my Expected output

OUTPUT
--------------------

Reference, User, Expert, CreditAmount, DebitAmount, Balance

SAAdvertisement 01user admin 0 1 -1
SAAdvertisement 01user admin 0 1 -2
SAMembership 01user admin 0 1 -3
Escrow 01user subram 1.2 0 -4.2
Expert 01user subram 0 3 -7.2
Expert 01user subram 0 3 -10.2
Membership 01user admin 0 63 -10.2
Advertisement 01user admin 0 1 -10.2
Expert 01user subram 1 0 -11.2
Admin 01user admin 0 150 138.8
Expert 01user subram 0 1 137.8
Site Owner 01user admin 0 0.8 137
Affiliate 01user admin 0 0 137
Expert 01user subram 0 3 135
Escrow 01user subram 1.2 0 133.8
EscrowSite Owner01user admin 0 0.6 133.2
EscrowAffiliate 01user admin 0 0 133.2
Negotiate 01user subram 0 0 133.2
Expert sam 01user 1.2 0 134.4
Site Owner sam admin 0 0.6 133.8
Affiliate sam admin 0 0.2 133.6
Escrow sam 01user 1.2 0 132.4

thanks an advance

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources