get 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

get select query

Author

Topic

esambath
Yak Posting Veteran

89 Posts

Posted - 2009-11-09 : 01:58:52

Hi,

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
)

------------------------------------
sample data
------------------------------------
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)

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

------------------------------------
sample data
------------------------------------
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')

---------------------------------
Explanation
---------------------------------

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

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
--------------------------------------------
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

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

if you need more clarification i will explain you

kindly help this one

thanks an advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-11-09 : 11:12:26

i think what you need is a set of case ..when expressions to deal with your different conditions

esambath
Yak Posting Veteran

89 Posts

Posted - 2009-11-10 : 07:26:08

quote:
Originally posted by visakh16

i think what you need is a set of case ..when expressions to deal with your different conditions

Dear visakh16,

yes you absolutely correct. i have try the query but cannot find extract output kindly help this one

Thanks an advance

esambath
Yak Posting Veteran

89 Posts

Posted - 2009-11-11 : 08:46:01

quote:
Originally posted by esambath

quote:
Originally posted by visakh16

i think what you need is a set of case ..when expressions to deal with your different conditions

Dear visakh16,

yes you absolutely correct. i have try the query but cannot find extract output kindly help this one

Thanks an advance

Dear all,

Kindly help this concern

please

Thanks an advance

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources