Eliminate Duplicate rows from Stored Procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Eliminate Duplicate rows from Stored Procedure

Author

Topic

camyden
Starting Member

9 Posts

Posted - 2007-03-01 : 19:39:25

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

camyden
Starting Member

9 Posts

Posted - 2007-03-01 : 20:27:42

I got it!

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))

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-02 : 03:00:02

It is more efficient to do it this way.


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 Invoices.Date >= dateadd(day, datediff(day, 0, getdate()), -30)
AND   Invoices.Date <= dateadd(day, datediff(day, 0, getdate()), 0)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources