Solved

Please help refine a query to count orders on CRM accounts

Posted on 2009-05-07
4
392 Views
Last Modified: 2012-05-06
Hi experts,
I'm trying to get a count of orders for every account that fits the criteria in the 'where' clause as shown in the code below.  
There is nothing in the Product view that I can 'AND' to the 'where' clause to match the account id's up to.  When I run the query, I get the following error in SQL:
Msg 512, Level 16, State 1, Line 2
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

Is there some way I can rewrite the code below to get the order count to parent to the correct account?  the code works and gets the right total, I just need to get the 'count' generated back to the proper account somehow.
Thanks for your help!


UPDATE dbo.accountextensionbase
SET new_ordercount = (
	SELECT	COUNT(SalesOrderId)  				
	FROM dbo.SalesOrder AS so, dbo.Product AS p
	WHERE
	(New_source_paid_amount <> 0) 
	AND (DATEPART(year, so.New_source_event_date) = DATEPART(year, 
                     GETDATE()))	 AND (so.new_source_event_id = p.new_source_event_id) AND (p.	new_include_in_rollup = '1')and(so.New_source_order_type not like 'S')		and ((so.new_createdby LIKE 'W%') or (so.new_createdby LIKE 'P%'))
	GROUP BY AccountId )

Open in new window

0
Comment
Question by:bucball2007
  • 2
4 Comments
 
LVL 9

Assisted Solution

by:ezraa
ezraa earned 100 total points
ID: 24330415
Instead of grouping the sub query, you need to specify that for each record in the parent table you want the corresponding records in the child table.

replace the group by with a where clause:

instead of GROUP BY AccountId
try: AND AccountID = accountextensionbase.AccountID

(assuming the the parent table accountextensionbase has this field)





UPDATE accountextensionbase
SET new_ordercount = (
        SELECT  COUNT(SalesOrderId)                             
        FROM dbo.SalesOrder AS so, dbo.Product AS p
        WHERE (New_source_paid_amount <> 0) AND (DATEPART(year, so.New_source_event_date) = DATEPART(year, GETDATE())) AND (so.new_source_event_id = p.new_source_event_id) AND (p.   new_include_in_rollup = '1')and(so.New_source_order_type not like 'S') and ((so.new_createdby LIKE 'W%') or (so.new_createdby LIKE 'P%'))
		AND AccountID = accountextensionbase.AccountID
        )

Open in new window

0
 
LVL 40

Accepted Solution

by:
Sharath earned 400 total points
ID: 24331419

Another way of doing the same thing.
UPDATE t1
   SET new_ordercount = t2.Cnt_SalesOrderId
  FROM dbo.accountextensionbase as t1
 INNER JOIN (SELECT	AccountId,COUNT(SalesOrderId) AS Cnt_SalesOrderId
	              FROM dbo.SalesOrder AS so, dbo.Product AS p
	             WHERE (New_source_paid_amount <> 0) 
	               AND (DATEPART(year, so.New_source_event_date) = DATEPART(year,GETDATE()))	 
	               AND (so.new_source_event_id = p.new_source_event_id) 
	               AND (p.	new_include_in_rollup = '1')
                AND (so.New_source_order_type not like 'S')		
                AND ((so.new_createdby LIKE 'W%') OR (so.new_createdby LIKE 'P%'))
	             GROUP BY AccountId) as t2
	   ON t1.AccountId = t2.AccountID

Open in new window

0
 

Author Closing Comment

by:bucball2007
ID: 31579187
The solution from Sharath 123 actually fit our purposes a little better (ezraa's suggestion had an issue with binding, but I see the logic there and I appreciate the input).  Thanks to you both for your help! :)
0
 
LVL 40

Expert Comment

by:Sharath
ID: 24338847
Glad to help you.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Delete from table 6 47
How to query LOCK_ESCALATION 4 41
Updating statistics with error notification email in SQL server 4 109
SQL Restore Script - Syntax Error 8 104
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question