get data from different row - 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 data from different row

Author

Topic

mcotten
Starting Member

1 Post

Posted - 2010-03-15 : 15:48:44

I have a table called Billing_Detail that contains a couple fields that I need to use.

Service Date 1 Sub item
01/15/2010 0
02/03/2010 2
02/03/2010 3

If the sub item is 0 it was a charge and if it is anything else it is a payment or adjustment to the charge. What I need to do is when it reads the row with sub item 2 I need to print the service date 1 from the row with sub item 0.

ray-SQL
Starting Member

18 Posts

Posted - 2010-03-15 : 16:08:50

What you need is a self-join


SELECT Payment.*, Charge.Service_Date_1
FROM Billing_Detail as Charge
INNER JOIN Billing_Detail as Payment
ON Charge.CustomerID = Payment.CustomerID --whatever key you have that identify this account
WHERE Payment.Sub_Item=2 --find only those equal to 2
AND Charge.Sub_Item=0 --charge are those with 0

Your question mentioned only read "row with sub item 2" but if you need it for both 2 & 3, just change

WHERE Payment.Sub_Item=2

WHERE Payment.Sub_Item in (2,3)

Ray Dai

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-16 : 00:13:20

what field do you have in your table to decide on group inside which you want to do the above?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources