Need Help With This Query - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

Need Help With This Query

Author

Topic

ronnyr
Starting Member

21 Posts

Posted - 2006-10-03 : 20:00:54

Hi All,

Can you help me with this query problem? I need to have Client Account who did not order in certain period but have ordered within the past 3 months. And I will show:

Client --- Last Invoice DT --- Total Order
MUI302 --- 09/06/2006 --- 4 Orders
JUI490 --- 09/01/2006 --- 2 Orders

I already have this query but I don't know how to group this client because each order showing different invoice date.
This is my query:

===========================================================

SELECT OrderID, AccountName, SUM(TotalOrder) as TotalOrder, InvoiceDT
FROM (
SELECT
o.OrderID,
o.AccountID,
TotalOrder = COUNT(o.OrderID),
o.InvoiceDT
FROM
oms.[Order] o
JOIN Account a ON a.AccountID = o.AccountID
WHERE
o.AccountID NOT IN (SELECT DISTINCT AccountID FROM
[Order] WHERE InvoiceDT IS NOT
NULL AND (InvoiceDT > '09/06/2006')
) AND
o.AccountID IN (SELECT DISTINCT
AccountID FROM oms.[Order]
WHERE o.InvoiceDT IS NOT NULL AND
(o.InvoiceDT BETWEEN '09/01/2006'
AND '09/06/2006'))
GROUP BY o.OrderID, o.AccountID, a.AccountName, o.InvoiceDT
) as tmp
GROUP BY AccountName, InvoiceDT

=================================================================

Thanks in advance.

Ron.

“Know where to find the information and how to use it - That's the secret of success”

nr
SQLTeam MVY

12543 Posts

Posted - 2006-10-04 : 05:22:38

Remove InvoiceDT from the group by clause and use max(InvoiceDT) in the select

and you can get rid of
GROUP BY o.OrderID, o.AccountID, a.AccountName, o.InvoiceDT

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-04 : 05:28:39

Are you still working with this query, and have done since mid september?
What can we help you with?

Peter Larsson
Helsingborg, Sweden

nr
SQLTeam MVY

12543 Posts

Posted - 2006-10-04 : 05:48:13

I would have thought you want sometiung like this.
I think you have the in and not in the wrong way round


SELECT o.OrderID, a.AccountName, TotalOrder = SUM(TotalOrder) , InvoiceDT = max(InvoiceDT)
FROM oms.[Order] o
	JOIN Account a 
	ON a.AccountID = o.AccountID
WHERE o.InvoiceDT IS NOT NULL 
AND o.InvoiceDT > '20060609'	-- use yyyymmdd here
and o.AccountID NOT IN	(	SELECT DISTINCT AccountID
							FROM [Order] WHERE InvoiceDT IS NOT NULL 
							InvoiceDT BETWEEN '20060109' AND '20060609'	-- use yyyymmdd here
						)
group by o.OrderID, a.AccountName

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources