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

Author

Topic

Zoomer36
Starting Member

13 Posts

Posted - 2012-07-20 : 13:57:23

I am trying to get the order number, order date, the number of cartons sold, the number of pack sold(which is a multiple of the cartons), and the customer ID and the Customer State.

I have the script working well without the State added. When I add the state, about 10% of my numbers get skewed. I determined that when I look in the address table to get the state, it causes me a problem.

In the address table, it give contact address (4), ship to address (5), and bill to address (6). This should be easy in that I make a condition where the type = 5 for the ship address.

The problem is that we have other software that imports customers. Each time it is run, it creates another instead of updating. So for most customers, I have 3 entries (4,5,6). For some customers, I have 33. That is the largest amount. What it then does is that it takes my numbers for cartons and packs and multplies it by the number of type 5 entries there are for each customer code. If there is one entry of type 5 then the number is correct. If there are 10 then it multiplies my numbers by 10.

How can I make this work so it only gets the state (A.STATE) in the code and does not multiply it?

Thanks for any and all help!


SELECT  CASE WHEN I.STATUS = 9 
	  THEN 'Invoice' 
	  ELSE 'Return' 
 	END AS DOCTYPE, 
       	I.ORDER_NO, I.ORDER_DATE,  
       	CASE WHEN I.STATUS = 9 
	  THEN SUM(X.QTY_SHIP) 
	  ELSE SUM(X.QTY_SHIP * -1) 
	END AS CARTONS,
       	CASE WHEN I.STATUS = 9 
	  THEN SUM(CASE WHEN (IT.SALE_MEAS = 'CARTON' or IT.SALE_MEAS = '8/25-PK') THEN X.QTY_SHIP * 10 ELSE X.QTY_SHIP * 5 END) 
	  ELSE -1 * SUM(CASE WHEN IT.SALE_MEAS = 'CARTON' THEN X.QTY_SHIP * 10 ELSE X.QTY_SHIP * 5 END) 
	END AS Packs,
	C.CUST_CODE, A.STATE
FROM INVOICES I WITH (NOLOCK)
INNER JOIN X_INVOIC X WITH (NOLOCK) ON I.DOC_NO = X.ORDER_NO AND I.STATUS = X.STATUS
INNER JOIN ITEMS IT WITH (NOLOCK) ON X.ITEM_CODE = IT.ITEMNO
INNER JOIN CUST C ON I.CUST_CODE = C.CUST_CODE
INNER JOIN ADDRESS A ON C.CUST_CODE = A.CUST_CODE
WHERE I.STATUS IN (9,12)
AND A.TYPE = 5
AND IT.CATEGORY IN ('CIGS','CIGSSPL','CIGS25')
AND (I.ORDER_DATE >= '01/01/2008') AND (I.ORDER_DATE < '1/01/2009')
GROUP BY I.ORDER_DATE, I.ORDER_NO, A.STATE, I.STATUS, C.CUST_CODE
ORDER BY I.ORDER_DATE, I.ORDER_NO

Feelfree to email me at bzkjoe59@gmail.com

Have a great day!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-20 : 14:07:38

Instead of joining on the ADDRESS table insert a subquery that picks only one state per cust_code, like this:

--- YOUR CURRENT JOIN CONDITION
INNER JOIN ADDRESS A
		ON  C.CUST_CODE = A.CUST_CODE
		
-- REPLACE IT WITH THIS
	INNER JOIN 
		( 
			SELECT 
				a.CUST_CODE, 
				MAX(a.STATE) AS STATE 
			FROM 
				ADDRESS a
			WHERE
				a.TYPE=5
			GROUP BY 
				a.CUST_CODE 
		) A
		ON  C.CUST_CODE = A.CUST_CODE

Zoomer36
Starting Member

13 Posts

Posted - 2012-07-23 : 09:22:30

Good morning!

Thank you so much. That worked perfectly for me. Can't thank you enough. I am doing reports for sales for the past 4 years and there are 10K-15K invoices per year so this will save me a ton of work.

Have a great day!

Charles

Do I need to try to close a topic when it is solved?

Have a great day!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources