Solved

SQL Join query

Posted on 2014-03-12
2
249 Views
Last Modified: 2014-03-12
hello

can anyone help solve this t-sql query I have. I have 1 table with invoices for costs and another with invoices for revenue. I have a project code which is common to both tables. you might have different number of cost invoices to revenue

What I need is to show the costs invoices and revenue invoices in the same view, but not repeat any thing. I know I could use a GROUP BY and SUM, but the users need to see the invoice numbers.

See attached t-sql script to create a table to highlight my issue.

SQL resultt-sql-join-problem.sql
0
Comment
Question by:Graham_Highlander
2 Comments
 
LVL 22

Accepted Solution

by:
Steve Wales earned 500 total points
ID: 39923819
First, thanks for providing the code to create the tables and test data.  Awesome!

How about something like this ?
SELECT     isnull(ic.Project,ir.Project) as ProjectCode , 
           ic.InvoiceNumber as CostInvoiceNumber,  
           ic.InvoiceAmount as CostAmount,  
           ir.InvoiceNumber AS RevInvoiceNumber , 
           ir.InvoiceAmount AS RevAmount
FROM       (select InvoiceNumber, Project, row_number() over(partition by Project order by InvoiceNumber) as InvItem, InvoiceAmount from Invoices_Cost) as ic
full outer join (select InvoiceNumber, Project, row_number() over(partition by Project order by InvoiceNumber) as InvItem, InvoiceAmount from Invoices_Rev) as ir
ON ic.Project = ir.Project and ic.InvItem = ir.InvItem

Open in new window


Produces:

ProjectCode CostInvoiceNumber CostAmount            RevInvoiceNumber RevAmount
----------- ----------------- --------------------- ---------------- ---------------------
aaa123      800               100.00                900              200.00
abc123      801               100.00                901              200.00
abc123      802               20.00                 NULL             NULL
ccc123      803               90.00                 902              20.00
ccc123      NULL              NULL                  903              200.00

(5 row(s) affected)

Open in new window


You may need some handling in there for the NULL's in the numerics.  I tried wrapping them in ISNULL functions, spitting out spaces in place of nulls but since they were numbers, they appeared as 0 instead.

Anyway, that should get you started.
0
 

Author Closing Comment

by:Graham_Highlander
ID: 39923850
steve - fantastic, this is exactly what I needed. I will find a way to resolve the nulls

regards, graham
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question