Filtering a query

Question

Post reply

Filtering a query

iklektic

Ten Centuries

Points: 1236
More actions
February 28, 2011 at 2:58 pm

#235758

Hi all; I have a report that lists the total sum of fees done in a specific date range.
SELECT Customer.Number, Customer.Name, sum(InvoicedFeeView.Fees) AS TotalFees, Staff.Username, Staff.StaffID
FROM InvoicedFeeView INNER JOIN
Customer ON InvoicedFeeView.CustomerID = Customer.CustomerID INNER JOIN
Staff ON Customer.SalesRepStaffID = Staff.StaffID
WHERE (InvoicedFeeView.InvoiceDate BETWEEN dbo.dateonly(@Start) AND DBO.DATEONLY(@End)) AND (Staff.StaffID = @staff)
GROUP BY Customer.Number, Customer.Name, Staff.Username, Staff.StaffID
I need to filter this out more by excluding any customers that have placed an order prior to the start date (InvoicedFeeView.InvoiceDate < @Start).
Can anyone help me with this?
Thank you so much in advance for your help.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 1

If u dont need data that are between start and end and also < @Start, why not put, < @End only in your query ??

iklektic Ten Centuries Points: 1236 More actions · Answer 2

Basically, I am trying to get a list of the customers that have done work with us, where their FIRST ORDER was done within the specified date range.

I hope that explains it a bit better?

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 3

i can get the scope of it; but if u could provide some sample data along with a snapshot of the expected output, we will be all set to launch into the code 🙂

iklektic Ten Centuries Points: 1236 More actions · Answer 4

Thank you.

some examples:

DECLARE @staff INT, @Start datetime, @End datetime

SELECT @staff = 183, @Start = '3/1/2010', @End = '2/28/2011'

cNumber Name TotalAmountStaff StaffIDFIRST ORDER DATE Should Be Listed

406 JOHNNY'S PIZZA $2,343.50 jsmith 183 3/5/2010 Yes

49 D&B $438.00 jsmith 183 11/1/2010 Yes

524 SULLIVAN $1,006.60jsmith 183 1/1/2011 Yes

500 SWEET TOOTH $518.00 jsmith 183 2/1/2009 No

This list shows that there were orders placed by this group of customers within the date range specified, the first three rows has there first order placed within the date range, and the last one had their first order outside of the parameters (even though there was results from within that date range).

As you can probably see, I'm not really knowledgeable and any help is greatly appreciated.

Thank you.

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 5

You need to think of this in steps. First you need to find the first order in your system for each customer. This involves a MIN() and a group by on the customerId.

Using that as a subquery, you then filter the remaining customer list by only those customers who have a MinDate after your check date.

If you check out the first link in my signature for sample data and ddl setup, we can probably help you out with explicit code.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

iklektic Ten Centuries Points: 1236 More actions · Answer 6

Thanks for your responses!

I got a solution and that was putting the date range parameter into a having clause and adding "Min(InvoiceDate) between...". That seems to have worked!