Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 397
  • Last Modified:

Please help refine a query to count orders on CRM accounts

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
bucball2007
Asked:
bucball2007
  • 2
2 Solutions
 
ezraaCommented:
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
 
SharathData EngineerCommented:

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
 
bucball2007Author Commented:
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
 
SharathData EngineerCommented:
Glad to help you.
0

Featured Post

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now