3 Table Join With Spesific Criterias

  • I have several SQL tables I'm working with, and I want to join each other. I can perform join operations and see the tables under one roof. But there is an error in the join process I did, I need to sort and select the last one at the same time while joining. However, I couldn't do that 🙁 If I had to explain with an example;

    I have the following tables;

    Table1: StoreStock

    Columns: StoreCode, ProductCode, Stock, Time

    Rule: This is my main table, we must join other tables here. StoreCode+ProductCode will be used as the key.

    Table2: StockOnTheRoad

    Columns: TransferNo, StoreCode, ProductCode, OnTheWayStock, Status, Time

    Rule: There is more than one status for 1 transfer here. The current status of the transfer is written in the table according to the process. The number of stocks is taken according to the status. "Pending" if the status of the transfer is S1, S2; if the status of the transfer is S3, S4, it is called "OnTheWay". Here, it is only necessary to get the current status number and write it under "Waiting" or "OnTheWay" to sort the statuses and get the current one. So, if a transfer's final status is S4, it will take this number and write it under "OnTheWay", and under "Waiting" if S2. For a product from different transfers, "Waiting" and "OnTheWay" may come together. I use StoreCode+ProductCode as the key.

    Table3: StoreOrder

    Columns: StoreCode, ProductCode, OpenOrder, Status

    Rule: I join the open order quantities for a store in this table into my first table. Here, I remove the statuses "Cancel" and "Done". I use StoreCode+ProductCode as the key.

    The most complicated situation here is the correct numbers of Table2 to Table1. I couldn't make it here. Is there anyone who can help? Thank you in advance 🙂

  • I'm sorry for that. But if I knew that much, I would answer this question myself 🙂 I can give the attached excel example for the table and query. If you can't help, I understand, I'll try to do something myself 🙂

    https://we.tl/t-Vngmp1R7jU

    • This reply was modified 2 years, 4 months ago by  tonic.
  • I'm sure that someone here would be able to help you, if you were able to provide sample data (in consumable form, ready for cut & paste into SSMS – CREATE TABLE and INSERT will do the job) and desired output based on that sample data.

    If you do that, people would be able to use it in conjunction with the excellent description you have already provided to give you a working solution.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • I wish so much that I could do what you said and that you guys could help me 🙁 But unfortunately, I haven't mastered that much yet 🙁 The best I can do is the following query. My problem is that I can't add and sum join from the "StockOnTheRoad" table according to status.

    `
    select

    ss.StoreCode, ss.ProductCode, ss.Stock, ss.Time,

    sotr.TransferNo, sotr.StoreCode, sotr.ProductCode, sum(sotr.OnTheWayStock),
    case when status in ('S1', 'S2') then 'Pending'
    when status in ('S3', 'S4') then 'OnTheWay' else '' end sotr.Status,
    sotr.Time,

    so.StoreCode, so.ProductCode, sum(so.OpenOrder), so.Status,

    from [dbo].[StoreStock] ss

    left join [dbo].[StockOnTheRoad] sotr on sotr.(StoreCode+ProductCode)=ss.(StoreCode+ProductCode)
    left join [dbo].[StoreOrder] so on so.(StoreCode+ProductCode)=ss.(StoreCode+ProductCode)

    where so.Status not in ('Cancel', 'Done')
    `
  • If you <Right-Click> your tables, you can generate "Script Table As" ....CREATE To...., which you can copy-paste here as  "CODE" to help everyone understand your table definitions.

    Then sample data included as INSERT INTO statements.

    And some examples of what you want the SELECT results to look like.

    • This reply was modified 2 years, 4 months ago by  homebrew01.
    • This reply was modified 2 years, 4 months ago by  homebrew01.
    • This reply was modified 2 years, 4 months ago by  homebrew01.
  • What question are you trying to answer? I am guessing what you need, but does it look anything like this? (There may be typos as I do not have tables to write against).

    This looks for the most recent time for each transfer, store and product, then assigns a status of 'pending' or 'on the way', then groups by store, product and status to give a sum of OnTheWayStock.

    If you put something like this in a CTE, could you join your other tables to it to get what you need? This will return multiple rows per store and product if there are multiple transfers with different statuses, is that what you need, or do you need a single row per store and product, perhaps with the Pending and OnTheWay totals in different columns?

    SELECT a.StoreCode, a.ProductCode, 
    CASE
    WHEN a.[Status] IN ('S1', 'S2') THEN 'Pending'
    WHEN a.[Status] IN ('S3', 'S4') THEN 'OnTheWay'
    ELSE 'Other'
    END AS StatusSummary,
    SUM(a.OnTheWayStock) AS StockUnits,
    FROM (
    SELECT TransferNo, StoreCode, ProductCode, OnTheWayStock, [Status], [Time],
    ROW_NUMBER() OVER (PARTITION BY TransferNo, StoreCode, ProductCode ORDER BY [Time] DESC) AS RowNum
    FROM DBO.StockOnTheRoad
    ) AS a
    WHERE a.RowNum = 1
    GROUP BY a.StoreCode, a.ProductCode,
    CASE
    WHEN a.[Status] IN ('S1', 'S2') THEN 'Pending'
    WHEN a.[Status] IN ('S3', 'S4') THEN 'OnTheWay'
    ELSE 'Other'
    END
  • I'm not really sure what you want to do. Do you want to group rows by "Time"?

    SELECT ss.StoreCode, 
    ss.ProductCode,
    ss.Stock,
    ss.Time,
    sotr.TransferNo,
    sotr.StoreCode,
    sotr.ProductCode,
    sotr.OnTheWayStockSum,
    sotr.Status,
    sotr.Time
    so.StoreCode,
    so.ProductCode,
    so.OpenOrderSum,
    so.Status
    FROM StoreStock ss
    OUTER APPLY(SELECT case when sotr.TransferNo in ('S1', 'S2') then 'Pending'
    when sotr.TransferNo in ('S3', 'S4') then 'OnTheWay'
    else ''
    end Status,
    sotr.TransferNo,
    sotr.StoreCode,
    sotr.ProductCode,
    sotr.Time,
    SUM((sotr.OnTheWayStock) OnTheWayStockSum
    FROM StockOnTheRoad sotr
    WHERE sotr.StoreCode = ss.StoreCode
    AND sotr.ProductCode = ss.ProductCode
    GROUP BY sotr.TransferNo,
    sotr.StoreCode,
    sotr.ProductCode,
    sotr.Time,
    case when sotr.TransferNo in ('S1', 'S2') then 'Pending'
    when sotr.TransferNo in ('S3', 'S4') then 'OnTheWay'
    else ''
    end
    ) sotr
    OUTER APPLY(SELECT so.StoreCode,
    so.Status,
    so.ProductCode,
    sum(so.OpenOrder) OpenOrderSum
    FROM StoreOrder so
    WHERE so.StoreCode = ss.StoreCode
    AND so.ProductCode = ss.ProductCode
    GROUP BY so.StoreCode, so.Status, so.ProductCode) so

     

  • Actually, "Ed B" was right about this "do you need a single row per store and product, perhaps with the Pending and OnTheWay totals in different columns?".

    Retail thinking is as follows; In the table where we see the stock of that product in a store, on the basis of product and store, in the form of a non-repeating line, the number of that product on the way, waiting to be collected in the warehouse (Pending) and shipping from the warehouse to the store (OnTheWay), at the same time, coming to that store from the supplier (OpenOrder).

    In summary, while there are 12 stocks in 123 stores of SKU1 products, there are 34 waiting to be collected in the warehouse, and there are 5354 on-the-road stocks from the supplier to 123 stores.

    To be seen in a single table; the pieces of stocks in the store, the total pieces of stocks on the way from the warehouse to the store, and the total pieces of orders on the way from the supplier to the store.

  • I can't tell what information is stored in StoreOrder and I hesitate to join to it unless StoreCode and ProductCode are unique. I have added the join, but it will duplicate data if StoreCode and ProductCode are not unique. The StoreOrder status filter is included in the join to preserve the outer join. You could put the filter in the where section, as long as you accounted for nulls.

    Does this return anything like what you need? I did create some tables with these column names, so the syntax is valid, but I don't understand the relationships between StoreStock and StoreOrder. This is where providing sample data would be helpful.


    WITH StockInTransit
    AS (

    SELECT a.StoreCode, a.ProductCode,
    SUM(a.OnTheWayStock * (CASE WHEN a.[Status] IN ('S1', 'S2') THEN 1 ELSE 0 END)) AS PendingStock,
    SUM(a.OnTheWayStock * (CASE WHEN a.[Status] IN ('S3', 'S4') THEN 1 ELSE 0 END)) AS OnTheWayStock
    FROM (
    SELECT TransferNo, StoreCode, ProductCode, OnTheWayStock, [Status],
    ROW_NUMBER() OVER (PARTITION BY TransferNo, StoreCode, ProductCode ORDER BY [Time] DESC) AS RowNum
    FROM dbo.StockOnTheRoad
    ) AS a
    WHERE a.RowNum = 1
    GROUP BY a.StoreCode, a.ProductCode
    )

    SELECT ss.StoreCode, ss.ProductCode, ss.Stock,
    ISNULL(st.PendingStock,0) AS PendingStock,
    ISNULL(st.OnTheWayStock,0) AS OnTheWayStock,
    so.OpenOrder, so.[Status] AS StoreOrderStatus
    FROM dbo.StoreStock AS ss
    LEFT JOIN StockInTransit AS st ON ss.StoreCode = st.StoreCode AND ss.ProductCode = st.ProductCode
    LEFT JOIN dbo.StoreOrder AS so ON ss.StoreCode = st.StoreCode AND ss.ProductCode = st.ProductCode
    AND so.[Status] <> 'Cancel' AND so.[Status] <> 'Done';



Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply