February 28, 2011 at 2:58 pm
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.
February 28, 2011 at 3:13 pm
If u dont need data that are between start and end and also < @Start, why not put, < @End only in your query ??
February 28, 2011 at 3:22 pm
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?
February 28, 2011 at 3:55 pm
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 🙂
February 28, 2011 at 4:13 pm
Thank you.
some examples:
DECLARE @Staff INT, @Start datetime, @End datetime
SELECT @Staff = 183, @Start = '3/1/2010', @End = '2/28/2011'
cNumber Name TotalAmount Staff StaffID FIRST 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.60 jsmith 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.
February 28, 2011 at 4:34 pm
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.
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
March 1, 2011 at 9:28 am
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!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply