SQL View Problem

I have the following two views
SELECT     TOP 100 PERCENT dbo.ArInvoice.Customer, dbo.ArCustomer.Name, dbo.ArInvoice.Invoice, dbo.ArInvoice.DocumentType, dbo.ArInvoice.InvoiceDate, 
                      SUM(dbo.ArTrnDetail.NetSalesValue) AS NetSales, SUM(dbo.ArTrnDetail.TaxValue) AS NetTax
FROM         dbo.ArInvoice INNER JOIN
                      dbo.ArCustomer ON dbo.ArInvoice.Customer = dbo.ArCustomer.Customer INNER JOIN
                      dbo.ArTrnDetail ON dbo.ArInvoice.Invoice = dbo.ArTrnDetail.Invoice
GROUP BY dbo.ArInvoice.Customer, dbo.ArCustomer.Name, dbo.ArInvoice.Invoice, dbo.ArInvoice.DocumentType, dbo.ArInvoice.InvoiceDate
HAVING      (dbo.ArInvoice.InvoiceDate > CONVERT(DATETIME, '2011-03-01 00:00:00', 102))
ORDER BY dbo.ArInvoice.Invoice

Open in new window

and
SELECT     TOP 100 PERCENT dbo.ArCustomer.Name, dbo.ArCustomer.Customer, SUM(dbo.ArPayHistory.PayValue) AS PayValue, dbo.ArPayHistory.PayDate, 
                      dbo.ArPayHistory.ChequeReference
FROM         dbo.ArCustomer INNER JOIN
                      dbo.ArPayHistory ON dbo.ArCustomer.Customer = dbo.ArPayHistory.Customer
GROUP BY dbo.ArCustomer.Name, dbo.ArCustomer.Customer, dbo.ArPayHistory.PayDate, dbo.ArPayHistory.ChequeReference
HAVING      (dbo.ArPayHistory.PayDate >= CONVERT(DATETIME, '2011-03-01 00:00:00', 102))

Open in new window

The first one returns the Invoices and Credits the second any Payments.

Is it possible to create one view which shows all invoices / credits and payments ?
LVL 1
RickCooperAsked:
Who is Participating?
 
deightonprogCommented:
make it so they have the same list of selected fields, in the same order, renaming the fields if necessary - where a field is not applicable to a query, replace it with
 'N/A' as YourField

then UNION the two queries

0
 
mmr159Commented:
Without knowing the schema, I can't say for sure, but it looks possible.  One potential issue I see is what if there are multiple payments for a single invoice?  Or do you want to SUM them?  If you want to sum all credits and payments for a given invoice, then it looks doable.

Here is a new version of your code that enhances readability.  Please note that I may have made an error:


SELECT TOP 100 PERCENT
	inv.Customer,cust.Name,inv.Invoice,inv.DocumentType,
	inv.InvoiceDate,SUM(trn.NetSalesValue) AS NetSales,
	SUM(trn.TaxValue) AS NetTax
FROM dbo.ArInvoice AS inv INNER JOIN dbo.ArCustomer AS cust
	ON inv.Customer = cust.Customer INNER JOIN dbo.ArTrnDetail AS trn
		ON inv.Invoice = trn.Invoice
GROUP BY
	inv.Customer
	cust.Name,
	inv.Invoice,
	inv.DocumentType,
	inv.InvoiceDate
HAVING inv.InvoiceDate > CONVERT(DATETIME,'2011-03-01 00:00:00',102)
ORDER BY inv.Invoice

SELECT TOP 100 PERCENT
	cust.Name,cust.Customer,SUM(dbo.ArPayHistory.PayValue) AS PayValue,
	pay.PayDate,pay.ChequeReference
FROM dbo.ArCustomer AS cust INNER JOIN dbo.ArPayHistory AS pay
	ON cust.Customer = pay.Customer
GROUP BY
	cust.Name,
	cust.Customer,
	pay.PayDate,
	pay.ChequeReference
HAVING pay.PayDate >= CONVERT(DATETIME,'2011-03-01 00:00:00',102)

Open in new window

0
 
RickCooperAuthor Commented:
mmr159,

 I need to sum the invoice credits and payments by each invoice, even if there multiple payments to a single invoice.

I also need to know if its an invoice / credit  based on the document type I or C
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
mmr159Commented:
Then yes, it looks like you could create a query that returns summed credits, payments for a given invoice.  Again, I don't know the schema so I can tell you how to write the query.
0
 
gdupadhyayCommented:
Hi,
You need outer join with table dbo.ArPayHistory.

Your first view add outer join with dbo.ArPayHistory
on ON dbo.ArCustomer.Customer = dbo.ArPayHistory.Customer

Select all fields from all table as per your requirement.
I am sure it will work for you.
0
 
sachitjainCommented:
You need to common unique attribute that could link payment against invoice for a customer. That would the column that would form join between the 2 then you could do order by customer name to show all payments against each of the invoice generated for each of the customer.
0
 
RickCooperAuthor Commented:
I have used a completely different method to show the output on a report.

I have therefore split the points
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.