SQL Help - 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

SQL Server Development (2000)

SQL Help

Author

Topic

jrockfl
Posting Yak Master

223 Posts

Posted - 2004-11-19 : 15:37:34

How can I return all of the sales people even if they dont have any sales? Currently, it only returns 2 people because they are the ones that have sales.

SELECT SUM(Sls_Amt) AS SalesAmount, SUM(Qty_Ordered) AS qty, dash_product_sales.slspsn_no, slspsn_name
FROM dash_product_sales, ARSLMFIL_SQL
WHERE MONTH(Inv_Dt) = MONTH('#qGetFocus.focusmonth#')
AND YEAR(Inv_Dt) = YEAR('#qGetFocus.focusmonth#')
AND prod_cat IN (#ListQualify(qGetFocus.prod_cat,"'",",","All")#)
AND dash_product_sales.slspsn_no IN ('101', '102', '103', '104', '105', '106', '107', '108', '110', '116', '117')
AND ARSLMFIL_SQL.slspsn_no = dash_product_sales.slspsn_no
GROUP BY dash_product_sales.slspsn_no, slspsn_name
ORDER BY qty DESC

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-11-19 : 15:42:22

maybe a union can help?

select * from
(SELECT SUM(Sls_Amt) AS SalesAmount, SUM(Qty_Ordered) AS qty, dash_product_sales.slspsn_no, slspsn_name
FROM dash_product_sales, ARSLMFIL_SQL
WHERE MONTH(Inv_Dt) = MONTH('#qGetFocus.focusmonth#')
AND YEAR(Inv_Dt) = YEAR('#qGetFocus.focusmonth#')
AND prod_cat IN (#ListQualify(qGetFocus.prod_cat,"'",",","All")#)
AND dash_product_sales.slspsn_no IN ('101', '102', '103', '104', '105', '106', '107', '108', '110', '116', '117')
AND ARSLMFIL_SQL.slspsn_no = dash_product_sales.slspsn_no
GROUP BY dash_product_sales.slspsn_no, slspsn_name
Union ALL
SELECT 0 AS SalesAmount, 0 AS qty, dash_product_sales.slspsn_no, slspsn_name
FROM dash_product_sales, ARSLMFIL_SQL
--WHERE -- here goes the conditions that define ones that have no sales...
GROUP BY dash_product_sales.slspsn_no, slspsn_name
) t

Go with the flow & have fun! Else fight the flow

X002548
Not Just a Number

15586 Posts

Posted - 2004-11-19 : 15:55:28

NOT TESTED

With the DDL we could though



    SELECT  b.SalesAmount
	  , b.qty
	  , a.slspsn_no
	  , a.slspsn_name
      FROM (SELECT DISTINCT dash_product_sales.slspsn_no, slspsn_name
	      FROM dash_product_sales
		INNER JOIN  ARSLMFIL_SQL
			ON  ARSLMFIL_SQL.slspsn_no = dash_product_sales.slspsn_no) AS A
	 LEFT JOIN  (SELECT SUM(Sls_Amt) AS SalesAmount
		  	,  SUM(Qty_Ordered) AS qty
		  	,  dash_product_sales.slspsn_no
		  	,  slspsn_name
		      FROM dash_product_sales
		INNER JOIN  ARSLMFIL_SQL
			ON  ARSLMFIL_SQL.slspsn_no = dash_product_sales.slspsn_no
		   WHERE MONTH(Inv_Dt) = MONTH('#qGetFocus.focusmonth#')
		     AND YEAR(Inv_Dt) = YEAR('#qGetFocus.focusmonth#')
		     AND prod_cat IN (#ListQualify(qGetFocus.prod_cat,"'",",","All")#)
		     AND dash_product_sales.slspsn_no IN ('101', '102', '103', '104', '105', '106', '107', '108', '110', '116', '117')
		GROUP BY dash_product_sales.slspsn_no, slspsn_name) AS b
  ORDER BY b.qty DESC

Brett

8-)

jrockfl
Posting Yak Master

223 Posts

Posted - 2004-11-19 : 16:10:05

Thank you, that pointed me in the right direction.
I ended up with this...

SELECT A.slspsn_no, A.slspsn_name, IsNull(B.SalesAmount,0) As SalesAmount, IsNull(B.qty,0) As qty
FROM ARSLMFIL_SQL A
LEFT OUTER JOIN
(
SELECT SUM(Sls_Amt) AS SalesAmount, SUM(Qty_Ordered) AS qty, slspsn_no
FROM dash_product_sales
WHERE MONTH(Inv_Dt) = MONTH('#qGetFocus.focusmonth#')
AND YEAR(Inv_Dt) = YEAR('#qGetFocus.focusmonth#')
AND prod_cat IN (#ListQualify(qGetFocus.prod_cat,"'",",","All")#)
GROUP BY slspsn_no
) AS B
ON A.slspsn_no = B.slspsn_no
WHERE A.slspsn_no IN ('101', '102', '103', '104', '105', '106', '107', '108', '110', '116', '117')

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2004-11-19 : 16:25:08

Not that it matters, but I remember reading that you should try to use COALESCE instead of ISNULL because it is ANSI compliant.

jrockfl
Posting Yak Master

223 Posts

Posted - 2004-11-19 : 16:42:48

Thanks for the tip :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources