Solved

SQL Join query

Posted on 2014-03-12
2
250 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

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…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how the fundamental information of how to create a table.

829 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