April 28, 2006 at 6:19 am
Hi,
I have the following query which I am using to create sales per customer since the beginning of the year...
SELECT
VW_WPL_CUSTOMERS.CUSTOMER_KEY,
(
SELECT ROUND((SUM(SHIPQTY)),3) AS QTY
FROM VW_WPL_SALES
WHERE (DOCDATE >= DATEADD(YY, DATEDIFF(YY, 0, GETDATE()), 0) )
GROUP BY VW_WPL_SALES.CUSTKEY
)
FROM
VW_WPL_CUSTOMERS INNER JOIN VW_WPL_SALES
ON
VW_WPL_CUSTOMERS
.CUSTOMER_KEY = VW_WPL_SALES.CUSTKEY
However I get an error when running the query, stating that the sub-query returns more than one result. When I check the sub-query by running it seperately, it produces the correct results without any duplicate CUSTKEY's
In the VW_WPL_CUSTOMER I know there are also no duplicates, so what seems to be failing?
The precise error msg I get is :
Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Anyone help??..
April 28, 2006 at 7:20 am
It is not a matter of duplicates, a sub query in a select can only return one result. Your subquery will always return multiple rows (otherwise why the GROUP BY) and you surely must have more than one customer
Is this what you wanted
SELECT c.CUSTOMER_KEY, ROUND((SUM(SHIPQTY)),3) AS QTY
FROM VW_WPL_CUSTOMERS c
INNER JOIN VW_WPL_SALES s
ON s.CUSTKEY = c.CUSTOMER_KEY
WHERE (DOCDATE >= DATEADD(YY, DATEDIFF(YY, 0, GETDATE()), 0))
GROUP BY c.CUSTOMER_KEY
I kept the join assuming that you may have missing customers !!
Far away is close at hand in the images of elsewhere.
Anon.
April 28, 2006 at 8:31 am
I suppose it might make it a bit more logical if I tell you what the goal is with the data - I can do in several steps by creating views, but was afetr a one step method...
I need to produce a table, which will give me sales totals for each customer within data ranges.
i.e.
column 1 - customer name
column 2 - sales year to date
column 3 - sales last year to date
column 4 - sales month to date
I know I can create 3 views, one for the current ytd values, one for the prev. ytd values and one for the month to date values, but I thought it would be quicker if I used a sub-query to sum the total sales in the date range required.
April 28, 2006 at 9:11 am
DECLARE @Today datetime, @TodayLastYear datetime,
@StartThisYear datetime, @StartLastYear datetime,
@StartThisMonth datetime
SET @Today = DATEADD(day,0,DATEDIFF(day,0,GETDATE()))
SET @TodayLastYear = DATEADD(year,-1,@Today)
SET @StartThisYear = DATEADD(year,DATEDIFF(year,0,GETDATE()),0)
SET @StartLastYear = DATEADD(year,-1,@StartThisYear)
SET @StartThisMonth = DATEADD(month,DATEDIFF(month,0,GETDATE()),0)
SELECT c.CUSTOMER_KEY, c.CUSTOMER_NAME,
ROUND((SUM(CASE WHEN DOCDATE >= @StartThisYear
THEN SHIPQTY ELSE 0 END)),3) AS [sales year to date],
ROUND((SUM(CASE WHEN DOCDATE >= @StartLastYear AND DOCDATE <= @TodayLastYear
THEN SHIPQTY ELSE 0 END)),3) AS [sales last year to date],
ROUND((SUM(CASE WHEN DOCDATE >= @StartThisMonth
THEN SHIPQTY ELSE 0 END)),3) AS [sales month to date]
FROM VW_WPL_CUSTOMERS c
INNER JOIN VW_WPL_SALES s
ON s.CUSTKEY = c.CUSTOMER_KEY
WHERE DOCDATE >= @StartLastYear
AND DOCDATE <= @Today
GROUP BY c.CUSTOMER_KEY, c.CUSTOMER_NAME
Far away is close at hand in the images of elsewhere.
Anon.
April 28, 2006 at 9:20 am
Thx a lot...I feel a bit sheepish having gone off on the wrong track...
Looks like I've got a lot of learning to do.
Thx again.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply