Database

SELECT     InvoicePayment.DateCreated AS InvoicePaymentDate, 
InvoicePayment.InvoiceID, 
InvoicePayment.Amount, 
Customer.Name, 
Invoice.CustomerID, 
Invoice.InvoiceNumber
FROM         (Select * from CustomerAccountPayment where CustomerID = @CustomerID and EntryDate <=@EndDate) as CustomerAccountPayment INNER JOIN
                      CustomerAccountPaymentDetail ON CustomerAccountPayment.ID = CustomerAccountPaymentDetail.CustomerAccountPaymentID  RIGHT OUTER JOIN
                      InvoicePayment INNER JOIN
                      Invoice ON InvoicePayment.InvoiceID = Invoice.ID INNER JOIN
                      Customer ON Invoice.CustomerID = Customer.ID INNER JOIN
                      PaymentMethod ON InvoicePayment.PaymentMethodID = PaymentMethod.ID ON CustomerAccountPaymentDetail.InvoicePaymentID = InvoicePayment.ID
WHERE (Customer.ID = @CustomerID) 
AND (CustomerAccountPaymentDetail.ID IS NULL) 
AND (Invoice.Void = 'False') 
AND (PaymentMethod.IsCredit = 'True') 
and (InvoicePayment.DateCreated <= @EndDate)
union
SELECT	InvoicePayment.DateCreated AS InvoicePaymentDate, 
	InvoicePayment.InvoiceID, 
	InvoicePayment.Amount - SUM(case CustomerAccountPayment.Void 
		when 'True' then 0 
		when 'False' then ROUND(CustomerAccountPaymentDetail.Amount, 2)
	end) AS Amount, 
	Customer.Name, 
	Invoice.CustomerID, 
	Invoice.InvoiceNumber
FROM InvoicePayment INNER JOIN
                      Invoice ON InvoicePayment.InvoiceID = Invoice.ID INNER JOIN
                      Customer ON Invoice.CustomerID = Customer.ID INNER JOIN
                      CustomerAccountPaymentDetail ON InvoicePayment.ID = CustomerAccountPaymentDetail.InvoicePaymentID INNER JOIN
                      PaymentMethod ON InvoicePayment.PaymentMethodID = PaymentMethod.ID INNER JOIN
                      (Select * from CustomerAccountPayment where CustomerID = @CustomerID and EntryDate <=@EndDate) as CustomerAccountPayment ON CustomerAccountPaymentDetail.CustomerAccountPaymentID = CustomerAccountPayment.ID
WHERE (Invoice.Void = 'False') 
	AND (PaymentMethod.IsCredit = 'True') 
	and Invoice.CustomerID = @CustomerID
	and InvoicePayment.DateCreated <= @EndDate
GROUP BY InvoicePayment.DateCreated, InvoicePayment.InvoiceID, Invoice.InvoiceNumber, InvoicePayment.Amount, InvoicePayment.DateCreated, Customer.Name, 
                      Invoice.CustomerID
HAVING      (SUM(case CustomerAccountPayment.Void 
		when 'True' then 0 
		when 'False' then ROUND(CustomerAccountPaymentDetail.Amount, 2)
	end)  <> InvoicePayment.Amount)

loyal ginger wrote:

There are limitations on SQL Server Express.

Dmitry Khudorozhkov wrote:

First question: is this a good db structure?

Select *
From Users
 inner join Addresses on Addresses.UserID = User.ID
where Users.ID = @IDno