Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 258
  • Last Modified:

SQL Join query

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
Graham_Highlander
Asked:
Graham_Highlander
1 Solution
 
Steve WalesSenior Database AdministratorCommented:
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
 
Graham_HighlanderAuthor Commented:
steve - fantastic, this is exactly what I needed. I will find a way to resolve the nulls

regards, graham
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now