Help creating a Left Join Stored Procedure to show a result set

Transact-SQL
1

Hello all, I am new to the forum and new to TSql so please be gentle.

I am trying to create my first procedure to show a set of results from a couple of other tables.

I have made a start but struggling to get it to work. As I say I am new so it will probably be something so basic I am missing but would appreciate so help and guidance towards my results.

So here is what I want the end table to look like:

t1.Branch, (data held on all tables to identify which branch)
t1.[route], (data held on table 1 only, is unique by cust_code and del_order_no)
t1.[drop_number], (data held on table 1 only, is unique by cust_code and del_order_no)
t1.[cust_code], (data held on table 1 to link to the route and drop_number, may be more than one cust_code with different del_order_no but the same route and drop_number)
t2.[business_name], (data held on table 2 only)
t2.[mob_no], (data held on table 2 only)
t2.[post_code], (data held on table 2 only)
t2.[addr_1], (data held on table 2 only)
NULL AS 'Payment Method', (blank column)
NULL AS 'ETA', (blank column)
NULL AS 'Weight', (blank column)
NULL AS 'Cages', (blank column)
NULL AS 'Pallets', (blank column)
NULL AS 'Bread', (blank column)
NULL AS 'Stamps', (blank column)
NULL AS 'Credit', (blank column)
NULL AS 'Cigarettes', (blank column)

So as you can see, I only have two tables (t1 and t2) that all my data is stored.

I basically want to be able to enter a date (orderdue, held on T1, and i want to set this as a variable) and branch and then be given all the results ordered by route, drop_number asc.

My issue is that there may be multiple rows and results with the same cust_code, orderdue but always a different del_order_no e.g..
cust del_order_no route drop orderdue
a 001 1 1 27-05-2022
a 002 1 1 27-05-2022
a 003 1 1 27-05-2022
b 004 1 2 27-05-2022
c 005 1 3 27-05-2022
d 006 2 1 27-05-2022
e 007 2 2 27-05-2022
e 008 2 2 27-05-2022

so as you can see a customer may have more than one order, all with a different del_order_no but with the same orderdue date but the route and drop_number will be the same. So in my results I just want it all merged together so i just get...
cust route drop
a 1 1
b 1 2
c 1 3
d 2 1
e 2 2

My final issue is that T1 will have details for customers who I don't want to know about, so I need to look at T2 before doing any work and only find customers with a field called route set to = xxxx and then bring my results for those customers with details on T1 only.

I hope this make some sense.
and thanks in advance
Richard

2

Hi Richard,

Welcome to the forum and everybody here has started with the first step so don't worry to much about this. My advise would be not think to much about the end result but try to write a query first with the desired result, add parameters after and create the stored procedure after that.

All merged together... we all have a mutal friend and it's called google. You can merge the result sets together by using

SELECT DISTINCT

Another option is to use

GROUP BY

if you search for those words and t-sql and I'm sure you will find examples that will help you.

I think you will like this

SQL Server tutorial for beginners - YouTube

Look at part 18,19 and 20 :slight_smile:

1 Like
3

thank you for the reply and very handy you tube links.

I have actually managed to get a lot further now but struggling on one thing if someone may be able to help please.

So i currently of this row header:
SUM (dd.[qty_ordered]) as 'QTY of part4'

So the data i use has a parent order number that ends in 0. This parent order is then split into parts and with each part the order number changes by one digit at the end and replacing the 0 with 1, 2, 3, or 4.

Basically a customer places an order we get the following order numbers on the database:
1233330
1233331
1233332
1233333
1233334

What i am wanting with my above SUM header is only to SUM the QTY ordered data if the order number ends in 4 (LIKE %4).

I am struggling on how to do this though?

4

Are there a lot of rows in the table with part numbers that end in 4?

5

Well as @yosiasz is telling you, performance can be an issue. I don't know your table structure but it could be something like this:

SELECT
SUM(CASE WHEN RIGHT(your ordernumber,1)=4 THEN dd.[qty_ordered] ELSE 0 END)