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.
Author |
Topic |
Bau_JJones
Starting Member
13 Posts |
Posted - 2014-06-26 : 13:07:10
|
Greetings to all. I've worked with Crystal Reports for more than 10 years now, but I've never had any formal training on it. My SQL knowledge is limited to only trial-and-error and on-the-fly learning, sadly. However, I've had past experience with VB and some earlier, more basic programming, so I don't find it that difficult to grasp; it's just rather overwhelming without any formalized training.I'm trying now to solve a problem that involves a sales history report. Our sales history table lists only records for product lines which a customer has purchased in the past. However, the budgeting team needs a list of each customer with all product lines, even if they've never purchased, and they'd like to include an industry code value that is from another table.So the way the tables are looking is this:Sales_History as the main table with the appropriate data in itProduct_Lines as a reference to fill out the main table with non-purchased linesAccounts as another reference that gives the industry code per the customer ID in the main tableI'm seemingly on the right track with the following, which tries to create a list of all product lines and the records from sales_history which match them:SELECT sales_history.sah_sort_id,sales_history.sah_sortkey3,sales_history.sah_sortkey2_desc, prod_lines.pl_prodline_id,prod_lines.pl_short_descFROM dbo.sales_historyINNER JOIN prod_lines ON sales_history.sah_sortkey3=prod_lines.pl_prodline_idUNION ALL SELECT CAST(NULL AS VARCHAR(20)), CAST(NULL AS VARCHAR(20)), CAST(NULL AS VARCHAR(20)), prod_lines.pl_prodline_id,prod_lines.pl_short_descFROM prod_linesWHERE NOT EXISTS ( SELECT * FROM sales_history WHERE sales_history.sah_sortkey3 = prod_lines.pl_prodline_id) ORDER BY pl_prodline_id,sah_sortkey2_descHowever, this produces a list of only 21 rows, when there should be 41.I feel as I've summarized this that I am looking at the problem from completely the wrong direction, so any advice would be most appreciated.Thank you. |
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-06-26 : 13:15:57
|
Try LEFT JOIN instead. If you need further help, please post sample data, expected output and DDL for the tables involved. You can use this link to see how to post data/code to get the best help: http://www.sqlservercentral.com/articles/Best+Practices/61537/.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
bitsmed
Aged Yak Warrior
545 Posts |
Posted - 2014-06-26 : 15:37:11
|
Try this:select a.sah_sort_id ,a.sah_sortkey3 ,a.sah_sortkey2_desc ,b.pl_prodline_id ,b.pl_shortdesc from sales_history as a full outer join prod_lines as b on b.pl_prodline_id=a.sah_sortkey3 order by b.pl_prodline_id ,a.sah_sortkey2_desc |
|
|
Bau_JJones
Starting Member
13 Posts |
Posted - 2014-06-26 : 16:52:25
|
Hi tkizer,Thank you for the response and the gentle nudge in the correct direction! I have found that no changing of the JOIN makes a differenceThe code below will create a sample set of data from the Sales History table, which contains all the important sales figures. I believe I can work out those figures later so I've left them out of the sample set here.--===== If the test table already exists, drop it IF OBJECT_ID('tempdb.#mytable','U') IS NOT NULL DROP TABLE #mytable--===== Create the test table with CREATE TABLE #mytable ( ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, FLT VARCHAR(3), PL VARCHAR(30), CUST VARCHAR(75), )--===== All Inserts into the IDENTITY column SET IDENTITY_INSERT #mytable ON--===== Insert the test data into the test table INSERT INTO #mytable (ID, FLT, PL, CUST) SELECT '1','Z37','10100 ','21st Century' UNION ALL SELECT '2','Z37','10300 ','21st Century' UNION ALL SELECT '3','Z37','10700 ','21st Century' UNION ALL SELECT '4','Z37','20300 ','21st Century' UNION ALL SELECT '5','Z37','30150 ','21st Century' UNION ALL SELECT '6','Z37','30900 ','21st Century' UNION ALL SELECT '7','Z37','40900 ','21st Century' --===== Set the identity insert back to normal SET IDENTITY_INSERT #mytable ON SELECT v.* FROM #MyTable v ORDER BY PL The next bit of code will create the complete 41-row sample set of all applicable product lines from the Product Lines table.--===== If the test table already exists, drop it IF OBJECT_ID('tempdb.#mytable','U') IS NOT NULL DROP TABLE #mytable--===== Create the test table with CREATE TABLE #mytable ( PL INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, SDESC VARCHAR(75) )--===== All Inserts into the IDENTITY column SET IDENTITY_INSERT #mytable OFF--===== Insert the test data into the test table INSERT INTO #mytable (PL, SDESC) SELECT '10100 ','S-IS: Inductive Sensors' UNION ALL SELECT '10150 ','S-CS: Capacitive Sensors' UNION ALL SELECT '10200 ','S-US: Ultrasonic Sensors' UNION ALL SELECT '10250 ','S-MS: My-Com Switches' UNION ALL SELECT '10300 ','S-PS: Photoelectric Sensors' UNION ALL SELECT '10600 ','S-AS: Magnetic Sensors' UNION ALL SELECT '10700 ','S-AC: Accessories' UNION ALL SELECT '10800 ','S-SS: Sensor Systems' UNION ALL SELECT '10900 ','S-OT: SE-Other Products/Svcs' UNION ALL SELECT '20100 ','M-IO: Inc. Encoders optical' UNION ALL SELECT '20150 ','M-IM: Inc. Encoders magnetic' UNION ALL SELECT '20200 ','M-AO: Abs. Encoders optical' UNION ALL SELECT '20250 ','M-AM: Abs. Encoders magnetic' UNION ALL SELECT '20300 ','M-MS: Encoders Without Bearing' UNION ALL SELECT '20350 ','M-TR: Tach. & Resolvers' UNION ALL SELECT '20400 ','M-SS: Speed Limit Switches' UNION ALL SELECT '20450 ','M-CO: Counters' UNION ALL SELECT '20500 ','M-DS: Drive & Spindle Position' UNION ALL SELECT '20600 ','M-IS: Inclinometers' UNION ALL SELECT '20900 ','M-OT: MC - Other Products/Svcs' UNION ALL SELECT '30100 ','V-DC: Digital Camera' UNION ALL SELECT '30150 ','V-SV: Smart Vision' UNION ALL SELECT '30200 ','V-IP: Image Processing Equip.' UNION ALL SELECT '30250 ','V-VS: Vision Systems' UNION ALL SELECT '30300 ','V-IM: Inspection Machines' UNION ALL SELECT '30350 ','V-PS: Vision Proj. & Sys. Int.' UNION ALL SELECT '30900 ','V-OT: VT - Other Products/Svcs' UNION ALL SELECT '40100 ','P-PE: Pressure electronic' UNION ALL SELECT '40150 ','P-PM: Pressure mechanical' UNION ALL SELECT '40200 ','P-TE: Temperature electronic' UNION ALL SELECT '40250 ','P-TM: Temperature mechanical' UNION ALL SELECT '40300 ','P-AL: Level' UNION ALL SELECT '40500 ','P-FS: Force & Strain Sensors' UNION ALL SELECT '40850 ','P-HW: Haenni Wheel Load Scales' UNION ALL SELECT '40900 ','P-OT: PI - Other Products/Svcs' UNION ALL SELECT '50100 ','G-CG: Cold Glue' UNION ALL SELECT '50150 ','G-HM: Hot Melt' UNION ALL SELECT '50200 ','G-CQ: Gluing Controller & QA' UNION ALL SELECT '50910 ','G-SE: GS - Services' UNION ALL SELECT '50920 ','G-OT: GS - Other Products' UNION ALL SELECT '90100 ','COMA: Contract Manufacturing' --===== Set the identity insert back to normal SET IDENTITY_INSERT #mytable OFF SELECT v.* FROM #MyTable v ORDER BY PL Finally the code below will produce a short list of sample customers from the Customers table (I added an ID column here because our database has non-integer primary keys):--===== If the test table already exists, drop it IF OBJECT_ID('tempdb.#mytable','U') IS NOT NULL DROP TABLE #mytable--===== Create the test table with CREATE TABLE #mytable ( U_ID INT IDENTITY(1,1), CUST VARCHAR(9) PRIMARY KEY CLUSTERED, NAME VARCHAR(35), GRP VARCHAR (25) )--===== All Inserts into the IDENTITY column SET IDENTITY_INSERT #mytable ON--===== Insert the test data into the test table INSERT INTO #mytable (U_ID,CUST, NAME, GRP) SELECT '1','C1491','Korber Medipak NA Inc.','' UNION ALL SELECT '2','C1492','R.R. Donnelley South','900' UNION ALL SELECT '3','C1493','21st Century','' UNION ALL SELECT '4','C1494','Integrated Power Services','' UNION ALL SELECT '5','C1495','NOV ASEP Elmar (National Oilwell)','' --===== Set the identity insert back to normal SET IDENTITY_INSERT #mytable OFF SELECT v.* FROM #MyTable v ORDER BY NAME I am not sure how to create a table of my expected result, so here is a tab-separated text list of what I'm trying to do:Customers.Name Product Lines.PL Product Lines.Description Sales History.Filter Sales History.PL Sales History.Customer21st Century 10100 S-IS: Inductive Sensors Z37 10100 21st Century21st Century 10150 S-CS: Capacitive Sensors 21st Century 10200 S-US: Ultrasonic Sensors 21st Century 10250 S-MS: My-Com Switches 21st Century 10300 S-PS: Photoelectric Sensors Z37 10300 21st Century21st Century 10600 S-AS: Magnetic Sensors 21st Century 10700 S-AC: Accessories Z37 10700 21st Century21st Century 10800 S-SS: Sensor Systems 21st Century 10900 S-OT: SE-Other Products/Svcs 21st Century 20100 M-IO: Inc. Encoders optical 21st Century 20150 M-IM: Inc. Encoders magnetic 21st Century 20200 M-AO: Abs. Encoders optical 21st Century 20250 M-AM: Abs. Encoders magnetic 21st Century 20300 M-MS: Encoders Without Bearing Z37 20300 21st Century21st Century 20350 M-TR: Tach. & Resolvers 21st Century 20400 M-SS: Speed Limit Switches 21st Century 20450 M-CO: Counters 21st Century 20500 M-DS: Drive & Spindle Position 21st Century 20600 M-IS: Inclinometers 21st Century 20900 M-OT: MC - Other Products/Svcs 21st Century 30100 V-DC: Digital Camera 21st Century 30150 V-SV: Smart Vision Z37 30150 21st Century21st Century 30200 V-IP: Image Processing Equip. 21st Century 30250 V-VS: Vision Systems 21st Century 30300 V-IM: Inspection Machines 21st Century 30350 V-PS: Vision Proj. & Sys. Int. 21st Century 30900 V-OT: VT - Other Products/Svcs Z37 30900 21st Century21st Century 40100 P-PE: Pressure electronic 21st Century 40150 P-PM: Pressure mechanical 21st Century 40200 P-TE: Temperature electronic 21st Century 40250 P-TM: Temperature mechanical 21st Century 40300 P-AL: Level 21st Century 40500 P-FS: Force & Strain Sensors 21st Century 40850 P-HW: Haenni Wheel Load Scales 21st Century 40900 P-OT: PI - Other Products/Svcs Z37 40900 21st Century21st Century 50100 G-CG: Cold Glue 21st Century 50150 G-HM: Hot Melt 21st Century 50200 G-CQ: Gluing Controller & QA 21st Century 50910 G-SE: GS - Services 21st Century 50920 G-OT: GS - Other Products 21st Century 90100 COMA: Contract Manufacturing |
|
|
Bau_JJones
Starting Member
13 Posts |
Posted - 2014-06-26 : 16:53:42
|
That tab-separated list looks rather ugly, so if you can tell me a better way to present the expected result I will reformat it.Thank you. |
|
|
kostya1122
Starting Member
15 Posts |
Posted - 2014-06-26 : 18:50:11
|
try to cross join your costumer table and product lines tablethen join sales history to the outcome of the cross join |
|
|
Bau_JJones
Starting Member
13 Posts |
Posted - 2014-06-27 : 08:11:01
|
quote: Originally posted by bitsmed Try this:select a.sah_sort_id ,a.sah_sortkey3 ,a.sah_sortkey2_desc ,b.pl_prodline_id ,b.pl_shortdesc from sales_history as a full outer join prod_lines as b on b.pl_prodline_id=a.sah_sortkey3 order by b.pl_prodline_id ,a.sah_sortkey2_desc
Thank you for the reply, bitsmed, but I'm not sure how I can join those two tables without a common column. I guess I could create a table using the methods in posting my codes and adding an ID column, but I imagine there is an easier way.I'd say my first, vague post was not nearly clear enough. |
|
|
Bau_JJones
Starting Member
13 Posts |
Posted - 2014-06-27 : 08:13:21
|
quote: Originally posted by kostya1122 try to cross join your costumer table and product lines tablethen join sales history to the outcome of the cross join
Hello kostya1122. Thanks for your reply.I've done a cross join with Customers and Prod_lines. The code below will produce a table that shows the results, limited to one customer, 21st Century (for the sake of simplicity).--===== If the test table already exists, drop it IF OBJECT_ID('tempdb.#mytable','U') IS NOT NULL DROP TABLE #mytable--===== Create the test table with CREATE TABLE #mytable ( ID VARCHAR(5), NAME VARCHAR(30), SG VARCHAR(3), PL INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, PL_DESC VARCHAR(45) )--===== All Inserts into the IDENTITY column SET IDENTITY_INSERT #mytable ON--===== Insert the test data into the test table INSERT INTO #mytable (ID, NAME, SG, PL, PL_DESC)SELECT 'C1493','21st Century',' ','10100 ','S-IS: Inductive Sensors' UNION ALLSELECT 'C1493','21st Century',' ','10150 ','S-CS: Capacitive Sensors' UNION ALLSELECT 'C1493','21st Century',' ','10200 ','S-US: Ultrasonic Sensors' UNION ALLSELECT 'C1493','21st Century',' ','10250 ','S-MS: My-Com Switches' UNION ALLSELECT 'C1493','21st Century',' ','10300 ','S-PS: Photoelectric Sensors' UNION ALLSELECT 'C1493','21st Century',' ','10600 ','S-AS: Magnetic Sensors' UNION ALLSELECT 'C1493','21st Century',' ','10700 ','S-AC: Accessories' UNION ALLSELECT 'C1493','21st Century',' ','10800 ','S-SS: Sensor Systems' UNION ALLSELECT 'C1493','21st Century',' ','10900 ','S-OT: SE-Other Products/Svcs' UNION ALLSELECT 'C1493','21st Century',' ','20100 ','M-IO: Inc. Encoders optical' UNION ALLSELECT 'C1493','21st Century',' ','20150 ','M-IM: Inc. Encoders magnetic' UNION ALLSELECT 'C1493','21st Century',' ','20200 ','M-AO: Abs. Encoders optical' UNION ALLSELECT 'C1493','21st Century',' ','20250 ','M-AM: Abs. Encoders magnetic' UNION ALLSELECT 'C1493','21st Century',' ','20300 ','M-MS: Encoders Without Bearing' UNION ALLSELECT 'C1493','21st Century',' ','20350 ','M-TR: Tach. & Resolvers' UNION ALLSELECT 'C1493','21st Century',' ','20400 ','M-SS: Speed Limit Switches' UNION ALLSELECT 'C1493','21st Century',' ','20450 ','M-CO: Counters' UNION ALLSELECT 'C1493','21st Century',' ','20500 ','M-DS: Drive & Spindle Position' UNION ALLSELECT 'C1493','21st Century',' ','20600 ','M-IS: Inclinometers' UNION ALLSELECT 'C1493','21st Century',' ','20900 ','M-OT: MC - Other Products/Svcs' UNION ALLSELECT 'C1493','21st Century',' ','30100 ','V-DC: Digital Camera' UNION ALLSELECT 'C1493','21st Century',' ','30150 ','V-SV: Smart Vision' UNION ALLSELECT 'C1493','21st Century',' ','30200 ','V-IP: Image Processing Equip.' UNION ALLSELECT 'C1493','21st Century',' ','30250 ','V-VS: Vision Systems' UNION ALLSELECT 'C1493','21st Century',' ','30300 ','V-IM: Inspection Machines' UNION ALLSELECT 'C1493','21st Century',' ','30350 ','V-PS: Vision Proj. & Sys. Int.' UNION ALLSELECT 'C1493','21st Century',' ','30900 ','V-OT: VT - Other Products/Svcs' UNION ALLSELECT 'C1493','21st Century',' ','40100 ','P-PE: Pressure electronic' UNION ALLSELECT 'C1493','21st Century',' ','40150 ','P-PM: Pressure mechanical' UNION ALLSELECT 'C1493','21st Century',' ','40200 ','P-TE: Temperature electronic' UNION ALLSELECT 'C1493','21st Century',' ','40250 ','P-TM: Temperature mechanical' UNION ALLSELECT 'C1493','21st Century',' ','40300 ','P-AL: Level' UNION ALLSELECT 'C1493','21st Century',' ','40500 ','P-FS: Force & Strain Sensors' UNION ALLSELECT 'C1493','21st Century',' ','40850 ','P-HW: Haenni Wheel Load Scales' UNION ALLSELECT 'C1493','21st Century',' ','40900 ','P-OT: PI - Other Products/Svcs' UNION ALLSELECT 'C1493','21st Century',' ','50100 ','G-CG: Cold Glue' UNION ALLSELECT 'C1493','21st Century',' ','50150 ','G-HM: Hot Melt' UNION ALLSELECT 'C1493','21st Century',' ','50200 ','G-CQ: Gluing Controller & QA' UNION ALLSELECT 'C1493','21st Century',' ','50910 ','G-SE: GS - Services' UNION ALLSELECT 'C1493','21st Century',' ','50920 ','G-OT: GS - Other Products' UNION ALLSELECT 'C1493','21st Century',' ','90100 ','COMA: Contract Manufacturing' --===== Set the identity insert back to normal SET IDENTITY_INSERT #mytable OFF SELECT v.* FROM #MyTable v ORDER BY PL |
|
|
Bau_JJones
Starting Member
13 Posts |
Posted - 2014-06-27 : 08:14:25
|
Ah, no 'edit' option on the posts. I'll now look into how to join that result with the sales_history table. |
|
|
Bau_JJones
Starting Member
13 Posts |
Posted - 2014-06-27 : 11:44:02
|
I think I'm getting a better idea what the problem is with this case. I've been trying to combine the two results using a CTE, and I'm running into the same problem.In a nutshell, the problem seems to be that I need the following result:- 41 product lines (which is not the complete set; there are obsolete rows in the table), plus descriptions from the prod_lines table
- all rows from the sales_history table where the prod_line fields match, leaving either NULL or duplicating the cust_name field otherwise. This result requires limitation to the appropriate subset as there are several different groups of data stored in the same table
- the salesgroup_ID field from the Customers table where the cust_ID field matches the same in the sales_history table
So if I were to select the three tables individually, I would do as below.For the Product Lines:SELECT PL.pl_prodline_ID,PL.pl_shortdesc --Select only Product Line and Description columns FROM prod_lines PL WHERE PL.pl_prodline_ID>'10000' AND PL.pl_prodline_ID<'91000' --Limit to current product line codes Then the Sales History information:SELECT * FROM sales_history SH WHERE SH.sah_year='2014' --Limit to this fiscal year AND SH.sah_sort_id='Z37' --The sort key to limit to the appropriate records AND SH.sah_sortkey2='C1493' --Limits to 21st Century account to reduce results for testing And finally the Industry Code from the Customers table:SELECT cu_salesgroup_ID FROM customers CU No matter how I try to join these tables, I end up with one of two results:[list]All rows from the sales_history table result multiplied by the prodline_ID field in the prod_lines tableLimitation to only the two rows from 2014 in the sales_history table for 21st century, including the lined information from the prod_lines tableI'm not sure what to do at this point, but I'd love to learn what I am doing wrong. It's got to be something very simple that is just beyond my knowledge at this point. |
|
|
Bau_JJones
Starting Member
13 Posts |
Posted - 2014-06-27 : 12:09:07
|
Okay, now I'm getting somewhere. This process has been very enlightening thus far.This has produced nearly the result I'm looking for:--Create a CTE to select all appropriate sales_history rows and the salesgroup_ID field from Customers.WITH SH_CustAS (SELECT SH.*, CU.cu_salesgroup_ID FROM sales_history SH RIGHT JOIN customers CU ON SH.sah_sortkey2=CU.cu_cust_id WHERE SH.sah_year='2014' AND SH.sah_sort_id='Z37' AND SH.sah_sortkey2='C1493')--Select all the active product line codes from the prod_lines table and join with the CTE from above.SELECT PL.pl_prodline_ID,PL.pl_short_desc,SH_Cust.* FROM prod_lines PL LEFT JOIN SH_Cust ON pl_prodline_id=SH_Cust.sah_sortkey3 WHERE PL.pl_prodline_ID>'10000' AND PL.pl_prodline_ID<'91000' Now I just need to fill in the NULL values in the sah_sortkey2_desc field with the result from cust_ID and come up with a sum of the twelve monthly columns to create one yearly result, and I'm done. |
|
|
Bau_JJones
Starting Member
13 Posts |
Posted - 2014-06-27 : 13:53:31
|
All right, I am once again stumped.This does not produce the desired result with more than one account listed, because the result places accounts in available rows of the original 41 rather than duplicating it.Now I'm trying to find a way to create a result like this:PL1 Customer1PL2 Customer1PL3 Customer1PL4 Customer1PL5 Customer1PL1 Customer2PL2 Customer2PL3 Customer2PL4 Customer2PL5 Customer2 Even though the data in the sales history table might only list this:PL1 NULLPL2 Customer1PL3 NULLPL4 NULLPL5 Customer1PL1 Customer2PL2 NULLPL3 Customer2PL4 NULLPL5 Customer2 It seems to me that what I need is a way to list all product lines, then reset and list them again, times the number of customers with the customer name listed beside the product line each time. Then I need to show the matching data from the sales history table or a NULL value if there is nothing available.I need to rest my head and regroup. |
|
|
Bau_JJones
Starting Member
13 Posts |
Posted - 2014-06-30 : 11:15:27
|
After doing some research on this I am no closer to a solution.My problem seems to be that I cannot even frame the correction properly to find the solution.I feel like I might be on the right track with INSERT INTO but I'm uncertain.After reading quite a bit, I am thinking that I might need to do something like this:- SELECT all the needed data from the three tables.
- CREATE VIEW to make a virtual table that I can populate as needed.
- Make every row in the Customers table repeat (if necessary) so that there are 41 rows for every customer name[*]INSERT INTO to fill the view with the 41 rows from the Customers table, matching the data from the 41 rows from the Product Lines table (INNER JOIN), and including the applicable rows from the Sales History Table (LEFT OUTER JOIN)
If I can do all of that, I think I'll have what I need.Am I on the right track with that procedure, or am I overlooking something much simpler? |
|
|
Bau_JJones
Starting Member
13 Posts |
Posted - 2014-06-30 : 11:18:04
|
That procedure should read like this:- SELECT all the needed data from the three tables.
- CREATE VIEW to make a virtual table that I can populate as needed.
- Make every row in the Customers table repeat (if necessary) so that there are 41 rows for every customer name
- INSERT INTO to fill the view with the 41 rows from the Customers table, matching the data from the 41 rows from the Product Lines table (INNER JOIN), and including the applicable rows from the Sales History Table (LEFT OUTER JOIN)
|
|
|
Bau_JJones
Starting Member
13 Posts |
Posted - 2014-07-02 : 17:39:43
|
I am slowly starting to get somewhere with this case, but I'm not there yet.I broke it into individual steps as listed above, and first came up with a SELECT statement that combines the customers and prod_lines tables (I am using only one account at this time):SELECT CU.cu_cust_id AS 'Acct',CU.cu_name AS 'Customer',CU.cu_salesgroup_id AS 'Industry Code', PL.pl_prodline_ID as 'Prod Line',PL.pl_short_desc AS 'Description' FROM sisl_data02.dbo.customers CU CROSS JOIN sisl_data02.dbo.prod_lines PL WHERE CU.cu_cust_id='C1493' AND PL.pl_prodline_id>'10000' AND PL.pl_prodline_ID<'91000' That code generates 41 rows containing exactly the information I need after completing the first three steps of my procedure.So I put this statement into a CTE and tried JOINING it (using every JOIN type) in an attempt to get 41 rows that contain linked records from the sales_history table only where the customer ID and the product line are equal.Here is the code:--Create a CTE to select all appropriate Customer and Prod Line rows and combine them.WITH Cust_PLAS (SELECT CU.cu_cust_id AS 'Acct',CU.cu_name AS 'Customer',CU.cu_salesgroup_id AS 'Industry Code', PL.pl_prodline_ID as 'Prod Line',PL.pl_short_desc AS 'Description' FROM sisl_data02.dbo.customers CU CROSS JOIN sisl_data02.dbo.prod_lines PL WHERE CU.cu_cust_id='C1493' AND PL.pl_prodline_id>'10000' AND PL.pl_prodline_ID<'91000') --Select the Sales History rows and match to the Cust_PL CTE.SELECT Cust_PL.*,SH.sah_sortkey2,SH.sah_sortkey2_desc AS 'Customer',SH.sah_sortkey3, ROUND(SH.sah_sales_pd1_amt+SH.sah_sales_pd2_amt+SH.sah_sales_pd3_amt+ SH.sah_sales_pd4_amt+SH.sah_sales_pd5_amt+SH.sah_sales_pd6_amt+ SH.sah_sales_pd7_amt+SH.sah_sales_pd8_amt+SH.sah_sales_pd9_amt+ SH.sah_sales_pd10_amt+SH.sah_sales_pd11_amt+SH.sah_sales_pd12_amt,0) AS 'Sales YTD' FROM sisl_data02.dbo.sales_history SH Right JOIN Cust_PL ON SH.sah_sortkey2=Cust_PL.Acct WHERE SH.sah_year='2014' AND SH.sah_sort_id='Z37' ORDER BY Cust_PL.Customer,Cust_PL.[Prod Line] This code results in 123 rows. There are three rows in the sales_history table for customer C1493. What I'm trying to get is 41 rows with the three rows from sales_history listed only in the row where they match.If I do the four types of JOINS in the second statement, these are the resulting number of rows: - INNER: 123 rows
- LEFT OUTER: 1773 rows (which makes sense, since it is showing all rows from the sales_history table)
- RIGHT OUTER: 123 rows (this is confusing because it is the same as the INNER JOIN)
- CROSS JOIN: 67773 rows (again this makes sense)
I think I'm doing something wrong in the JOIN criteria and the WHERE clause.I have a bit of a problem because the sales_history table has many different sort_id types, for some 57 different reports, and I need to select just one type ('Z37') and one fiscal year ('2014').I'm not sure how to do this at this point. It seems to me that I need to select the appropriate rows from sales_history before JOINING the two tables, but I'm not sure how to do that.Can someone please give me a nudge in the right direction?Thank you. |
|
|
Bau_JJones
Starting Member
13 Posts |
Posted - 2014-07-03 : 14:30:55
|
All right, I've figure it out. The problem ended up being in the WHERE clause; I should have just included all those criteria in the ON phrase of the JOIN.Here is the final code which works:--Create a CTE to select all appropriate Customer and Prod Line rows and combine them.WITH Cust_PLAS (SELECT CU.cu_cust_id AS 'Acct',CU.cu_name AS 'Customer',CU.cu_defaultslsrep_id AS 'Territory', CU.cu_salesgroup_id AS 'Industry Code',PL.pl_prodline_ID as 'Prod Line', PL.pl_short_desc AS 'Description' FROM sisl_data02.dbo.customers CU CROSS JOIN sisl_data02.dbo.prod_lines PL WHERE --(CU.cu_cust_id='C1493' OR CU.cu_cust_id='C1492') AND PL.pl_prodline_id>'10000' AND PL.pl_prodline_ID<'91000') --Select the Sales History rows and match to the Cust_PL CTE.SELECT Cust_PL.*,--ISNULL(SH.sah_sortkey2,'') AS 'Customer',ISNULL(SH.sah_sortkey2_desc,'') AS 'Name', --ISNULL(SH.sah_sortkey3,'') AS 'SortKey3', ISNULL(ROUND(SH.sah_sales_pd1_amt+SH.sah_sales_pd2_amt+SH.sah_sales_pd3_amt+ SH.sah_sales_pd4_amt+SH.sah_sales_pd5_amt+SH.sah_sales_pd6_amt+ SH.sah_sales_pd7_amt+SH.sah_sales_pd8_amt+SH.sah_sales_pd9_amt+ SH.sah_sales_pd10_amt+SH.sah_sales_pd11_amt+SH.sah_sales_pd12_amt,0),'') AS 'Sales YTD' FROM Cust_PL LEFT JOIN sisl_data02.dbo.sales_history SH ON Cust_PL.Acct=SH.sah_sortkey2 AND Cust_PL.[Prod Line]=SH.sah_sortkey3 AND SH.sah_year='2014' AND SH.sah_sort_id='Z37' ORDER BY Cust_PL.Customer,Cust_PL.[Prod Line] Thanks for getting me started on this. I learned quite a bit in the last week, and all said and done it took me about 10 hours to get where I needed to go. A long time for one query, but I picked up some great knowledge that will be invaluable going started. |
|
|
|
|
|
|
|