Solved

Please help refine a query to count orders on CRM accounts

Posted on 2009-05-07
4
389 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
Comment Utility
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
Comment Utility

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
Comment Utility
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
Comment Utility
Glad to help you.
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

772 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now