Solved

SQL Join query

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

911 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now