Eliminate Duplicate ID's in this StoredProcedure

I have a stored procedure that I use for Monthly Billing

delete from BillingCurrent

insert into BillingCurrent([Name],Address,City,State,Zip,InvoiceID,CustomerID,[Date],InvoiceTotal)

SELECT Customers.Name,Customers.Address,Customers.City,Customers.State,Customers.Zip,Invoices.InvoiceID,Customers.CustomerID,Invoices.Date,Invoices.InvoiceTotal

FROM Invoices INNER JOIN

Customers ON Invoices.CustomerID = Customers.CustomerID

WHERE CONVERT(varchar(15), Invoices.Date, 112) Between CONVERT(varchar(15),dateadd (d,-30,GETDATE()), 112)and CONVERT(varchar(15), GETDATE(), 112)

This works great, but if a customer has more than one invoice open it adds that Customer again (for each invoice that is not 0.00. How can I change this SP to only add each Customer once regardless of how many invoices they have?

In your query you tried to work with Invoice.Date and Invoice.InvoiceTotal fields.

If you have following data in your Invoices table for CustomerId=1:

Date InvoiceTotal

1.1.2006 1000

1.1.2007 2000

What do you want to save into BillingCurrent?

Which SQL Server version do you use?

|||

Cammyr:

You state that you want to reduce the number of rows to 1 whenever a customer has more than one invoice during a month. However, what I don't understand is what we are supposed to put in these three fields if we are returning only one row: (1) InvoiceID, (2) Date, and (3) InvoiceTotal. I have therefore put together two methods to return only one row per customer. The first method simply returns this information for the last invoice posted for a given customer. There is a good chance that this is not what you want but would rather have the total of all invoices summed into the InvoiceTotal column:

--
-- This method uses uses (1) CROSS APPLY and (2) ROW_NUMBER() to
-- find the "last" invoice posted to a particular customers account to
-- display that information with the customer information.
--

SELECT c.Name,
c.Address,
c.City,
c.State,
c.Zip,
i.InvoiceID,
c.CustomerID,
i.Date,
i.InvoiceTotal
FROM Customers c
cross apply
( select invoiceId,
row_number ()
over ( order by Date desc,
invoiceId desc
)
as seq,
Date,
InvoiceTotal
from Invoices p
where p.customerId = c.customerId
and CONVERT(varchar(15), p.Date, 112)
Between CONVERT(varchar(15),dateadd (d,-30,GETDATE()), 112)
and CONVERT(varchar(15), GETDATE(), 112)
) i
where i.seq = 1

-- Name Address City State Zip InvoiceID CustomerID Date InvoiceTotal
-- -- -- -- -- --
-- Dave Mugambo 1318 Mockingbird Lane Munster In 46321 4 1 2007-02-28 00:00:00.000 14.92

--
-- This method instead posts the SUM of all invoices into the InvoiceTotal
-- column and chooses the highest InvoiceID for the InvoiceID column and
-- the highest Date for the Date column. It is not clear what is requried
-- for these fields
--

SELECT c.Name,
c.Address,
c.City,
c.State,
c.Zip,
max (i.InvoiceID) as InvoiceID,
c.CustomerID,
max (i.Date) as Date,
sum (i.InvoiceTotal) as InvoiceTotal
FROM Invoices i
INNER JOIN Customers c
ON i.CustomerID = c.CustomerID
WHERE CONVERT(varchar(15), i.Date, 112)
Between CONVERT(varchar(15),dateadd (d,-30,GETDATE()), 112)
and CONVERT(varchar(15), GETDATE(), 112)
group by c.CustomerId,
c.Name,
c.Address,
c.City,
c.State,
c.Zip

-- Name Address City State Zip InvoiceID CustomerID Date InvoiceTotal
-- -- -- -- -- --
-- Dave Mugambo 1318 Mockingbird Lane Munster In 46321 4 1 2007-02-28 00:00:00.000 94.76

( I completely agree with Konstantin's questions. )

|||

I want to save Name,Address,City,State,Zip,InvoiceID,CustomerID in BillingCurrent

But really I guess I don't need InvoiceID or InvoiceTotal as I have a relationship between BillingCurrent and InvoiceDetails On CustomerID Column that will get all the InvoiceDetails for each CustomerID.

I'm using vb.express with sql.express

|||

You are both right there were quite a few fields in my insert statement that were not needed. All I really needed was CustomerID,Name,Address,City and Zip because I can use the getchildrows method to get all of the corresponding invoicedetails for each customerID.

This is what I came up with, it seems to be working fine.

insert into BillingCurrent (CustomerID,[Name],Address,City,State,Zip)

SELECT Distinct Customers.CustomerID, Customers.Name,Customers.Address, Customers.City, Customers.State, Customers.Zip

FROM Invoices INNER JOIN

Customers ON Invoices.CustomerID = Customers.CustomerID

WHERE (CONVERT(varchar(15), Invoices.Date, 112) BETWEEN CONVERT(varchar(15), DATEADD(d, - 30, GETDATE()), 112) AND CONVERT(varchar(15),

GETDATE(), 112))

I'm sure yours works for that situation Kent, so I'm marking yours as the answer.

Thanks