How to combine data from two tables into one line??

[scoobyroo]

I have two tables that I need to get data from. One table has client information and billings for the last 10 years based on the billing attorney. The other table has client information and billings for the last 10 years based on the originating attorney. The same attorney can be in both tables for the same client since the attorney can be both the billing and originating attorney. I need one line per client showing the client information, attorney information and billings for the last 10 years, but they also want to know whether the attorney is
the billing attorney, originating attorney or both. Every statement I come up with duplicates the client since they are in both tables. Any advice would be appreciated.

 

[bborissov]

Could you post some example data and what you want as a result?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[scoobyroo]

Sample Data:
Client Name Atty Type 2008 billing
SKYFIRE LABS BA 7,710.16
SKYFIRE LABS OA 7,710.16
VENKAT KONDA OA 5,000.00
CRYSTAL BALL OA 5,200.00
CRYSTAL BALL BA 5,200.00
ALAN KUHN BA 6,130.00
ALAN KUHN OA 6,130.00
FREZWEB, INC. OA 220.00
FREZWEB, INC. BA 220.00


All this data is for one attorney. I need to show one client line with the 2008 billing information and whether the atty type is ba, oa or both. For Skyfire Labs, the line would show Skyfire Labs, 7,710.16, both (since there is a line for BA and OA under atty type). For Venkat Konda, the line would show Venkat Konda, 5000.00, OA.

 

[bborissov]

DECLARE @Test TABLE ([Client Name] varchar(200), [Atty Type] char(2),[2008 billing] numeric(15,2))
INSERT INTO @Test VALUES ('SKYFIRE LABS','BA',7710.16)
INSERT INTO @Test VALUES ('SKYFIRE LABS','OA',7710.16)
INSERT INTO @Test VALUES ('VENKAT KONDA','OA',5000.00)
INSERT INTO @Test VALUES ('CRYSTAL BALL','OA',5200.00)
INSERT INTO @Test VALUES ('CRYSTAL BALL','BA',5200.00)
INSERT INTO @Test VALUES ('ALAN KUHN','BA',6130.00)
INSERT INTO @Test VALUES ('ALAN KUHN','OA',6130.00)
INSERT INTO @Test VALUES ('FREZWEB, INC.','OA',220.00)
INSERT INTO @Test VALUES ('FREZWEB, INC.','BA',220.00)


SELECT COALESCE(Tbl1.[Client Name],  
                Tbl2.[Client Name])  AS [Client Name],
       COALESCE(Tbl1.[Atty Type],'')+  
       COALESCE(' '+Tbl2.[Atty Type],'') AS [Atty Type],
       COALESCE(Tbl1.[2008 billing],
                Tbl2.[2008 billing]) AS [2008 billing]
FROM (SELECT * FROM @Test
             WHERE [Atty Type] = 'OA') Tbl1
FULL JOIN (SELECT * FROM @Test
             WHERE [Atty Type] = 'BA') Tbl2
ON Tbl1.[Client Name] = Tbl2.[Client Name]

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP