September 5, 2010 at 3:51 pm
My first post here, not sure if this is the right forum but here goes....
I have the following query that returns a list of all customer orders that have a total amount greater than 0, and orders them by name and then order date.
select
account0.name as 'name',
ab.createdon as 'orderdate'
from FilteredAccount as account0
join FilteredSalesOrder as ab
on (account0.accountid = ab.customerid and (ab.totalamount > 0))
order by account0.name, ab.createdon asc
sample output:
name Orderdate
cust1 2000/01/01 06:00:00:000
cust2 2004/10/01 08:44:00:000
cust2 2005/03/01 06:00:00:000
cust3 2000/01/01 06:00:00:000
cust3 2002/01/01 06:00:00:000
cust4 2000/01/01 06:00:00:000
What I'm trying to do is only show the oldest order for each customer, for example I would like the sample output above to show:
name Orderdate
cust1 2000/01/01 06:00:00:000
cust2 2004/10/01 08:44:00:000
cust3 2000/01/01 06:00:00:000
cust4 2000/01/01 06:00:00:000
I'm pretty novice, any help or direction is greatly appreciated.
September 5, 2010 at 4:07 pm
If you would post your table definitions some sample data (not anything that would divulge company confidential info), desired result from the sample data. To learn how to do all this please refer to the first link in my signature block.
With the above, I am sure some one will assist you with a tested answer.
September 5, 2010 at 4:50 pm
Hey Ron, the tables I'm working with are rather large (well over 100 columns each), I'm not sure how to show them to you accurately.
The query in my first post returns just under 16,000 records, but I can't figure out how to have it return just the oldest order for each account, instead of all orders for each account.
Basically one of the tables is an Accounts table, the other is an Orders table. I'm pulling only the accounts on the accounts table who have an order in the system that is greater than $0, and displaying all those orders sorting first by account, and then by the date the order was created on. I want to take this query that pulls this list of 'first' orders for an account and put it into a dynamic excel worksheet that will prompt the end user to select the start and end dates they want to filter "new" orders by. All in all a rather convoluted way to pull a list of new customers between particular dates.
September 5, 2010 at 8:25 pm
Without having the table ddl, I'm guessing here, but this looks like it would work:
SELECT account0.name as 'name',
ab.createdon as 'orderdate'
FROM FilteredAccount as account0
JOIN (SELECT customerid, createdon = max(createdon)
FROM FilteredSalesOrder
WHERE totalamount > 0
GROUP BYcustomerid) as ab
ON (account0.accountid = ab.customerid )
ORDER BY account0.name, ab.createdon asc
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 6, 2010 at 11:32 am
awesome, thanks wayne, that did the trick. I was able to add a few more columns I wanted to show in the report, its prompting for the start and end dates and all the data showing is perfect, thanks a ton!
Here is my modified query:
SELECT account0.name as 'name',
account0.industrycodename as 'industry',
account0.numberofemployees as '# of employees',
account0.owneridname as 'ownername',
ab.createdon as 'orderdate'
FROM FilteredAccount as account0
JOIN (SELECT customerid, createdon = min(createdon)
FROM FilteredSalesOrder
WHERE totalamount > 0
GROUP BY customerid) as ab
ON (account0.accountid = ab.customerid )
WHERE (CAST(CONVERT(VARCHAR,ab.createdon,101) as DATETIME) BETWEEN ? AND ?)
ORDER BY ab.createdon asc
Only thing I can't seem to figure out now is how to add an additional column from the filteredsalesorder view. Specifically trying to get the total amount from that order. The query below succeeds, but it brings back the oldest order within the user specified dates for all accounts, not just the accounts whos min(createdon) was within that range. I'm sure I'm overlooking something pretty simple
SELECT account0.name as 'name',
account0.industrycodename as 'industry',
account0.numberofemployees as '# of employees',
account0.owneridname as 'ownername',
ab.createdon as 'orderdate',
ab.totalamount as 'total'
FROM FilteredAccount as account0
JOIN (SELECT customerid, createdon = min(createdon), totalamount
FROM FilteredSalesOrder
WHERE totalamount > 0
GROUP BY customerid, totalamount) as ab
ON (account0.accountid = ab.customerid )
WHERE (CAST(CONVERT(VARCHAR,ab.createdon,101) as DATETIME) BETWEEN ? AND ?)
ORDER BY ab.createdon asc
September 6, 2010 at 1:03 pm
Johnnymac (9/6/2010)
awesome, thanks wayne, that did the trick. I was able to add a few more columns I wanted to show in the report, its prompting for the start and end dates and all the data showing is perfect, thanks a ton!Here is my modified query:
SELECT account0.name as 'name',
account0.industrycodename as 'industry',
account0.numberofemployees as '# of employees',
account0.owneridname as 'ownername',
ab.createdon as 'orderdate'
FROM FilteredAccount as account0
JOIN (SELECT customerid, createdon = min(createdon)
FROM FilteredSalesOrder
WHERE totalamount > 0
GROUP BY customerid) as ab
ON (account0.accountid = ab.customerid )
WHERE (CAST(CONVERT(VARCHAR,ab.createdon,101) as DATETIME) BETWEEN ? AND ?)
ORDER BY ab.createdon asc
Only thing I can't seem to figure out now is how to add an additional column from the filteredsalesorder view. Specifically trying to get the total amount from that order. The query below succeeds, but it brings back the oldest order within the user specified dates for all accounts, not just the accounts whos min(createdon) was within that range. I'm sure I'm overlooking something pretty simple
SELECT account0.name as 'name',
account0.industrycodename as 'industry',
account0.numberofemployees as '# of employees',
account0.owneridname as 'ownername',
ab.createdon as 'orderdate',
ab.totalamount as 'total'
FROM FilteredAccount as account0
JOIN (SELECT customerid, createdon = min(createdon), totalamount
FROM FilteredSalesOrder
WHERE totalamount > 0
GROUP BY customerid, totalamount) as ab
ON (account0.accountid = ab.customerid )
WHERE (CAST(CONVERT(VARCHAR,ab.createdon,101) as DATETIME) BETWEEN ? AND ?)
ORDER BY ab.createdon asc
What I'm trying to do is only show the oldest order for each customer
Okay, I see where I did the most recent createdon date instead of the oldest.
Only thing I can't seem to figure out now is how to add an additional column from the filteredsalesorder view.
However, your change to your query is now getting the oldest createdon date for each customer/totalamount. This is substantially different. If you now want other information about that row, you will have to join back to it to get it.
SELECT account0.name as 'name',
account0.industrycodename as 'industry',
account0.numberofemployees as '# of employees',
account0.owneridname as 'ownername',
ab.createdon as 'orderdate',
fso.totalamount as 'total'
FROM FilteredAccount as account0
JOIN (SELECT customerid, createdon = min(createdon)
FROM FilteredSalesOrder
WHERE totalamount > 0
GROUP BY customerid) as ab
ON (account0.accountid = ab.customerid )
JOIN FilterSalesOrder fso
ON fso.customerid = ab.customerid
AND fso.createdon = ab.createdon
WHERE (CAST(CONVERT(VARCHAR,ab.createdon,101) as DATETIME) BETWEEN ? AND ?)
ORDER BY ab.createdon asc
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 6, 2010 at 7:11 pm
Thanks Wayne, I learned quite a bit with your help on this.
-John
September 6, 2010 at 8:41 pm
Johnnymac (9/6/2010)
Thanks Wayne, I learned quite a bit with your help on this.-John
Glad I could help. Just please respond back and let us all know how things ended up for you!
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 7, 2010 at 5:20 pm
All the reports turned out great, above expectations for certain. Was able to use the query in a number of pivot table reports/charts that ended up looking pretty nice. Best of all got a much better understanding of how to approach working with the data as it structured in our CRM system.
If there are any good SQL books you recommend I'd love the suggestions.
-John
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy