fetch correct data - 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

fetch correct data

Author

Topic

esambath
Yak Posting Veteran

89 Posts

Posted - 2009-10-16 : 05:59:42

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(2,'sam','sambath.ekambaram@gant.com',1)
insert into LP_Usermanagement(UserId,NickName,Email,UserType)values(4,kotti,kottiprasad.ramakrishnan@gants.com,2)
insert into LP_Usermanagement(UserId,NickName,Email,UserType)values(107,subram,subramanian.mahalingam@gants.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
)

insert into LP_Payment(PaymentId,UserId,ExpertId,PaymentMethod,Price,Reference)values(262,107,1,'paypal',1.0,'SAAdvertisement')
insert into LP_Payment(PaymentId,UserId,ExpertId,PaymentMethod,Price,Reference)values(266,107,1,'paypal',1.0,'SAAdvertisement')
insert into LP_Payment(PaymentId,UserId,ExpertId,PaymentMethod,Price,Reference)values(274,107,1,'paypal',1.0,'SAAdvertisement')
insert into LP_Payment(PaymentId,UserId,ExpertId,PaymentMethod,Price,Reference)values(278,2,107,'paypal',2.3,'Escrow')
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(281,2,107,'paypal',3.0,'Expert')
insert into LP_Payment(PaymentId,UserId,ExpertId,PaymentMethod,Price,Reference)values(292,2,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,107,1,'paypal',63.0,'Membership')
insert into LP_Payment(PaymentId,UserId,ExpertId,PaymentMethod,Price,Reference)values(373,107,1,'paypal',1.0,'Advertisement')
insert into LP_Payment(PaymentId,UserId,ExpertId,PaymentMethod,Price,Reference)values(376,107,1,'paypal',12.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')

condition

when the ReferenceText= 'SAAdvertisement' means subract to be from balance amount

when the ReferenceText= 'Escrow' means not to be add & subtract balance amount

when the (ReferenceText= 'Membership' OR ReferenceText='Advertisement
') means not to add & subtract balance amount

my test query

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 = 107 then price else 0 end,
Debitamount = case when expertid = 1 then price else 0 end,
isnull(((select sum(price) from LP_Payment where paymentid <= p.paymentid
and Reference='Expert' and Expertid = 107)-isnull((select (price) from LP_Payment
where PaymentId=p.PaymentId and Reference='SAAdvertisement' and UserId = 107),0)),0) as
Balance from lp_payment p where (P.expertid= 107 or P.UserId=107) and
Reference!='Negotiate' order by paymentid asc

Expected output

ReferenceText ,Username, Expertname,creditAmount,Debitamount,Balance

SAAdvertisement subram NULL 0 1 -1
SAAdvertisement subram NULL 0 1 -2
SAAdvertisement subram NULL 0 1 -3
Escrow sam subram 2.4 0 -3
Escrow 01user subram 1.2 0 -3
User released the fund sam subram 3 0 0
Escrow sam subram 1.2 0 0
User released the fund 01user subram 3 0 3
User released the fund 01user subram 3 0 6
Membership subram NULL 0 63 6
Advertisement subram NULL 0 1 6
Advertisement subram NULL 0 12 6
User released the fund 01user subram 1 0 7
User released the fund kotti subram 1 0 8

kindly help this one

If you need more explanations , i will explain

Thanks an advance

weipublic
Starting Member

19 Posts

Posted - 2009-10-16 : 13:44:53

Should the balance be the accumulated value?
And the line balance should be the creditAmount-Debitamount?

If so, following should work

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 = 107 then price else 0 end,
        Debitamount = case when expertid = 1 then price else 0 end,
        0
from    lp_payment p 

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources