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-08-04 : 12:04:33

Hi all,

I have two table

LP_Usermanagement

UserId
UserName

LP_Payment

PaymentId
UserId
ExpertId
Price

CREATE TABLE LP_UserManagement(UserId bigint NOT NULL,NickName varchar(128) NOT NULL)

insert into LP_UserManagement(UserId,NickName)values(1,'admin')
insert into LP_UserManagement(UserId,NickName)values(2,'sam')
insert into LP_UserManagement(UserId,NickName)values(3,'bin')
insert into LP_UserManagement(UserId,NickName)values(4,'sean')

CREATE TABLE LP_Payment(PaymentId bigint NOT NULL,UserId bigint NOT NULL,ExpertId bigint NOT NULL,Price float NOT NULL)

insert into LP_Payment(PaymentId,UserId,ExpertId,Price)values(1,2,1,10)
insert into LP_Payment(PaymentId,UserId,ExpertId,Price)values(2,2,1,10)
insert into LP_Payment(PaymentId,UserId,ExpertId,Price)values(3,2,1,10)
insert into LP_Payment(PaymentId,UserId,ExpertId,Price)values(4,2,1,10)
insert into LP_Payment(PaymentId,UserId,ExpertId,Price)values(5,2,1,250)
insert into LP_Payment(PaymentId,UserId,ExpertId,Price)values(6,2,4,4)
insert into LP_Payment(PaymentId,UserId,ExpertId,Price)values(7,2,4,10)

This is my test query

Select u.NickName as Username,
creditAmount = case when expertid = 1 then price else 0 end,
Debitamount = case when expertid <> 1 then price else 0 end,
(select sum(price) from LP_Payment where paymentid <= p.paymentid and userid = 2) as Balance
from lp_payment p inner join LP_Usermanagement u on u.userid= p.userid where u.userid= 2

In LP_Payment table when expertid=1 the amount was added ,other than 1 the amount was deducted.

For example we can consider the first row of lp_payment table
here the userid 2 has credited amount of 10 to the Expertid 1 so we get creditamount as 10 ,debitamount as 0 and Balance as 10
in the expectedoutput table.
For second row in lp_payment table
here again the userid 2 has credited the amount of 10 to the expertid 1 so we get credit amount of 10 ,debit amount of 0 and
balance as 20 (first row balance + 2nd row credit amount)
Like this we have to calculate

But if the expert id is other than 1 means we need to deduct amount from the balance.

If you need better explantion ,please reply me i will explain.

Expected output

Username,creditAmount,Debitamount, Balance
sam 10 0 10
sam 10 0 20
sam 10 0 30
sam 10 0 40
sam 250 0 290
sam 0 4 286
sam 0 10 276

thanks an advance

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-04 : 13:16:42

Here is one way:


Select u.NickName as Username,
       creditAmount = case when expertid = 1 then price else 0 end,
       Debitamount = case when expertid <> 1 then price else 0 end,
       oa.balance
from   lp_payment p 
inner join LP_Usermanagement u 
       on u.userid= p.userid 

cross apply (
       select sum (price * case when expertid = 1 then 1 else -1 end) as balance
       from   LP_Payment
       where  paymentid <= p.paymentid
       ) oa

where  u.userid= 2

EDIT:
output:
Username          creditAmount           Debitamount            balance
---------------------------------------- ---------------------- ----------------------
sam               10                     0                      10
sam               10                     0                      20
sam               10                     0                      30
sam               10                     0                      40
sam               250                    0                      290
sam               0                      4                      286
sam               0                      10                     276

Be One with the Optimizer
TG

esambath
Yak Posting Veteran

89 Posts

Posted - 2009-08-04 : 13:40:14

Hi TG,

i have execute your query i got this below error.

Msg 170, Level 15, State 1, Line 9
Line 9: Incorrect syntax near 'apply'.
Msg 170, Level 15, State 1, Line 13
Line 13: Incorrect syntax near 'oa'.

Thanks an advance

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-04 : 14:33:27

Are you using sql server 2000 (or earlier) ?

Be One with the Optimizer
TG

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-04 : 14:40:02

If you are on 2005 or greater your database compatibility level may be set to 80. If so you can change to 90 or appropriate. If you are on 2000 or earlier then this should work (it is close to what you had originally)


Select u.NickName as Username,
       creditAmount = case when expertid = 1 then price else 0 end,
       Debitamount = case when expertid <> 1 then price else 0 end,
       balance = (select sum (price * case when expertid = 1 then 1 else -1 end) as balance
                  from   LP_Payment
                  where  paymentid <= p.paymentid)
from   lp_payment p 
inner join LP_Usermanagement u 
       on u.userid= p.userid 

where  u.userid= 2

Be One with the Optimizer
TG

esambath
Yak Posting Veteran

89 Posts

Posted - 2009-08-05 : 01:32:58

Hi TG,

Thanks for you message.

I am using sql server 2005 and i am not getting the correct result.Please find the execution result below.

sam 10 0 2
sam 10 0 12
sam 10 0 22
sam 10 0 32
sam 250 0 282
sam 0 4 362
sam 0 10 352

Please help me to get the correct result.

esambath
Yak Posting Veteran

89 Posts

Posted - 2009-08-05 : 07:44:04

Hi Friends

Please anyone help me regarding the query

Your help will be much appreciated

Thanks in Advance

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-08-05 : 08:21:30

What query are you using to get that result? What set of data EXACTLY are you using to get that result? Is it what you posted earlier, or your actual production environment?

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-08-05 : 08:45:38

Hi DonAtWork,

Thanks for your replay

I am using sql server 2005(SQL Server 9.0.1399) and i am not getting the correct result.Please find the execution result below.

Select u.NickName as Username,
creditAmount = case when expertid = 1 then price else 0 end,
Debitamount = case when expertid <> 1 then price else 0 end,
balance = (select sum (price * case when expertid = 1 then 1 else -1 end) as balance
from LP_Payment
where paymentid <= p.paymentid)
from lp_payment p
inner join LP_Usermanagement u
on u.userid= p.userid

where u.userid= 2

Username creditAmount Debitamount balance
---------------------------------------- ---------------------- ----------------------
sam 10 0 2
sam 10 0 12
sam 10 0 22
sam 10 0 32
sam 250 0 282
sam 0 4 362
sam 0 10 352

Expected result

Username creditAmount Debitamount balance
---------------------------------------- ---------------------- ----------------------
sam 10 0 10
sam 10 0 20
sam 10 0 30
sam 10 0 40
sam 250 0 290
sam 0 4 286
sam 0 10 276

If you need better explantion ,please reply me i will explain.

Thanks an Advance

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-05 : 10:15:30

quote:
Msg 170, Level 15, State 1, Line 9
Line 9: Incorrect syntax near 'apply'.
Msg 170, Level 15, State 1, Line 13
Line 13: Incorrect syntax near 'oa'.

what does this return (run it in the same database that generated this error

select compatibility_level from master.sys.databases where database_id = db_id()

I got my output (which matches your expected output) by running the statement I posted against the sameple data you posted. I got the same (correct) output when I posted the statement you have (imediately above). So I have no idea why you are not getting the same results as me???

Be One with the Optimizer
TG

esambath
Yak Posting Veteran

89 Posts

Posted - 2009-08-05 : 12:30:19

Hi TG,

Thanks for your response.

When i used the earlier sample data ,i am getting the correct result.but when i changed the sample data like given below, i am not getting the correct answer .

Sorry for confusing .

CREATE TABLE LP_UserManagement(UserId bigint NOT NULL,NickName varchar(128) NOT NULL)

insert into LP_UserManagement(UserId,NickName)values(1,'admin')
insert into LP_UserManagement(UserId,NickName)values(2,'sam')
insert into LP_UserManagement(UserId,NickName)values(3,'bin')
insert into LP_UserManagement(UserId,NickName)values(4,'sean')
insert into LP_UserManagement(UserId,NickName)values(9,'A')
insert into LP_UserManagement(UserId,NickName)values(70,'B')
insert into LP_UserManagement(UserId,NickName)values(114,'C')

CREATE TABLE LP_Payment(PaymentId bigint NOT NULL,UserId bigint NOT NULL,ExpertId bigint NOT NULL,Price float NOT NULL)

insert into LP_Payment(PaymentId,UserId,ExpertId,Price)values(1,4,6,4)
insert into LP_Payment(PaymentId,UserId,ExpertId,Price)values(2,9,14,4)
insert into LP_Payment(PaymentId,UserId,ExpertId,Price)values(3,2,1,10)
insert into LP_Payment(PaymentId,UserId,ExpertId,Price)values(4,2,1,10)
insert into LP_Payment(PaymentId,UserId,ExpertId,Price)values(5,2,1,10)
insert into LP_Payment(PaymentId,UserId,ExpertId,Price)values(6,2,1,10)
insert into LP_Payment(PaymentId,UserId,ExpertId,Price)values(7,2,1,250)
insert into LP_Payment(PaymentId,UserId,ExpertId,Price)values(8,70,1,21)
insert into LP_Payment(PaymentId,UserId,ExpertId,Price)values(9,114,1,63)
insert into LP_Payment(PaymentId,UserId,ExpertId,Price)values(10,2,114,4)
insert into LP_Payment(PaymentId,UserId,ExpertId,Price)values(11,2,114,10)
insert into LP_Payment(PaymentId,UserId,ExpertId,Price)values(12,2,114,16)
insert into LP_Payment(PaymentId,UserId,ExpertId,Price)values(13,2,114,16)
insert into LP_Payment(PaymentId,UserId,ExpertId,Price)values(14,2,114,2)
insert into LP_Payment(PaymentId,UserId,ExpertId,Price)values(15,2,114,2)
insert into LP_Payment(PaymentId,UserId,ExpertId,Price)values(16,2,114,2)

I used the below query

Select u.NickName as Username,
creditAmount = case when expertid = 1 then price else 0 end,
Debitamount = case when expertid <> 1 then price else 0 end,
balance = (select sum (price * case when expertid = 1 then 1 else -1 end) as balance
from LP_Payment
where paymentid <= p.paymentid)
from LP_Payment p
inner join LP_UserManagement u
on u.userid= p.userid
where p.userid=2

Thanks

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-05 : 12:38:27

OK, the sub-query is missing the correlation to userid. Add that to the where clause.

btw, did you run that compatibility_level query I posted above? Still don't know why you got an error with the CROSS APPLY statement.

Be One with the Optimizer
TG

esambath
Yak Posting Veteran

89 Posts

Posted - 2009-08-06 : 00:55:30

Hi TG,

Thanks for your message.your query is working

Select u.NickName as Username,creditAmount = case when expertid = 1 then price else 0 end,Debitamount = case when expertid <> 1 then price else 0 end,balance = (select sum (price * case when expertid = 1 then 1 else -1 end) as balance 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 p.userid=2

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources