Improve SQL and concept - 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)

Improve SQL and concept

Author

Topic

jrockfl
Posting Yak Master

223 Posts

Posted - 2006-06-22 : 11:39:22

I'm trying to improve upon some previous code and looking for a better method...

I start off by running this query and joining the item number to get the product category.
SELECT monthlyspecials.ad_code, monthlyspecials.product_name, IMINVLOC_SQL.prod_cat
FROM monthlyspecials
LEFT OUTER JOIN
IMINVLOC_SQL ON monthlyspecials.item_number = IMINVLOC_SQL.Item_No
WHERE IMINVLOC_SQL.loc = 'TP'
ORDER BY monthlyspecials.page_number

Then I would "Loop over" the results from the previous query to perform calculations based on the product category in the previous query

SELECT isNull(SUM(sls_amt),0) As Total, isNull(SUM(cost_amt),0) As Cost
FROM dash_product_sales
WHERE dash_product_sales.prod_cat = '#qCats.prod_cat#' -- this the variable to represent the product category in the previous query.

Then insert it all into a new table.
INSERT INTO squareinch
(ad_code, product_name, prod_cat, total, cost)

I'm looking to do this efficiently with a stored procedure, can someone push me in the right direction?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-22 : 11:47:27

Writing

LEFT JOIN   IMINVLOC_SQL ON monthlyspecials.item_number = IMINVLOC_SQL.Item_No
WHERE       IMINVLOC_SQL.loc = 'TP'

is not necessary. If you want to filter on the table where data might or not might exist, use INNER JOIN instead, because all those NULLs are not fetched anyway. Write

INNER JOIN  IMINVLOC_SQL ON monthlyspecials.item_number = IMINVLOC_SQL.Item_No AND IMINVLOC_SQL.loc = 'TP'

Peter Larsson
Helsingborg, Sweden

jrockfl
Posting Yak Master

223 Posts

Posted - 2006-06-22 : 11:54:36

Thanks for your reply. Thank you, that will help, but what about the rest of my post?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-22 : 13:59:55

What is "loop over"?

Peter Larsson
Helsingborg, Sweden

jrockfl
Posting Yak Master

223 Posts

Posted - 2006-06-22 : 15:45:35

Nevermind, I figured it out.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources